Contents Index Using FOR XML RAW Using FOR XML EXPLICIT

ASA SQL User's Guide
  Using XML in the Database
    Obtaining query results as XML

Using FOR XML AUTO


AUTO mode generates nested elements within the XML document. Each table referenced in the select list is represented as an element in the generated XML. The order of nesting is based on the order in which tables are referenced in the select list. When you specify AUTO mode, an element is created for each table in the select list, and each column in that table is a separate attribute.

Syntax 

FOR XML AUTO[, ELEMENTS ]

Parameters 

ELEMENTS    tells FOR XML AUTO to generate an XML element, instead of an attribute, for each column in the result. For example,

SELECT employee.emp_id, department.dept_name
FROM employee JOIN department
   ON employee.dept_id=department.dept_id
ORDER BY emp_id
FOR XML AUTO, ELEMENTS

In this case, each column in the result set is returned as a separate element, rather than as an attribute of the <employee> element. If there are NULL values, the element is omitted from the generated XML document.

<employee>
   <emp_id>102</emp_id>
   <department>
      <dept_name>R &amp; D</dept_name>
   </department>
</employee>
<employee>
   <emp_id>105</emp_id>
   <department>
      <dept_name>R &amp; D</dept_name>
   </department>
</employee>
<employee>
   <emp_id>160</emp_id>
   <department>
   <dept_name>R &amp; D</dept_name>
   </department>
</employee>
...
Usage 

When you execute a query using FOR XML AUTO, data in BINARY, LONG BINARY, IMAGE, and VARBINARY columns is automatically returned in base64-encoded format. By default, NULL values are omitted from the result. You can return NULL values as empty attributes by setting the FOR_XML_NULL_TREATMENT option to EMPTY.

For information about setting the FOR_XML_NULL_TREATMENT option, see FOR_XML_NULL_TREATMENT option [database].

Unless otherwise requested, the database server returns the rows of a table in an order that has no meaning. If you wish the results to appear in a particular order, or for a parent element to have multiple children, you must include an ORDER BY clause in the query so that all children are adjacent. If you do not specify an ORDER BY clause, the nesting of the results depends on the plan chosen by the optimizer and you may not get the nesting you desire.

FOR XML AUTO does not return a well-formed XML document because the document does not have a single root node. If a <root> element is required, one way to insert one is to use the XMLELEMENT function. For example,

SELECT XMLELEMENT( NAME root,
                   (SELECT emp_id AS id, emp_fname AS name
                   FROM employee FOR XML AUTO ) )

For more information about the XMLELEMENT function, see XMLELEMENT function [String].

You can change the attribute or element names used in the XML document by specifying aliases. The following query renames the id attribute to product_id:

SELECT id AS product_id
FROM product
WHERE color='black'
FOR XML AUTO

The following XML is generated:

<product product_id="302"/>
<product product_id="400"/>
<product product_id="501"/>
<product product_id="700"/>

You can also rename the table with an alias. The following query renames the table to product_info:

SELECT id AS product_id
FROM product AS product_info
WHERE color='black'
FOR XML AUTO

The following XML is generated:

<product_info product_id="302"/>
<product_info product_id="400"/>
<product_info product_id="501"/>
<product_info product_id="700"/>
Example 

The following query generates XML that contains both <employee> and <department> elements, and the <employee> element (the table listed first in the select list) is the parent of the <department> element.

SELECT employee.emp_id, department.dept_name
FROM employee JOIN department
   ON employee.dept_id=department.dept_id
ORDER BY emp_id
FOR XML AUTO

The following XML is generated by the above query:

<employee emp_id="102">
   <department dept_name="R &amp; D"/>
</employee>
<employee emp_id="105">
   <department dept_name="R &amp; D"/>
</employee>
<employee emp_id="160">
   <department dept_name="R &amp; D"/>
</employee>
<employee emp_id="243">
   <department dept_name="R &amp; D"/>
</employee>
...

If you change the order of the columns in the select list as follows:

SELECT department.dept_name, employee.emp_id
FROM employee JOIN department
   ON employee.dept_id=department.dept_id
ORDER BY 1, 2
FOR XML AUTO

the result is nested as follows:

<department dept_name="R &amp; D">
   <employee emp_id="102"/>
   <employee emp_id="105"/>
   <employee emp_id="160"/>
   ...
</department>
<department dept_name="Sales">
   <employee emp_id="129"/>
   <employee emp_id="195"/>
   <employee emp_id="299"/>
   ...
</department>
...

Again, the XML generated for the query contains both <employee> and <department> elements, but in this case the <department> element is the parent of the <employee> element.


Contents Index Using FOR XML RAW Using FOR XML EXPLICIT