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 | ||
---|---|---|
|
||
|
||
|
||
|
||
... |
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 | ||
---|---|---|
|
||
|
||
|
||
|
||
... |
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.
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. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |