The genxml() and genxmlclob() XML functions

Return rows of SQL results as XML elements. Use genxmlclob if the returned row is greater than LVARCHAR(32739).

Purpose

Use these functions to create an XML row element for each row that is returned from an SQL query. Each column is an attribute of the row element. Use genxml for returned row values that are LVARCHAR(32739) or less. For larger values, use genxmlclob, which returns a CLOB.

These aggregate functions process the rows before an ORDER BY is completed. If order is important, use the derived table queries to get the result set in the correct order, and then apply the function on the result set. See Enforcing order for details.

The genxml() syntax

genxml (root_element,rows )

The genxmlclob() syntax

genxmlclob( root_element,rows)

Parameters

root_element
The table name or names of columns to return. To return all columns, specify the table name.
rows
The name given to the XML element of the returned row.

Example 1

This example shows how to retrieve XML rows from an SQL query on the following table:
Table 1. The classes table
classid classsubject
1125Chemistry
2250 Physics
3 375Mathematics
4500 Biology
The first parameter, classes, is the name of the table, which indicates to return all rows. The second parameter, row, is the name of the XML element that contains each returned row.
SELECT genxml(classes, "row") from classes;
The following lines show the results of the query in XML. The attributes in the rows are the names of the table columns.
<row classid="1" class="125" subject="Chemistry"/>
<row classid="2" class="250" subject="Physics"/>
<row classid="3" class="375" subject="Mathematics"/>
<row classid="4" class="500" subject="Biology"/>

Example 2

From the same table as Example 1, this example returns only the columns classid and class.
SELECT genxml(row(classid, class), "row") from classes;
<row classid="1" class="125" />
<row classid="2" class="250"/>
<row classid="3" class="375" />
<row classid="4" class="500" />

Example 3

This example uses genxmlclob() because a large result set is expected.
SELECT genxmlclob(row(Customers.Customid, Orders.Orderid, 
       Customers.ContactName), "row")
From Customers, Orders
Where Customers.CustomerID = Orders.orderid;
This sample output shows only the first three rows:
<row Customerid="ALFKI" Orderid="10643" ContactName="Maria Anders"/>
<row Customerid="ALFKI" Orderid="10692" ContactName="Maria Anders"/>
<row Customerid="ALFKI" Orderid="10702" ContactName="Maria Anders"/>
.
.
.

Enforcing order

You can enforce the order of elements in XML document
SELECT genxml(row(c1, c2, c3), row) 
FROM (SELECT a, b, c from t order by c, d) 
AS vt(c1, c2, c3);