ASA SQL User's Guide
  Using XML in the Database
    Using SQL/XML to obtain query results as XML

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 sample database. It uses the following variable references:

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

• {$order_date}    Generates content for the <date> element using vaues from the order_date column in the sales_order table.

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

SELECT XMLGEN ( '<order>
                 <id>{$id}</id>
                 <date>{$order_date}</date>
                 <customer>{$customer}</customer>
                 </order>',
                sales_order.id,
                sales_order.order_date,
                customer.company_name AS customer)
 FROM sales_order JOIN customer
 ON customer.id = sales_order.cust_id

This query generates the following result:

<order>
 <id>2131</id>
 <date>2000-01-02</date>
 <customer>BoSox Club</customer>
</order>

<order>
 <id>2126</id>
 <date>2000-01-03</date>
 <customer>Leisure Time</customer>
</order>

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

<order>
 <id>2127</id>
 <date>2000-01-06</date>
 <customer>Creative Customs Inc.</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>{$order_date}</date>
                 <customer>{$customer}</customer>
                 </order>',
                sales_order.id,
                sales_order.order_date,
                customer.company_name AS customer
              ) AS order_info

FROM sales_order JOIN customer
 ON customer.id = sales_order.cust_id
 ORDER BY sales_order.order_date

This query generates the following result:

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

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

<order id="2065">
 <date>2000-01-03</date>
 <customer>Bloomfield&apos;s</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&apos;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 invalid 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 Adaptive Server Anywhere do not include header 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 fname, lname FROM customer FOR XML RAW) AS x )

This produces the following result:

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

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