SQL/XML or XML-Related Specifications is part 14 of the Structured Query Language (SQL) specification. In addition to the traditional predefined SQL data types like
NUMERIC, CHAR, TIMESTAMP, ... it introduces the predefined data type
XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database.
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 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).
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, to which the data for PostgreSQL 9.1, 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|
NOTE: only Oracle, IBM DB2 and MS-SQL-Server have been augmented with XQuery.
The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):
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;
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.
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.