SQL/XML orXML-Related Specifications is part 14 of theStructured Query Language (SQL) specification. In addition to the traditional predefined SQL data types likeNUMERIC, CHAR, TIMESTAMP, ... it introduces the predefined data typeXML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQLdatabase.
The specification defines the data typeXML, functions for working with XML, including element construction, mapping data from relational tables, combining XML fragments, and embeddingXQuery expressions inSQL 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[2][3][4].
The result of Wagner's objective evaluation of theSQL/XML:2006 standard compliance of Oracle 11g Release 1, MS SQL Server 2008 and MySQL 5.1.30 is shown in the following table[2], to which the data for PostgreSQL 9.1,[5][6] and IBM DB2 has been added:
| Oracle 11g Release 1 | IBM DB2 9.7 | MS SQL Server 2008 | MySQL 5.1.30 | PostgreSQL 9.1 | |
|---|---|---|---|---|---|
| Datatype XML | Partial (Oracle entitles the data type 'XMLType' instead of 'XML') | High | High | No | Partial |
| SQL/XML predicates | High | High | Partial | No | Partial |
| SQL/XML functions | High | High | Partial | Low | High |
| XQuery augmentation | Yes | Yes | Yes | No | No |
The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):
SELECTXMLELEMENT(NAMEs"PhoneBook",-- root element nameXMLAGG(-- aggregation over the rowsXMLELEMENT(NAME"Contact",XMLATTRIBUTES(cust.FIRST_NAMEAS"Name",cust.TEL))))FROMTMP.CUSTOMERAScust;
And the output:
<PhoneBook><ContactName="Daniel"TEL="788255855"/><ContactName="Martin"TEL="889665447"/><ContactName="Eva"TEL="111222333"/><ContactName="Alena"TEL="444555666"/><ContactName="Oliver"TEL="777888999"/><ContactName="George"TEL="444882446"/><ContactName="Jamie"TEL="123456789"/></PhoneBook>
Samples are taken from javalobby article[7].
