Using the XMLGEN function

The XMLGEN function is used to generate an XML value based on an XQuery constructor.

The XML generated by the following query provides information about customer orders in the SQL Anywhere sample database. It uses the following variable references:

  • {$ID}   Generates content for the <ID> element using values from the ID column in the SalesOrders table.

  • {$OrderDate}   Generates content for the <date> element using values from the OrderDate column in the SalesOrders table.

  • {$Customers}   Generates content for the <customer> element from the CompanyName column in the Customers table.



SELECT XMLGEN ( '<order>
              <ID>{$ID}</ID>
              <date>{$OrderDate}</date>
              <customer>{$Customers}</customer>
              </order>',
              SalesOrders.ID,
              SalesOrders.OrderDate,
              Customers.CompanyName AS Customers 
              ) AS order_info      
FROM SalesOrders JOIN Customers
ON Customers.ID = SalesOrders.CustomerID
ORDER BY SalesOrders.CustomerID;

This query generates the following result:

order_info
<order>
 <ID>2001</ID>
 <date>2000-03-16</date>
 <customer>The Power Group</customer>
</order>
<order>
 <ID>2005</ID>
 <date>2001-03-26</date>
 <customer>The Power Group</customer>
</order>
<order>
 <ID>2125</ID>
 <date>2001-06-24</date>
 <customer>The Power Group</customer>
</order>
<order>
 <ID>2206</ID>
 <date>2000-04-16</date>
 <customer>The Power Group</customer>
</order>
...
 Generating attributes
 Specifying header information for XML documents