Publishing SQL result sets in XML

Several functions let you publish XML from SQL queries.

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