Using the FOR XML clause to retrieve query results as XML

SQL Anywhere allows you to execute a SQL query against your database and return the results as an XML document by using the FOR XML clause in your SELECT statement. The XML document is of type XML.

For information about the XML data type, see XML data type.

The FOR XML clause can be used in any SELECT statement, including subqueries, queries with a GROUP BY clause or aggregate functions, and view definitions.

For examples of how the FOR XML clause can be used, see FOR XML examples.

SQL Anywhere does not generate a schema for XML documents generated by the FOR XML clause.

Within the FOR XML clause, you specify one of three XML modes that control the format of the XML that is generated:

  • RAW   represents each row that matches the query as an XML <row> element, and each column as an attribute.

    For more information, see Using FOR XML RAW.

  • AUTO   returns query results as nested XML elements. Each table referenced in the select-list is represented as an element in the XML. The order of nesting for the elements is based on the order of the tables in the select-list.

    For more information, see Using FOR XML AUTO.

  • EXPLICIT   allows you to write queries that contain information about the expected nesting so you can control the form of the resulting XML.

    For more information, see Using FOR XML EXPLICIT.

The following sections describe the behavior of all three modes of the FOR XML clause regarding binary data, NULL values, and invalid XML names. The section also includes examples of how you can use the FOR XML clause.

FOR XML and binary data
FOR XML and NULL values
Encoding illegal XML names
FOR XML examples

Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0