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

The genxmlclob() syntax

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	class	subject
1	125	Chemistry
2	250	Physics
3	375	Mathematics
4	500	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);