Support for XML-Typed Data

DB2, Oracle, and Microsoft SQL Server 2005 support a native XML data type. DataDirect XQuery can integrate the XML data stored in XML columns using a combination of DataDirect XQuery functionality and database-specific features. This section describes DataDirect XQuery’s support for XML-typed data and provides examples of combining DataDirect XQuery functionality with database-specific features.

Assume a database table, holdingsxml, containing the following data:

userid
holdings 
Jonathan 

<holdings> 
  <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> 
</holdings> 

Minollo 

<holdings> 
  <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> 
</holdings>

Example: Returning the XML Information

The following queries return the XML information; these two queries are equivalent. 

collection('holdingsxml')/holdingsxml/holdings/*

or 

collection('holdingsxml')/holdingsxml/holdings/node()

Result 

<holdings> 
  <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> 
</holdings> 
<holdings> 
  <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> 
</holdings>

Note how you must specify either holdings/node() or holdings/* to access the content of the holdings column. If you specify only holdings, the result is: 

<holdings> 
  <holdings> 
    <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> 
  </holdings> 
</holdings> 
<holdings> 
  <holdings> 
    <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> 
  </holdings> 
</holdings>

The additional holdings wrapper element in this example is the result of the default SQL/XML mapping (XMLFOREST) of the holdings column.

Example: Querying the XML Information

You cannot use path expressions, except for node() or *, or other XQuery expressions on the content of the holdings column. For example, if you specify: 

collection('holdingsxml')/holdingsxml/holdings//share

the following message is returned: 

Path expressions on XML column "holdings" are not 
supported.

Two approaches are available to work around this limitation:

The advantages and disadvantages of these approaches are explained next.

Evaluate in Memory

You can evaluate in memory a path expression or any XQuery expression on the value of the XML column. To do this, use the evaluate-in-memory extension expression.

For example, use the following query: 

let $v1 := collection('holdingsxml')/holdingsxml/holdings 
return (# ddtek:evaluate-in-memory #) {$v1//share}

The advantage of this approach is that it is portable across databases and that it allows you to use the complete DataDirect XQuery feature set to query the XML stored in the database.

The disadvantage of this approach is that it requires the instantiation of all the XML even if only part of it is needed. If the XML column contains large amounts of XML, performance and scalability can decrease.

Database-Specific XML Query Features

The second approach relies on the 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 disadvantage of this approach is that it is not portable across different databases and database versions and that the XML query capabilities of some databases are limited.

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

Some of the supported database-specific XML functions are SQL table functions. See "Using SQL Table Functions" for information about how DataDirect XQuery supports SQL table functions.

DB2 Examples

DB2 provides a set of extract functions that support the XML-typed data.

Example: ddtek-sql:DB2XML.extractxxx() 

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

This set of DB2 extract functions (extractInteger, extractDouble, 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>

Example: ddtek-sql:DB2XML.extractxxx()

This set of extract functions are equivalent to the ones in the preceding example, but instead of returning a single XML fragment, they return a table. 

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

When using any of these extract functions, 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> 
...

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

Example: 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>

Example: ddtek-sql:DB2XML.extractCLOBS()

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

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> 
...

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>

DB2 V9.1 for Linux/UNIX/Windows Examples

DB2 V9.1 introduces native XQuery support. Refer to your DB2 documentation for details.

DataDirect XQuery supports the DB2 native XQuery functionality using the following proprietary (predeclared) XQuery functions:

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 function declaration for ddtek-sql:db2-xmlparse() is: 

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

NOTE: You must be connected to a DB2 V9.1 database server that is configured with the Unicode character set to use native XQuery support.

Example: ddtek-sql:db2-xmlquery() using a built-in DB2 XQuery function

The following example shows the use of the built-in DB2 XQuery function db2-fn:xmlcolumn(string) to access XML-typed data. In this example, the XQuery expression is querying the holdings column of the holdingsxml table. The holdings column contains XML-typed 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 examples shows the use of an external variable ($var) to access XML-typed 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")

Example: ddtek-sql:db2-xmlparse() 

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")

Oracle Examples

Oracle supports XPath-like queries on XML data stored in an XML-typed column through proprietary SQL extension functions.

Example: 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"/>

Example: 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"/>

Example: 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}' />

Example: As Input to 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; 
declare function ddtek-sql:xmltype.getclobval($p as node()*) as 
  node() 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>{ddtek-sql:xmltype.getclobval($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> 
...

Oracle 10g R2 Examples

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 proprietary (predeclared) 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 two versions 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.

Microsoft SQL Server 2005 Examples

Microsoft SQL Server 2005 introduces 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 the Microsoft SQL Server 2005 SQL extensions using the following proprietary (predeclared) XQuery functions:

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

Example: ddtek-sql:sqs-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;

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

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"/>

Example: ddtek-sql:sqs-value() 

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

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().

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

Example: ddtek-sql:sqs-exist() 

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

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().

Example: ddtek-sql:sqs-nodes() 

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

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().

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.