Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » XML in the database » Use of SQL/XML to obtain query results as XML

 

Use of 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
 See also