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.
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:
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.
| classid | class | subject |
|---|---|---|
| 1 | 125 | Chemistry |
| 2 | 250 | Physics |
| 3 | 375 | Mathematics |
| 4 | 500 | Biology |
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); 
Release date: March 2013