FOR XML AUTO[, ELEMENTS ]

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.

Unless otherwise requested, the database server returns the rows of a table in an order that has no meaning. If you want the results to appear in a particular order, or for a parent element to have multiple children, 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 want.

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 EmployeeID AS id, GivenName AS name
                   FROM Employees FOR XML AUTO ) );

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 Products
WHERE Color='Black'
FOR XML AUTO;

The following XML is generated:

<Products product_ID="302"/>
<Products product_ID="400"/>
<Products product_ID="501"/>
<Products 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 Products 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"/>

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 EmployeeID, DepartmentName
FROM Employees AS employee 
JOIN Departments AS department
   ON employee.DepartmentID=department.DepartmentID
ORDER BY EmployeeID
FOR XML AUTO;

The following XML is generated by the above query:

<employee EmployeeID="102">
   <department DepartmentName="R &amp; D"/>
</employee>
<employee EmployeeID="105">
   <department DepartmentName="R &amp; D"/>
</employee>
<employee EmployeeID="129">
   <department DepartmentName="Sales;"/>
</employee>
<employee EmployeeID="148">
   <department DepartmentName="Finance;"/>
</employee>
...

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

SELECT DepartmentName, EmployeeID
FROM Employees AS employee JOIN Departments AS department
   ON employee.DepartmentID=department.DepartmentID
ORDER BY 1, 2
FOR XML AUTO;

The result is nested as follows:

<department DepartmentName="Finance">
   <employee EmployeeID="148"/>
   <employee EmployeeID="390"/>
   <employee EmployeeID="586"/>
   ...
</department> 
<department DepartmentName="Marketing">
   <employee EmployeeID="184"/>
   <employee EmployeeID="207"/>
   <employee EmployeeID="318"/>
   ...
</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.

• for_xml_null_treatment option
• XMLELEMENT function [String]