SQL/XML

<h2 id="specification">Specification</h2>
<p>The specification defines the data type XML, functions for working with XML, including element construction, mapping data from relational tables, combining XML fragments, and embedding <a href="/facts/XQuery/47Ep0fSS">XQuery</a> expressions in <a href="/facts/SQL/UjQLJcYm">SQL</a> statements. Functions which can be embedded include XMLQUERY (which extracts XML or values from an XML field) and XMLEXISTS (which predicates whether an XQuery expression is matched).
</p><p>Further information and examples of the SQL/XML functions are provided in the external links below<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a><a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a><a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a>.
</p>
<h2 id="standard-compliance">Standard compliance</h2>
<p>The result of Wagner's objective evaluation of the <i>SQL/XML</i>:2006 standard compliance of Oracle 11g Release 1, MS SQL Server 2008 and MySQL 5.1.30 is shown in the following table<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a>, to which the data for PostgreSQL 9.1,<a class="footnote-ref" id="fnref:6" href="#fn:6"><sup>6</sup></a><a class="footnote-ref" id="fnref:7" href="#fn:7"><sup>7</sup></a> and IBM DB2 has been added:
</p>
<table><tbody><tr><th></th><th>Oracle 11g Release 1</th><th>IBM DB2 9.7</th><th>MS SQL Server 2008</th><th>MySQL 5.1.30</th><th>PostgreSQL 9.1</th></tr><tr><th>Datatype XML</th><td>Partial (Oracle entitles the data type 'XMLType' instead of 'XML')</td><td>High</td><td>High</td><td>No</td><td>Partial</td></tr><tr><th>SQL/XML predicates</th><td>High</td><td>High</td><td>Partial</td><td>No</td><td>Partial</td></tr><tr><th>SQL/XML functions</th><td>High</td><td>High</td><td>Partial</td><td>Low</td><td>High</td></tr><tr><th><a href="/facts/XQuery/47Ep0fSS">XQuery</a> augmentation</th><td>Yes</td><td>Yes</td><td>Yes</td><td>No</td><td>No</td></tr></tbody></table>
<h2 id="examples">Examples</h2>
<p>The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):
</p>
SELECT XMLELEMENT(NAMEs "PhoneBook", -- root element name
                  XMLAGG(           -- aggregation over the rows	
                  XMLELEMENT(NAME "Contact",                                                  
                  XMLATTRIBUTES(cust.FIRST_NAME AS "Name",
                                cust.TEL)
                              )
                        )
                  )
FROM TMP.CUSTOMER AS cust;

<p>And the output:
</p>
<PhoneBook>
    <Contact Name="Daniel" TEL="788255855"/>
    <Contact Name="Martin" TEL="889665447"/>
    <Contact Name="Eva"    TEL="111222333"/>
    <Contact Name="Alena"  TEL="444555666"/>
    <Contact Name="Oliver" TEL="777888999"/>
    <Contact Name="George" TEL="444882446"/>
    <Contact Name="Jamie"  TEL="123456789"/>
</PhoneBook>

<p>Samples are taken from javalobby article<a class="footnote-ref" id="fnref:8" href="#fn:8"><sup>8</sup></a>.
</p>

<h2 id="external-links">External links</h2>
<ul><li><a href="http://www.postgresql.org/docs/current/static/functions-xml.html">SQL/XML on PostgreSQL</a></li>
<li><a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb13gen.htm#g1047191">SQL/XML on Oracle 11g</a></li></ul>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>Funderburk, J. E.; Malaika, S.; Reinwald, B. (2002), "XML programming with SQL/XML and XQuery" (PDF), IBM Systems Journal, 41 (4): 642–665, doi:10.1147/sj.414.0642, retrieved 14 November 2011 <a href="http://ict.udlap.mx/people/carlos/is346/files/reinwald.pdf" target="_blank">http://ict.udlap.mx/people/carlos/is346/files/reinwald.pdf</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>Wagner, Michael (2010), "1. Auflage", SQL/XML:2006 - Evaluierung der Standardkonformität ausgewählter Datenbanksysteme, Diplomica Verlag, ISBN 978-3-8366-9609-8 <a href="978-3-8366-9609-8" target="_blank">978-3-8366-9609-8</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>Eisenberg, Andrew; Melton, Jim (2004), "Advancements in SQL/XML" (PDF), SIGMOD Record, 33 (3): 79, doi:10.1145/1031570.1031588, retrieved 14 November 2011 <a href="http://www.sigmod.org/publications/sigmod-record/0409/11.JimMelton.pdf" target="_blank">http://www.sigmod.org/publications/sigmod-record/0409/11.JimMelton.pdf</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
<li id="fn:4"><p>Eisenberg, Andrew; Melton, Jim (2002), "SQL/XML is Making Good Progress" (PDF), SIGMOD Record, 31 (2): 101, CiteSeerX 10.1.1.109.4867, doi:10.1145/565117.565141, retrieved 14 November 2011 <a href="http://www.sigmod.org/publications/sigmod-record/0206/standard.pdf" target="_blank">http://www.sigmod.org/publications/sigmod-record/0206/standard.pdf</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li>
<li id="fn:5"><p>Wagner, Michael (2010), "1. Auflage", SQL/XML:2006 - Evaluierung der Standardkonformität ausgewählter Datenbanksysteme, Diplomica Verlag, ISBN 978-3-8366-9609-8 <a href="978-3-8366-9609-8" target="_blank">978-3-8366-9609-8</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li>
<li id="fn:6"><p>PostgreSQL Conformance with ISO 9075-14 (SQL/XML), at PostgreSQL 9.1 documentation. <a href="http://www.postgresql.org/docs/9.1/static/features.html" target="_blank">http://www.postgresql.org/docs/9.1/static/features.html</a> <a href="#fnref:6" class="footnote-back-ref">↩</a></p></li>
<li id="fn:7"><p>PostgreSQL 9.1 XML functions, at PostgreSQL 9.1 documentation. <a href="http://www.postgresql.org/docs/current/static/functions-xml.html" target="_blank">http://www.postgresql.org/docs/current/static/functions-xml.html</a> <a href="#fnref:7" class="footnote-back-ref">↩</a></p></li>
<li id="fn:8"><p>Kec, Daniel (2013), SQLX - From DB Straight to XML and Back, Javalobby.org <a href="#fnref:8" class="footnote-back-ref">↩</a></p></li>
</ol>

SQL/XML open-in-new

SQL/XML