SQL/XML

SQL/XML or XML-Related Specifications is an extension to the Structured Query Language (SQL) specification, which defines the use of XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database.

SQL/XML is defined by ISO/IEC 9075 Part 14:

• SQL:2003-14 defined the basic datatypes, mappings, predicates and functions;
• SQL:2006-14, SQL:2008-14, SQL:2011-14 was augmented, as a complementary standard to XQuery ^{[ 1]}.

Specification

The specification defines functions for working with XML, including element construction, mapping data from relational tables, combining XML fragments, and embedding XQuery expressions in SQL 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).

Further information and examples of the SQL/XML functions are provided in the external links below^{[ 1][ 1][ 1]}.

Standard compliance

The result of Wagner's objective evaluation of the SQL/XML:2006 standard compliance of Oracle 11g Release 1, MS SQL Server 2008 and MySQL 5.1.30 is shown in the following table^{[ 1]}, to which the data for PostgreSQL 9.1,^[1][2] and IBM DB2 has been added:

NOTE: only Oracle, IBM DB2 and MS-SQL-Server have been augmented with XQuery.

Examples

The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):

SELECT XMLELEMENT(NAME "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;

And the output:

<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>

Samples are taken from javalobby article^{[ 1]}.

External links

• SQLXML.org
• SQLX.org
• SQL/XML on PostgreSQL

References

1. ↑ PostgreSQL Conformance with ISO 9075-14 (SQL/XML), at PostgreSQL 9.1 documentation.
2. ↑ PostgreSQL 9.1 XML functions, at PostgreSQL 9.1 documentation.
3. ↑ Eisenberg, Andrew; Melton, Jim (2002), "SQL/XML is Making Good Progress", SIGMOD record 31 (2), retrieved 14 November 2011 
4. ↑ Funderburk, J. E.; Malaika, S.; Reinwald, B. (2002), "XML programming with SQL/XML and XQuery", IBM Systems Journal 41 (4), retrieved 14 November 2011 
5. ↑ Eisenberg, Andrew; Melton, Jim (2004), "Advancements in SQL/XML", SIGMOD record 33 (3), retrieved 14 November 2011 
6. ↑ Wagner, Michael (2010), "1. Auflage", SQL/XML:2006 - Evaluierung der Standardkonformität ausgewählter Datenbanksysteme, Diplomica Verlag, ISBN 3-8366-9609-6 
7. ↑ Kec, Daniel (2013), SQLX - From DB Straight to XML and Back, Javalobby.org  |accessdate= requires |url= (help)