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

If you want the order ID number to appear as an attribute of the <order> element, you would write query as follows (note that the variable reference is contained in double quotes because it specifies an attribute value):

SELECT XMLGEN ( '<order 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.OrderDate;

This query generates the following result:

order_info

<order ID="2131">
 <date>2000-01-02</date>
 <customer>BoSox Club</customer>
</order>

<order ID="2065">
 <date>2000-01-03</date>
 <customer>Bloomfield&apos;s</customer>
</order>

<order ID="2126">
 <date>2000-01-03</date>
 <customer>Leisure Time</customer>
</order>

<order ID="2127">
 <date>2000-01-06</date>
 <customer>Creative Customs Inc.</customer>
</order>

...

In both result sets, the customer name Bloomfield's is quoted as Bloomfield's because the apostrophe is a special character in XML and the column the <customer> element was generated from was not of type XML.

For more information about quoting of illegal characters in XMLGEN, see Invalid names and SQL/XML.

Specifying header information for XML documents

The FOR XML clause and the SQL/XML functions supported by SQL Anywhere do not include version declaration information in the XML documents they generate. You can use the XMLGEN function to generate header information.

SELECT XMLGEN( '<?xml version="1.0" 
                encoding="ISO-8859-1" ?>
                <r>{$x}</r>',
                (SELECT GivenName, Surname 
      FROM Customers FOR XML RAW) AS x );

This produces the following result:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<r>
 <row GivenName="Michaels" Surname="Devlin"/>
 <row GivenName="Beth" Surname="Reiser"/>
 <row GivenName="Erin" Surname="Niedringhaus"/>
 <row GivenName="Meghan" Surname="Mason"/>
 ...
</r>

For more information about the XMLGEN function, see XMLGEN function [String].

Discuss this page in DocCommentXchange.