XQuery

SQL/XML

We've seen how you can query XML data stored in a table, but how do you select relational data and return it as XML? This is where SQL/XML comes in. SQL/XML is an extension to SQL that is part of the ISO SQL 2003 standard. It adds an XML datatype, and allows XML elements to be constructed within the SQL query. Example 24-5 shows an SQL/XML query that might be used on our catalog table (Table 24-1).

Example 24-5. SQL/XML query

SELECT c.number,
       XMLELEMENT ( NAME "product",
                    XMLATTRIBUTES (
                      c.dept AS "dept",
                      c.name AS "prodname",
                   ) AS "product_as_xml"
FROM catalog c;

Table 24-12 shows the results, which consist of two columns. The product_as_xml column, whose type is XML, contains a newly constructed product element for each row.

Table 24-12. Results of SQL/XML query

number	product_as_xml
557	<product dept="WMN" prodname="Fleece Pullover"/>
563	<product dept="ACC" prodname="Floppy Sun Hat"/>
443	<product dept="ACC" prodname="Deluxe Travel Bag"/>
784	<product dept="MEN" prodname="Cotton Dress Shirt"/>

SQL/XML is not used to query XML documents, only relational data. As you can see, it can turn relational data into XML elements. For more information on SQL/XML, please see http://www.sqlx.org.