Home:Learn XQuery:XQuery Tutorial:Querying XML Columns :Using SQL Functions to Query XML

Using Database Vendor-Specific SQL Functions to Query XML

DataDirect XQuery® can call any SQL function as an external function. DB2 and Oracle provide SQL functions that allow you to return parts of an XML value stored in the database using path expressions similar to XPath 1.0.

For example, the DB2 extractCLOB function accepts two parameters; the first parameter is the XML to be queried, and the second parameter is a path expression to be evaluated against the XML.

To call an external function, you must first declare it in the query prolog:

declare function ddtek-sql:extractCLOB(
  $inp as node(), $xp as xs:string) as node() external;

Once the function is declared, it can be called in a query:

for $h in collection('HOLDINGSXML')/HOLDINGSXML/XMLCOL
return 
    ddtek-sql:extractCLOB(
      $h/node(),
      "/HOLDINGS")

In the preceding query, note that the first parameter is a path expression that points to the XML to be queried. The second parameter is a string containing the path expression to be used.

For more information about declaring and invoking SQL functions, refer to the DataDirect XQuery® User's Guide and Reference.

Using DB2 SQL Functions

The following examples show how to use some common DB2 SQL functions to query XML stored in the database.

When the DB2 function extractCLOB returns an XML value, the result can only be returned without further processing as part of the result of the complete XQuery expression. DataDirect XQuery® allows the following exception: the result of an extract function can be used as the content expression of an element constructor that is returned as-is from the XQuery expression. You can use the evaluate-in-memory extension expression to work around this limitation. This extension expression forces DataDirect XQuery® to evaluate the XQuery expression in memory.

In addition, DataDirect XQuery® does not support DB2 functions that return sequences of values, including extractIntegers, extractCLOBs, and so on.

NOTE: DB2 XML Extender functionality is not enabled by default; it must be explicitly enabled. In addition, the user ID executing DB2 SQL functions must have the correct permissions to use XML Extender functionality. Refer to your DB2 documentation for more information about enabling XML Extender functionality and setting permissions.

extractCLOB Function

This example returns the value of all HOLDINGS elements.

declare function ddtek-sql:DB2XML.extractCLOB(
  $inp as node(), $xp as xs:string) as node() external;
for $h in collection('HOLDINGSXML')/HOLDINGSXML/XMLCOL
return 
  ddtek-sql:DB2XML.extractCLOB(
    $h/node(),	
    "/HOLDINGS")

Using the evaluate-in-memory Extension Expression with the extractCLOB Function

In this example, notice how the argument of fn:node-name is the result of a SQL extractCLOB function that normally would have to be returned as-is from the expression. In this case, you must use the evaluate-in-memory extension expression to force DataDirect XQuery® to evaluate the XQuery expression in memory; otherwise, this query fails and raises an error.

declare function ddtek-sql:DB2XML.extractCLOB(
  $inp as node(), $xp as xs:string) as node() external;
for $h in collection('HOLDINGSXML')/HOLDINGSXML/XMLCOL
let $holdings := ddtek-sql:DB2XML.extractCLOB($h/node(),
   "/HOLDINGS")
let $holdings2 := (#ddtek:evaluate-in-memory#) {$holdings} 
for $n in $holdings2//(*|@*)
return
   fn:node-name($n)

Using Oracle SQL Functions

Now let's look at using Oracle SQL functions in the same way.

Similar to the DB2 extractCLOB function, when the Oracle function extract returns an XML value, the result can only be returned without further processing as part of the result of the complete XQuery expression. DataDirect XQuery® allows the following exception: the result of an extract function can be used as the content expression of an element constructor that is returned as-is from the XQuery expression. You can use the evaluate in-memory extension expression to force DataDirect XQuery® to evaluate the XML in memory.

Using our sample data, the following examples show how to use some common Oracle SQL functions to query XML stored in the database.

extract Function

This example returns all SHARE elements.

declare function ddtek-sql:extract(
  $inp as node(), $xp as xs:string) as node() external;
for $h in collection('HOLDINGSXML')/HOLDINGSXML/XMLCOL
return 
  <extract path='/SHARE'>{
    ddtek-sql:extract(
      $h/node(),
      "/HOLDINGS/SHARE")
  }</extract>
 

extractValue Function

This example returns the value of all SHARE elements for which the COMPANY attribute is set to "Progress Software".

declare function ddtek-sql:extractValue(
  $inp as node(), $xp as xs:string) as xs:string external;
for $h in collection('HOLDINGSXML')/HOLDINGSXML/XMLCOL
return 
  ddtek-sql:extractValue(
    $h/node(),
    "/HOLDINGS/SHARE[@COMPANY = 'Progress Software']")

Using the evaluate-in-memory Extension Expression with the extract Function

In this example, notice how the argument of the fn:node-name is the result of a SQL extract function that normally would have to be returned as-is from the expression. In this case, you must use the evaluate-in-memory extension expression to force DataDirect XQuery® to evaluate the XML in memory; otherwise, this query fails and raises an error.

declare function ddtek-sql:extract(
  $inp as node(), $xp as xs:string) as node() external;
for $h in collection('HOLDINGSXML')/HOLDINGSXML/XMLCOL
let $SHARE := ddtek-sql:extract($h/node(),"//SHARE")
return 
    (# ddtek:evaluate-in-memory #) {fn:node-name($SHARE)}