ASA SQL User's Guide
  Using XML in the Database

Obtaining query results as XML

Adaptive Server Anywhere supports two different ways to obtain query results from your relational data as XML:

• FOR XML clause    The FOR XML clause can be used in a SELECT statement to generate an XML document.

  For information about using the FOR XML clause, see Using the FOR XML clause to retrieve query results as XML and SELECT statement.

• SQL/XML    Adaptive Server Anywhere supports functions based on the draft SQL/XML standard that generate XML documents from relational data.

  For information about using one or more of these functions in a query, see Using SQL/XML to obtain query results as XML.

The FOR XML clause and the SQL/XML functions supported by Adaptive Server Anywhere give you two alternatives for generating XML from your relational data. In most cases, you can use either one to generate the same XML.

For example, this query uses FOR XML AUTO to generate XML,

SELECT id, name
FROM product
WHERE color='black'
FOR XML AUTO

and this query uses the XMLELEMENT function to generate XML:

SELECT XMLELEMENT(NAME product,
          XMLATTRIBUTES(id, name))
FROM product
WHERE color='black'

Both queries generate the following XML:

<product id="302" name="Tee Shirt"/>
<product id="400" name="Baseball Cap"/>
<product id="501" name="Visor"/>
<product id="700" name="Shorts"/>

Tip 

If you are generating deeply-nested documents, a FOR XML EXPLICIT query will likely be more efficient than a SQL/XML query because EXPLICIT mode queries normally use a UNION to generate nesting, while SQL/XML uses subqueries to generate the required nesting.

Using the FOR XML clause to retrieve query results as XML
Using FOR XML RAW
Using FOR XML AUTO
Using FOR XML EXPLICIT