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

Using FOR XML RAW

When you specify FOR XML RAW in a query, each row is represented as a <row> element, and each column is an attribute of the <row> element.

Syntax 

FOR XML RAW[, ELEMENTS ]

Parameters 

ELEMENTS    tells FOR XML RAW to generate an XML element, instead of an attribute, for each column in the result. If there are NULL values, the element is omitted from the generated XML document. The following query generates <emp_id> and <dept_name> elements:

SELECT employee.emp_id, department.dept_name
FROM employee JOIN department
   ON employee.dept_id=department.dept_id
FOR XML RAW, ELEMENTS

and gives the following result:

<row>
   <emp_id>102</emp_id>
   <dept_name>R &amp; D</dept_name>
</row>
<row>
   <emp_id>105</emp_id>
   <dept_name>R &amp; D</dept_name>
</row>

<row>
   <emp_id>160</emp_id>
   <dept_name>R &amp; D</dept_name>
</row>
<row>
   <emp_id>243</emp_id>
   <dept_name>R &amp; D</dept_name>
</row>
...

Usage 

Data in BINARY, LONG BINARY, IMAGE, and VARBINARY columns is automatically returned in base64-encoded format when you execute a query that contains FOR XML RAW.

By default, NULL values are omitted from the result. This behavior is controlled by the FOR_XML_NULL_TREATMENT option.

For information about how NULL values are returned in queries that contain a FOR XML clause, see FOR XML and NULL values.

FOR XML RAW 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 RAW))

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

The attribute or element names used in the XML document can be changed 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 RAW

and gives the following result:

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

The order of the results depend on the plan chosen by the optimizer, unless you request otherwise. If you wish the results to appear in a particular order, you must include an ORDER BY clause in the query, 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 RAW

Example 

Suppose you want to retrieve information about which department an employee belongs to, as follows:

SELECT employee.emp_id, department.dept_name
FROM employee JOIN department
   ON employee.dept_id=department.dept_id
FOR XML RAW

The following XML document is returned:

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