Publishing SQL result sets in XML

There are two of XML functions for each action depending on the size of total result set after adding all the tags necessary to publish it in XML format:

• One that returns a maximum of LVARCHAR(32739)
• One that returns a CLOB data type

The input XML must include every value inside an element, not as an attribute. For example:

<employee>
  <givenname>Roy</givenname>
  <familyname>Connor</familyname>
  <address>
    <address1>123 First Street</address1>
    <city>Denver</city>
    <state/>CO</state>
    <zipcode>80111</zipcode>
  </address>
  <phone>303-555-1212</phone>
</employee>

The XML functions are summarized in the following table.

Table 1. XML publishing functions

Action	Function	Comments
Return rows of SQL results as XML elements.	The genxml() and genxmlclob() XML functions	Similar to FOR XML RAW in Microsoft™ SQL Server
Return each column value as separate elements.	ids_xpgenxmlelem.html	Similar to FOR XML AUTO, ELEMENTS in Microsoft SQL Server
Return an XML schema and result in XML format.	The genxmlschema() and genxmlschemaclob() XML functions	Similar to FOR XML AUTO, XMLSCHEMA in Microsoft SQL Server
Returns the result set of a query in XML format.	The genxmlquery() and genxmlqueryclob() XML functions	These functions accept a SQL query as a parameter.
Returns the result set of a query in XML with the XML header.	The genxmlqueryhdr() and genxmlqueryhdrclob() XML functions	Every XML document must have a header. These functions provide a quick method of generating a header.
Evaluates an XPATH expression on an XML column, document, or string.	The extract() and extractclob() XML functions	Similar to the Oracle extract() function.
Returns the value of the XML node	The extractvalue() and extractvalueclob() XML functions	Similar to the Oracle extractvalue() function.
Verify whether a specific node exists in an XML document.	The existsnode() XML function	Similar to the Oracle exists() function.
Parse an XML document to determine whether it is well formed.	The idsxmlparse() XML function