Database-Specific Query Functions

One way to query XML data on relational databases relies on database-specific XML query features and their integration into DataDirect XQuery. The advantage of this approach is it leverages database capabilities (for example, XML indexing) and allows DataDirect XQuery to retrieve only the result of the query expression that is applied to the XML column.

The disadvantages of this approach are that XQuery you write for data stored on one database is not portable across different databases and database versions, and that the XML query capabilities of some databases are limited.

Note: The namespace for using database-specific XML functions in DataDirect XQuery is ddtek-sql.

This section covers the following topics:

Tip: If your database supports XML Type, see Querying XML Type Data for alternatives to using database-specific functions to query XML data on relational databases.

Querying XML on DB2

As mentioned in Supported Databases, IBM DB2 databases use different storage mechanisms for XML data – pureXML and XML Extender. The following table summarizes the different ways you can query XML data on DB2 depending on the type of XML storage being used.

Table 12-10. Ways to Query XML Data on DB2
Storage Mechanism
Support for XML Type
You Can Use
pureXML
Yes
XML Extender
No

Query Functions for DB2 XML Extender

DataDirect XQuery provides several extract functions that you can use to query XML data on DB2 when the XML Extender mechanism is in use. These functions are:

ddtek-sql:DB2XML.extracttype() 

extract[Integer|Smallint|Double|Real|Char|Varchar|Date|Time|Timestamp]

This set of DB2 extract functions (extractInteger, extractSmallint, and so on) extracts the element content or attribute value from an XML element or attribute node and returns the data as the type indicated by the function’s name. For example: 

declare function  
  ddtek-sql:DB2XML.extractDouble($inp as node(),$xp as xs:string) as 
  xs:double external; 
for $v1 in collection('holdingsxml')/holdingsxml 
return  
<shares>{ 
  ddtek-sql:DB2XML.extractDouble( 
    $v1/holdings/*, 
    '/holdings/share[@company="Progress Software"]') 
}</shares>

This example returns: 

<shares>23</shares> 
<shares>4000000</shares>

ddtek-sql:DB2XML.extracttypes()

The ddtek-sql:DB2XML.extracttypes() functionis equivalent to ddtek-sql:DB2XML.extracttype(), but instead of returning a single XML fragment, it returns a table. 

extract[Integers|Smallints|Doubles|Reals|Chars|Varchars|Dates|Times|Timestamps]

When using the extracttypes() extract function, you must declare it in the tableFunction element of the DataDirect XQuery source configuration file, for example: 

... 
<tableFunction name="EXTRACTDOUBLES"> 
  <resultSet> 
    <column name="RETURNEDDOUBLE" schemaType="double"/> 
  </resultSet> 
</tableFunction> 
...

See Using SQL Table Functions for more information on this topic.

The following example shows the usage of ddtek-sql:DB2XML.extractDoubles(): 

declare function ddtek-sql:DB2XML.extractDoubles($inp as node(),$xp as 
  xs:string)  
  as document-node(element()) external; 
for $v1 in collection('holdingsxml')/holdingsxml 
for $v2 in  
ddtek-sql:DB2XML.extractDoubles( 
  $v1/holdings/*, 
  '/holdings/share')/EXTRACTDOUBLES/RETURNEDDOUBLE/data(.) 
return $v2

This example returns: 

3000 4000 2500 23 3000 4000 40000 4.0E6

ddtek-sql:DB2XML.extractCLOB()

As stated in the IBM DB2 documentation, the extractCLOB function:

... extracts a fragment of XML documents, with element and attribute markup and content of elements and attributes, including sub-elements. This function differs from the other extract functions, which return only the content of elements and attributes. The extractClob(s) functions are used to extract document fragments, whereas extractVarchar(s) and extractChar(s) are used to extract simple values.

For example: 

declare function ddtek-sql:DB2XML.extractCLOB( 
  $inp as node(),$xp as xs:string) as node() external; 
for $v1 in collection('holdingsxml')/holdingsxml 
return  
ddtek-sql:DB2XML.extractCLOB( 
  $v1/holdings/*, 
  '/holdings/share[@company="Progress Software"]')

This example returns: 

<share company="Progress Software" userid="Jonathan">23</share> 
<share company="Progress Software" userid="Minollo">4000000</share>

ddtek-sql:DB2XML.extractCLOBS()

The extractCLOBS function is equivalent to extractCLOB, but instead of returning a single XML fragment, extractCLOBS returns a table.

In order to use extractCLOBS, you must declare the function in the DataDirect XQuery source configuration file as a table function. 

... 
<tableFunction name="EXTRACTCLOBS"> 
  <resultSet> 
    <column name="RETURNEDCLOB" schemaType="anyType"/> 
  </resultSet> 
</tableFunction> 
...

See Using SQL Table Functions for more information on this topic.

The following example shows the usage of ddtek-sql:DB2XML.extractCLOBS(): 

declare function ddtek-sql:DB2XML.extractCLOBS($inp as node(),$xp as 
xs:string)  
  as document-node(element()) external; 
for $v1 in collection('holdingsxml')/holdingsxml 
for $v2 in  
  ddtek-sql:DB2XML.extractCLOBS( 
    $v1/holdings/*, 
    '/holdings/share/@company')/EXTRACTCLOBS/RETURNEDCLOB 
return $v2

This example returns: 

<RETURNEDCLOB> 
  <share company="Amazon.com, Inc." userid="Jonathan">3000</share> 
</RETURNEDCLOB> 
<RETURNEDCLOB> 
  <share company="eBay Inc." userid="Jonathan">4000</share> 
</RETURNEDCLOB>

Query Functions for DB2 pureXML

Among the DB2 databases supported by DataDirect XQuery, DB2 pureXML is supported on DB2 V9.1, DB2 V9.5, and DB2 V9.1 for z/OS. All of these database versions also support XML Type. However, DB2 V9.1 for z/OS supports only XPath, and not XQuery.

You can use the following built-in functions to query XML Type data on DB2 databases using pureXML:

NOTE: If you want to use the database’s native XQuery support, the DB2 database server must be configured with the Unicode character set.

ddtek-sql:db2-xmlquery()

The function declarations for ddtek-sql:db2-xmlquery are: 

declare function ddtek-sql:db2-xmlquery($query as xs:string, $paramvalue as 
  item()*, $paramname as xs:string) as node()* external; 
declare function ddtek-sql:db2-xmlquery($query as xs:string) as  
  node()* external;

The following example shows the use of the built-in DB2 XQuery function db2-fn:xmlcolumn(string) to access XML Type data. In this example, the XQuery expression is querying the holdings column of the holdingsxml table. The holdings column contains XML Type data. 

ddtek-sql:db2-xmlquery(' 
  for $share in db2-fn:xmlcolumn("holdingsxml.holdings")//SHARE 
    [@COMPANY="Progress Software"] 
     return <progress-shares from="{$share/@userid}" number="{data($share)}"/> 
')

Example: ddtek-sql:db2-xmlquery() using external variables

The following example shows the use of an external variable ($var) to access XML Type data: 

for $v1 in collection('holdingsxml')/holdingsxml/holdings 
return ddtek-sql:db2-xmlquery(' 
       for $v in $var//SHARE[@COMPANY="Progress Software"] 
         return <progress-shares from="{$v/@userid}" number="{data($v)}"/>', 
       $v1/node(), "var")

A similar example using two external variables, $var and $companyName: 

for $v1 in collection('holdingsxml')/holdingsxml/holdings 
return ddtek-sql:db2-xmlquery(' 
       for $v in $var//share[@company=$CompanyName] 
         return <progress-shares from="{$v/@userid}" number="{data($v)}"/>', 
       $v1/node(), "var", 
       "Progress Software", "CompanyName")

ddtek-sql:db2-xmlparse()

The function declaration for ddtek-sql:db2-xmlparse() is: 

declare function ddtek-sql:db2-xmlparse($doc as xs:string) as 
  node()* external;

Here is an example: 

let $data := ' 
  &lt;holdings&gt; 
    &lt;SHARE COMPANY="Amazon.com, Inc."  
       userid="Jonathan"&gt;00003000.00&lt;/share&gt; 
    &lt;share company="eBay Inc." userid="Jonathan"&gt;00004000.00&lt;/share&gt; 
    &lt;share company="Int''l Business Machines C"  
       userid="Jonathan"&gt;00002500.00&lt;/share&gt; 
    &lt;share company="Progress Software"  
       userid="Jonathan"&gt;00000023.00&lt;/share&gt; 
    &lt;share company="Amazon.com, Inc."  
       userid="Minollo"&gt;00003000.00&lt;/share&gt; 
    &lt;share company="eBay Inc." userid="Minollo"&gt;00004000.00&lt;/share&gt; 
    &lt;share company="Lucent Technologies Inc."  
       userid="Minollo"&gt;00040000.00&lt;/share&gt; 
    &lt;share company="Progress Software"  
       userid="Minollo"&gt;04000000.00&lt;/share&gt; 
  &lt;/holdings&gt;' 
return ddtek-sql:db2-xmlquery(' 
       for $share in $p//share[@company="Progress Software"] 
        return <progress-shares from="{$share/@userid}" number="{data($share)}"/> ', 
        ddtek-sql:db2-xmlparse($data), "p")

Querying XML on Oracle

Oracle supports XPath-like queries on XML data stored in an XML Type column through these built-in SQL extension functions:

ddtek-sql:existsNode()

ddtek-sql:existsNode() accepts an XML value and an XPath expression as input and returns true if the XPath expression matches one or more nodes in the XML value. Refer to your Oracle documentation for details. 

declare function ddtek-sql:existsNode($inp as node(),$xp as xs:string) as 
  xs:boolean external; 
for $share in ("Amazon.com, Inc.","eBay") 
let $numberofshares :=  
  count(for $x in collection('holdingsxml')/holdingsxml 
    where ddtek-sql:existsNode( 
      $x/holdings/*, 
      concat("/holdings/share/@company[.='",$share,"']")) 
    return $x) 
return <number-of-shareholders share="{$share}" number="{$numberofshares}" />

This example returns: 

<number-of-shareholders share="Amazon.com, Inc." number="2"/> 
<number-of-shareholders share="eBay" number="0"/>

ddtek-sql:extractValue()

ddtek-sql:extractValue() accepts an XML value and an XPath expression as input and returns the scalar value of the node selected by the XPath expression. Refer to your Oracle documentation for details. 

declare function ddtek-sql:existsNode($inp as node(),$xp as xs:string) as  
  xs:boolean external; 
declare function ddtek-sql:extractValue($inp as node(),$xp as xs:string) as 
  xs:untypedAtomic external; 
for $share in ("Progress Software","eBay") 
for $x in collection('holdingsxml')/holdingsxml 
where ddtek-sql:existsNode( 
    $x/holdings/*, 
    concat("/holdings/share/@company[.='",$share,"']")) 
return  
  <share share='{$share}'  
    holder='{ddtek-sql:extractValue( 
      $x/holdings/*, 
      fn:concat("/holdings/share[@company='", $share ,"']/@userid"))}'  
    number='{ddtek-sql:extractValue( 
      $x/holdings/*, 
      fn:concat("/holdings/share[@company='", $share ,"']"))}' />

This example returns: 

<share share="Progress Software" holder="Jonathan" number="23"/> 
<share share="Progress Software" holder="Minollo" number="4000000"/>

ddtek-sql:extract()

ddtek-sql:extract() accepts an XML value and an XPath expression as input and returns an XML fragment that contains the nodes selected by the XPath expression. Refer to your Oracle documentation for more details. 

declare function ddtek-sql:extract($inp as node(),$xp as xs:string) as  
  node() external; 
declare function ddtek-sql:existsNode($inp as node(),$xp as xs:string) as 
  xs:boolean external; 
for $share in ("Amazon.com, Inc.","Progress Software","eBay") 
for $x in collection('holdingsxml')/holdingsxml 
where ddtek-sql:existsNode( 
    $x/holdings/*, 
    concat("/holdings/share/@company[.='",$share,"']")) 
return  
ddtek-sql:extract($x/holdings/*,fn:concat("/holdings/share[@company='", 
  $share ,"']"))

This example returns: 

<share company="Amazon.com, Inc." userid="Jonathan">3000</share> 
<share company="Progress Software" userid="Jonathan">23</share> 
<share company="Amazon.com, Inc." userid="Minollo">3000</share> 
<share company="Progress Software" userid="Minollo">4000000</share>

Because the result of ddtek-sql:extract() is a single XML fragment, limitations exist as to where this function can be used in an XQuery expression. The following usages of ddtek-sql:extract() are allowed:

Here is an example of using ddtek-sql:extract() that is not allowed: 

declare function ddtek-sql:extract($inp as node(),$xp as xs:string) as 
  node() external; 
declare function ddtek-sql:existsNode($inp as node(),$xp as xs:string) as 
  xs:boolean external; 
for $share in ("Amazon.com, Inc.","Progress Software","eBay") 
for $v1 in collection('holdingsxml')/holdingsxml 
for $v2 in ddtek-sql:extract($v1/holdings/*,"/holdings/share") 
where xs:string($v2/@userid) = "Minollo" 
return $v2

This example fails and DataDirect XQuery returns the following error: 

[DataDirect][XQuery]The value of the XML column "holdings" can only be used 
as a return value.

Example: As Input to Another Oracle SQL Function

The following example shows how to use the result of
ddtek-sql:extract() as input to ddtek-sql:extractValue(): 

declare function ddtek-sql:extract($inp as node(),$xp as xs:string) as  
  node() external; 
declare function ddtek-sql:extractValue($inp as node(),$xp as xs:string)  
  as xs:untypedAtomic external; 
for $v1 in collection('holdingsxml')/holdingsxml 
let $v2 := ddtek-sql:extract($v1/holdings/*,'/holdings/share') 
let $v3 := ddtek-sql:extractValue($v2, 
  '/share[@company="Progress Software"]/@userid') 
where xs:string($v3) = "Minollo" 
return $v2

Example: Inside a ddtek:evaluate-in-memory Extension Expression

This example shows how to use the result of ddtek-sql:extract() with the ddtek:evaluate-in-memory extension expression. 

declare function ddtek-sql:extract($inp as node(),$xp as xs:string) as  
  node() external; 
for $v1 in collection('holdingsxml')/holdingsxml 
let $v2 := ddtek-sql:extract($v1/holdings/*,'/holdings') 
for $v3 in (# ddtek:evaluate-in-memory #) {$v2/share} 
return  
  <shares from='{$v3/@userid}' for='{$v3/@company}' number='{$v3}' />

ddtek-sql:xmlSequence()

ddtek-sql:xmlSequence() accepts an XML value (fragment) as input and transforms it into a sequence. This is useful if you want to iterate over the result of a previously invoked ddtek-sql:extract().

You must declare ddtek-sql:xmlSequence() in the DataDirect XQuery source configuration file as a table function. 

... 
<schema name=""> 
  <tableFunction name="XMLSEQUENCE"> 
    <resultSet> 
      <column name="COLUMN_VALUE" schemaType="anyType"/> 
    </resultSet> 
  </tableFunction> 
</schema> 
... 
declare function ddtek-sql:extract($inp as node(),$xp as xs:string) as 
  node() external; 
declare function ddtek-sql:XMLSEQUENCE($inp as node()) as 
  document-node(element()) external; 
for $v1 in collection('holdingsxml')/holdingsxml 
for $v3 in  
  ddtek-sql:XMLSEQUENCE( 
ddtek-sql:extract($v1/holdings/*,'/holdings/share'))/XMLSEQUENCE/COLUMN_VALUE/* 
return <shareinfo>{$v3/*}</shareinfo>

This example returns: 

<shareinfo> 
  <share company="Amazon.com, Inc." userid="Jonathan">3000</share> 
</shareinfo> 
<shareinfo> 
  <share company="eBay Inc." userid="Jonathan">4000</share> 
</shareinfo> 
...

ddtek-sql:ora-xmlquery()

Oracle 10g R2 and higher support native XQuery support using SQL extensions that allow combining SQL statements and XQuery expressions. Refer to your Oracle documentation for details.

DataDirect XQuery supports the Oracle XMLQUERY statement using a built-in XQuery function, ddtek-sql:ora-xmlquery. Other Oracle XQuery related features can be supported easily through user-defined functions that wrap the SQL extensions and that can be invoked using DataDirect XQuery’s SQL function support. (See Using SQL Functions.)

DataDirect XQuery supports the following functional declarations of ddtek-sql:ora-xmlquery(): 

declare function ddtek-sql:ora-xmlquery($query as xs:string, $context-item as 
  node()?) as node()? external; 
declare function ddtek-sql:ora-xmlquery($query as xs:string) as  
  node()? external;

Using the Oracle XQuery engine, the function evaluates the XQuery expression passed by the $query parameter. The initial context item from the XQuery expression is initialized with the value of $context-item (Refer to your Oracle documentation for details about the meaning and usage of the initial context item). For example: 

for $x in collection('holdingsxml')/holdingsxml/holdings 
return  
  ddtek-sql:ora-xmlquery( 
    'for $v in //share[@company="Progress Software"]  
     return <progress-shares from="{$v/@userid}" number="{data($v)}"/>', 
       $x/node() 
    )

This example returns: 

<progress-shares from="Jonathan" number="23"/> 
<progress-shares from="Minollo" number="4000000"/>

Another example: 

ddtek-sql:ora-xmlquery('ora:view("holdings")/ROW')

This example returns: 

<ROW> 
  <userid>Jonathan</userid> 
  <stockticker>PRGS</stockticker> 
  <shares>23</shares> 
</ROW> 
...

Note that the result of ddtek-sql:ora-xmlquery() is similar to the result returned from ddtek-sql:extract(). The result is an XML fragment and the same restrictions and guidelines apply.

Querying XML on Microsoft SQL Server 2005

Microsoft SQL Server 2005 introduced native XQuery support using SQL extensions that allow combining SQL statements and XQuery expressions. Refer to your Microsoft SQL Server documentation for details.

DataDirect XQuery supports these Microsoft SQL Server 2005 (and higher) SQL extensions using the following proprietary (predeclared) XQuery functions:

These functions correspond to the Microsoft SQL Server 2005 query(), value(), exist(), and nodes() methods.

ddtek-sql:sqs-query()

This function evaluates the Microsoft SQL Server 2005 query() method using $context-item as the initial context item, if provided. Refer to your Microsoft SQL Server 2005 documentation for details about query(). 

declare function ddtek-sql:sqs-query($context-item as node(), $query as 
  xs:string) as node()? external; 
declare function ddtek-sql:sqs-query($query as xs:string) as 
  node()? external;

For example: 

for $x in collection('holdingsxml')/holdingsxml/holdings 
return  
  ddtek-sql:sqs-query( 
    $x/node(), 
    'for $v in //share[@company="Progress Software"]  
     return <progress-shares from="{$v/@userid}" number="{data($v)}"/>' 
    )

This example returns: 

<progress-shares from="Jonathan" number="23"/> 
<progress-shares from="Minollo" number="4000000"/>

ddtek-sql:sqs-value()

This function invokes the Microsoft SQL Server 2005 value() method on the XML value provided by $context-item. Refer to your Microsoft SQL Server documentation for details about value(). 

declare function ddtek-sql:sqs-value( 
$context-item as node(),  
$query as xs:string,  
$type as xs:string)  
  as xs:anyAtomicType? external;

For example: 

for $x in collection('holdingsxml')/holdingsxml/holdings 
return  
  ddtek-sql:sqs-value( 
    $x/node(), 
    '(for $v in //share[@company="Progress Software"] return $v)[1]','bigint' 
    )

This example returns: 

23 4000000

ddtek-sql:sqs-exist()

This function invokes the Microsoft SQL Server 2005 exist() method on the XML value provided by $context-item. Refer to your Microsoft SQL Server documentation for details about exist(). 

declare function ddtek-sql:sqs-exist( 
$context-item as node(),  
$query as xs:string) as xs:boolean external;

ddtek-sql:sqs-nodes()

This is a predeclared SQL table function that returns a document node containing a sequence of sqs-nodes elements, each of which contains a single col subelement. Refer to your Microsoft SQL Server documentation for details about node(). 

declare function ddtek-sql:sqs-nodes( 
$context-item as node(),  
$query as xs:string) as document-node(element()) external;

For example: 

for $i in collection('holdingsxml')/holdingsxml/holdings 
for $j in ddtek-sql:sqs-nodes($i/*,"//share")/sqs-nodes/col 
return ddtek-sql:sqs-query($j/*,'.')

This example returns: 

<share company="Amazon.com, Inc." userid="Jonathan">3000</share> 
<share company="eBay Inc." userid="Jonathan">4000</share> 
<share company="Int'l Business Machines C" userid="Jonathan">2500</share> 
<share company="Progress Software" userid="Jonathan">23</share> 
<share company="Amazon.com, Inc." userid="Minollo">3000</share> 
<share company="eBay Inc." userid="Minollo">4000</share> 
<share company="Lucent Technologies Inc." userid="Minollo">40000</share> 
<share company="Progress Software" userid="Minollo">4000000</share>

Note that the result of ddtek-sql:sqs-nodes() can only be used as input to another ddtek-sql:sqs-xxxx function.