Using External Functions

---

This topic explains the types of external functions supported by DataDirect XQuery and how to use them. This topic covers the following subjects:

Supported External Functions
Querying Multiple Files in a Directory
Using SQL Functions

Supported External Functions

External functions are functions that are implemented outside the query environment. DataDirect XQuery supports two types of external functions: Java and SQL.

Java functions might be used to return system information, to invoke a Web service call, or simply to make available a function that is not in the XQuery function library.

SQL functions might be used to invoke a stored procedure or to make available a function that is not in the XQuery function library.

All external functions are namespace qualified, and the namespace that is used tells DataDirect XQuery whether the external function is written in Java or in SQL. Before calling an external function, it must be declared in the query.

Example: Java Function (Static Method)

Suppose an application needs to return information about the Java environment in which a query runs. The following Java code defines a class that contains the function to retrieve this information in Java:

public class myClass extends Object { 
... 
    public static String myExternalFunction() { 
        StringBuffer returnBuffer = new StringBuffer(); 
        Properties systemProperties = System.getProperties(); 
        returnBuffer.append("VM Version:" 
            + systemProperties.get("java.vm.version") + "\n"); 
        returnBuffer.append("VM Vendor:" 
            + systemProperties.get("java.vm.vendor") + "\n"); 
        returnBuffer.append("VM Name:"  
            + systemProperties.get("java.vm.name")); 
        return returnBuffer.toString(); 
    } 
} 

Now, the function must be declared, as shown in the following query:

declare namespace ex="ddtekjava:myClass"; 
declare function ex:myExternalFunction() as xs:string external; 
let $infoString := ex:myExternalFunction() 
return              
  let($infoString := ex:myExternalFunction() 
  return <vm_info>{$infoString}</vm_info>) 

In the preceding XQuery expression, the XQuery binds a namespace prefix (ex) to the fully qualified name of the class that defines the function.

Example: SQL Function

A SQL function can be called in a similar way to calling a Java function. The namespace for a SQL function is the predefined namespace, in this case http://www.datadirect.com/xquery
/sql-function, which is bound to the predefined prefix ddtek-sql. The following example calls the SQL function rtrim().

declare function ddtek-sql:reverse($namevalue as xs:string) as 
  xs:string external;

for $username in collection('users')//lastname
return
<last_name>{ddtek-sql:reverse($username)}</last_name>  

Using Java Functions

DataDirect XQuery supports Java static methods, Java instance methods, and Java constructors.

The DataDirect XQuery type ddtek:javaObject allows you to invoke Java methods. The predeclared DataDirect XQuery namespace prefix is ddtek, and is bound to http://www.datadirect.com/xquery. The examples in this section demonstrate the usage of ddtek:javaObject.

Declaring Java Functions

Two steps are required to declare Java functions:

Import the class into the XQuery environment.
Declare the function.

1. Importing the Class

Before you can declare a Java class, you must import it into the XQuery environment. To do this, you must declare a namespace with a specific URI. The syntax of an import is:

declare namespace prefix = "ddtekjava:java class name"; 

where:

prefix is a namespace prefix to associate with the Java class.

java class name contains the complete package and class name of the Java class to import. The specified Java class must be accessed using the Java class loader in the environment in which the query is executed. Typically, this means that the CLASSPATH must contain a reference to the directory or jar file where the Java class can be found. In J2EE server environments, other requirements might exist – the jar or class file might have to be stored in a specific directory, for example, as shown here:

declare namespace file = "ddtekjava:java.io.File"; 

If DataDirect XQuery cannot find the Java class, it generates a static error.

2. Declaring the Function

Before you can invoke a Java function in a query, you must declare it. How you declare it depends on whether the Java function is a static method or an instance method.

Static Method

To declare a static method, use the following syntax:

declare namespace file = "ddtekjava:java.io.File"; 
declare function namespace:function name(argument list) as return type 
  external; 

For example:

declare namespace file = "ddtekjava:java.io.File"; 
declare function file:createTempFile($prefix as xs:string,$suffix as 
  xs:string) as ddtek:javaObject external; 

See also Example: Java Function (Static Method).

Instance Method

To declare an instance method, follow these steps:

Import the class to a namespace (see 1. Importing the Class).
In the cases where you need to explicitly create a new Java object instance, declare a function mapping to a Java constructor unless the Java object type you are declaring has a defined XQuery mapping. A class instance can be an XML type for which a mapping is defined.
If step 2 is required, declare a function mapping to an instance method declaring ddtek:javaObject as the value for the first parameter. Otherwise, the value of the first parameter is the appropriate XQuery data type.
Invoke the function using the class instance on which the instance method is invoked as the first argument.

The numbers in the following example correspond to the preceding steps:

declare namespace BigInteger = "ddtekjava:java.math.BigInteger"; 1 
declare function BigInteger:BigInteger($v as xs:string) 2 
    as ddtek:javaObject external; 
declare function BigInteger:gcd(  
    $this as ddtek:javaObject,  
    $val as xs:integer) as xs:integer external; 3 
 
    BigInteger:gcd(BigInteger:BigInteger("12"),4) 4 

The following example does not include step 2 because the Java object to be declared has a defined XQuery mapping.

declare namespace BigInteger = "ddtekjava:java.math.BigInteger"; 1 
    declare function BigInteger:gcd( 
      $this as xs:integer,  
      $val as xs:integer) as xs:integer external; 3 
 
     BigInteger:gcd(12,4) 4 

Mapping Types Between Java and XQuery for Java External Functions

Before DataDirect XQuery passes XQuery arguments to a Java method, it converts the arguments from the XQuery data type to a Java type using the SequenceType specified for the external function when it was declared.

Table 12-5 shows how to map arguments and return types from the Java function declaration to XQuery SequenceTypes to be used in the XQuery function declaration.

Table 12-5. Mapping Types Between Java and XQuery  

Java Type	XQuery SequenceType
boolean	xs:boolean
byte	xs:byte
byte[]	ddtek:javaObject xs:base64binary xs:hexBinary
double	xs:double
float	xs:float
int	xs:int
long	xs:long
short	xs:short
void	empty-sequence() 1
java.lang.Boolean	ddtek:javaObject xs:boolean[?]
java.lang.Byte	ddtek:javaObject xs:byte[?]
java.lang.Double	ddtek:javaObject xs:double[?]
java.lang.Float	ddtek:javaObject xs:float[?]
java.lang.Integer	ddtek:javaObject xs:int[?]
java.lang.Long	ddtek:javaObject xs:long[?]
java.lang.Object 2	ddtek:javaObject
java.lang.Short	ddtek:javaObject xs:short[?]
java.lang.String	ddtek:javaObject xs:untypedAtomic[?] xs:string[?]
java.math.BigDecimal	ddtek:javaObject xs:decimal[?]
java.math.BigInteger	ddtek:javaObject xs:integer[?]
java.net.URI	ddtek:javaObject xs:anyURI[?]
java.xml.namespace.QName	ddtek:javaObject xs:QName[?]
javax.xml.transform.Source3	ddtek:javaObject document-node( )
org.w3c.doc.ProcessingInstruction	ddtek:javaObject processing-instruction() [?]
org.w3c.dom.Attr	ddtek:javaObject attribute() [?]
org.w3c.dom.Comment	ddtek:javaObject comment() [?]
org.w3c.dom.Document	ddtek:javaObject document-node() [?]
org.w3c.dom.Element	ddtek:javaObject element() [?]
org.w3c.dom.Node	ddtek:javaObject document-node() [?] element() [?] attribute() [?] comment() [?] text() [?] processing-instruction() [?]
org.w3c.dom.Text	ddtek:javaObject text() [?]
boolean, byte, double, float, int, long, short 4	xs:anyAtomicType? d
byte[], java.lang.Byte, java.lang.Double, java.lang.Float, java.lang.Integer, java.lang.Long, java.lang.Short, java.lang.String, java.math.BigDecimal, java.math.BigInteger, java.net.URI, java.xml.namespace.QName	xs:anyAtomicType? d
Any Java data type in this table	item() d
Any Java object type in this table	item() ? d
Any Java type in this table	item()* a item() + a
java type [] 5	ddtek:javaObject XQuery type of this table(*|+)

1 Only for return types. 2 java.lang.Object or another Java class not listed in this table. 3 Must be one of the following interfaces: javax.xml.transform.stream.StreamSource, javax.xml.transform.sax.SAXSource, javax.xml.transform.dom.DOMSource, javax.xml.transform.stax.StAXSource, or com.ddtek.xquery.StAXSource. 3 Note that only javax.xml.transform.dom.DOMSource is supported for the function’s parameters. 4 Useful to declare Java methods overloaded on argument type. See Resolving Function Calls. 5 When the Java parameter type or method return type is an array, either declare the XQuery function to receive or return one of the matching types from this table and add a * or + quantifier.You can also use ddtek:javaObject with a * or + quantifier.

In cases where Table 12-5 lists multiple mapping options, consider the following information:

ddtek:javaObject cannot be combined with other XQuery types. This means that when the expression you want to pass to a Java function is the result of calling another Java method that returns a ddtek:javaObject, you must declare the function as receiving a ddtek:javaObject expression. Also, when you declare the return type of a Java function as ddtek:javaObject, the returned value can be passed only into another Java method. The returned value cannot be used with any other XQuery expression.
To map a Java method to an XQuery type when the method is overloaded on parameter type and no common XQuery type can be found for that parameter, you must use a less specific SequenceType. Typically, in this case, you would use a less specific SequenceType such as item(), xs:anyAtomicType, or ddtek:javaObject depending on the details of the set of overloaded methods. DataDirect XQuery attempts to map a given XQuery function invocation to the correct Java function using the static types of the function call arguments instead of the SequenceType of the arguments. See the next section Resolving Function Calls for more information on this topic.

Resolving Function Calls

Before DataDirect XQuery attempts to resolve the Java method used to invoke a given XQuery function call, DataDirect XQuery identifies the external function declaration.

Then, like any other XQuery function call, the static types of the argument expressions are matched with the SequenceType of the function declaration parameters. Static type errors are detected and reported.

Finally, the Java method must be resolved. Sometimes, DataDirect XQuery cannot determine how to map an XQuery function declaration and the associated function call to a Java instance method. Ambiguity can occur for the following reasons:

Java supports method overloading on argument type; however, XQuery does not support function overloading on argument type.
Invoking Java instance methods from DataDirect XQuery requires a first artificial "this" argument. This requirement creates a potential conflict with static methods of the same class that accept a true Java object as their first argument.

DataDirect XQuery uses the following steps to determine the Java method to invoke for a given function call. Except for the first step, each of the consecutive steps reduces the number of Java functions that are potential candidates to which to map the XQuery function call.

Add all public (static and instance) methods and public constructors of the Java class identified through the function’s namespace.
Use a public constructor if the name of the function equals the class name. In the absence of matching public constructors, DataDirect XQuery considers Java methods with the same name.
Retain only Java methods whose names match the name of the function if the name of the function being invoked does not equal the class name.
Remove all instance methods, unless the first argument in the function call is typed as ddtek:javaObject and the Java class associated with the ddtek:javaObject equals the class identified by the namespace of the function (see Notes About Using Java Instance Methods).
Remove all static methods and constructors whose number of parameters does not match the number of parameters in the XQuery function declaration.
Remove all instance methods whose number of parameters does not equal the number of parameters of the XQuery function declaration minus one. (This takes into account the first artificial "this" argument that is required when invoking an instance method from XQuery.)
Remove all Java methods and constructors for which the SequenceType of the argument as specified in the function declaration does not match the type of the Java method. The type matching requires a mapping from an XQuery SequenceType to a Java type. This mapping is documented in Table 12-5. Note that when the function argument is declared as item(), xs:anyAtomicType, or ddtek:javaObject, DataDirect uses the static type of the argument expression of the function call instead of the SequenceType of the function declaration. This typically is more accurate and allows correct method resolution in more scenarios.

Unless exactly one method remains after the previously described procedure, one of the following static errors is generated:

Static error during resolving of external Java function. 
Ambiguous call to Java external function '<function>' 

or

Static error during resolving of external Java function. No 
matching Java external function found for '<function>' 

Note that at execution time:

Standard Java late binding applies when invoking instance methods.
If the XQuery external function resolves to an instance method and at runtime the "this" argument evaluates to an empty sequence, the following error is generated:

Runtime error. Value of this pointer is null in call to external Java instance method.

NOTE: Generic methods introduced with J2SE 5.0 are not supported.

Notes About Using Java Instance Methods

ddtek:javaObject can only be used when declaring a SequenceType and cannot be used in any other XQuery expression where a QName bound to a type is allowed such as cast, treat as, and so on.
External variables of type ddtek:javaObject are not supported.
When possible, DataDirect XQuery keeps track of the exact Java class that is associated with a given ddtek:javaObject-typed expression. This can be useful when trying to map a given function call to a Java method.

When tracking the underlying Java class is not possible, you can use the DataDirect XQuery proprietary function, ddtek:javaCast, to resolve this issue (see ddtek:javaCast). DataDirect XQuery cannot track the underlying Java class in the following situations:

The ddtek:javaObject expression is the result of executing a recursive function, for example:

declare namespace c1 = "ddtekjava:c1";

declare namespace c2 = "ddtekjava:c2";

declare namespace c3 = "ddtekjava:c3";

declare variable $e as xs:integer external;

declare function c1:c1() external;

declare function c2:c2() external;

declare function c3:f($this as ddtek:javaObject) external;

declare function local:recursive($p as xs:integer) as ddtek:javaObject {

if($p le 1) then c1:c1()

else if($p eq 2) then c2:c2()

else local:recursive($p - 2)

};

c3:f(local:recursive($e))

Assuming that there are two static c3:f methods, the first taking an instance of c1 and the second taking an instance of c2, DataDirect XQuery is unable to determine statically which one to invoke and generates a static error.

The static type of an expression is a sequence (or union) of different ddtek:javaObject types, for example:

declare namespace c1 = "ddtekjava:c1";

declare namespace c2 = "ddtekjava:c2";

declare namespace c3 = "ddtekjava:c3";

declare function c1:c1() external;

declare function c2:c2() external;

declare function c3:f($this as ddtek:javaObject) external;

for $x in (c1:c1(),c2:c2()) return c3:f($x)

Assuming that there are two static c3:f methods, the first taking an instance of c1 and the second taking an instance of c2, DataDirect XQuery is unable to determine statically which one to invoke and generates a static error.

Keeping in mind the preceding information about tracking the extract Java class, it is possible to return a ddtek:javaObject value from a query. A Java program containing the query can retrieve the result by:
Serializing the result by using one of the following XQSequence methods: getSequenceAsString, writeSequence, writeSequenceToSAX, or writeSequenceToStream (see also Serialization Support).
Using the XQSequence getObject method, which returns the Java object.
Using the XQSequence getLexicalValue method.

NOTE: Using any other XQJ method to retrieve a ddtek:javaObject value generates an error.

See also Resolving Function Calls.

Disabling Java Functions

You can disable the ability to invoke Java functions, for example, for security reasons, by specifying the AllowJavaFunctions property of DDXQDataSource. See DDXQDataSource and DDXQJDBCConnection Properties.

Using SQL Functions

DataDirect XQuery allows you to invoke any SQL function provided by any supported database, including built-in database functions and stored procedures. See Example: SQL Function for an example of invoking a SQL function.

Requirements and Restrictions

The requirements and restrictions for using SQL functions within a query are:

You must declare the SQL function as an external function in the http://www.datadirect.com/xquery/sql-function namespace, which has a predefined prefix of ddtek-sql.

The following example first declares rtrim(), and then invokes it within a query:

declare function ddtek-sql:rtrim($inp as xs:string) as xs:string external; 
for $x in collection('items')//itemno 
return 
   <a>{ddtek-sql:rtrim(concat($x,'  '))}</a> 

You must declare a JDBC Escape function as an external function in the http://www.datadirect.com/xquery/sql-jdbc-escape-function namespace, which has a predefined prefix of ddtek-sql-jdbc.
Functions or procedures returning results through output parameters are not supported.
Functions that return a table are supported if the table function is defined in the source configuration file. This is specified in the tableFunction element of the source configuration file. See Using SQL Table Functions for more information.
The SequenceTypes for parameter and return values in the external function declarations must match the mapping of database data types to XML schema types as described in the tables listed in Data Type Mappings.
The SQL function is not supported if the SQL function through an argument or return type refers to a data type for which no mapping is defined. See Data Type Mappings for the mappings of database data types to XML schema types.
The quantifier of the return type must be ? or 1.

Microsoft SQL Server Examples

This section presents examples of Microsoft SQL Server functions and describes their equivalents in XQuery.

Example: ddtek-sql:STUFF

The STUFF function inserts the contents of one string in another. The syntax of this function is:

STUFF(character_expr,start,length,character_expr) 

The equivalent XQuery declaration is:

declare function ddtek-sql:STUFF( 
  $p1 as xs:string?, 
  $p2 as xs:integer, 
  $p3 as xs:integer, 
  $p4 as xs:string) as xs:string? external; 

TIP: Although ddtek-sql:STUFF accepts null for its arguments, it is a better idea to provide more precise SequenceType quantifier information, as shown in the preceding XQuery declaration, if the context in which the query is being executed allows such optimization.

An example query using ddtek-sql:STUFF is:

collection('users')//userid/ddtek-sql:STUFF(.,1,0,
  "userid=") 

The results are:

userid=Jonathan 
userid=Minollo 

Example: ddtek-sql:STDEV

The STDEV function returns the standard deviation of a set of values. The syntax of this function is:

STDEV(expression) 

where expression is a numeric expression.

The equivalent XQuery declaration is:

declare function ddtek-sql:STDEV($p as xs:decimal*)  
  as xs:double? external; 

Because STDEV is an aggregate function, the quantifier of the SequenceType for $p must be *.

IMPORTANT: You must use * as a quantifier for the argument of the ddtek-sql:STDEV declaration. If you do not, a SQL error is raised.

An example of invoking ddtek-sql:STDEV is:

declare function ddtek-sql:STDEV($p as xs:decimal*)  
  as xs:double? external; 
ddtek-sql:STDEV(collection('historical')//volume) 

Assume that another invocation of ddtek-sql:STDEV within the same query needs to operate on xs:double arguments. In this situation, you cannot declare the argument of ddtek-sql:STDEV as xs:decimal*. Instead, declare the argument as xs:anyAtomicType*, as shown:

declare function ddtek-sql:STDEV($p as xs:anyAtomicType*)  
  as xs:double? external; 
ddtek-sql:STDEV( 
  collection(' historical ')//volume/xs:decimal(.)), 
ddtek-sql:STDEV( 
  collection('historical')//actualclose/xs:double(.)) 

DB2 Examples

This section presents examples of DB2 functions and describes their equivalents in XQuery.

Example: ddtek-sql:encrypt

The encrypt function returns an encrypted value. The syntax of this function is:

encrypt(StringDataToEncrypt, PasswordOrPhrase,
 PasswordHint) 

The equivalent XQuery declaration is:

declare function ddtek-sql:encrypt($data as xs:string,  
                                   $password as xs:string,  
                                   $hint as xs:string)  
  as xs:string external; 

An example query using ddtek-sql:encrypt is:

declare function ddtek-sql:encrypt($data as xs:string,  
                                   $password as xs:string,  
                                   $hint as xs:string)  
  as xs:string external; 
for $x in collection('users')/users 
return 
<user id='{$x/userid}' encrypted='{ddtek-sql:encrypt 
  concat($x/firstname,$x/lastname,$x/othername), 
  'secret','hint')}' /> 

This example returns:

<user  
  id="Jonathan" 
  encrypted= 
 "089B6504E404BFD568696E743A5B64F5A7838CEEEE66DE7F9C5CD92D5E70954C00A81E71"/> 
<user  
  id="Minollo"  
  encrypted="08C04004E404A5D568696E742C93D28C8A2946BF74DB19F6CA6B27BD"/> 

Example: ddtek-sql:variance

The variance function returns the variance of a set of numbers. The syntax of this function is:

variance(numeric-expression) 

The equivalent XQuery declaration is:

declare function ddtek-sql:variance($inp as xs:decimal*) as 
  xs:double external; 

An example query using ddtek-sql:variance is:

declare function ddtek-sql:variance($inp as xs:decimal*) as 
  xs:double external; 
for $x in 
distinct-values(collection('historical')/historical/ticker) 
return 
<ticker-variance  
    ticker='{$x}'  
    variance='{ddtek-sql:variance( 
    collection('historical')/historical[ticker eq 
                $x]/adjustedclose)}' /> 

Because variance is an aggregate function, the quantifier of the SequenceType for $inp must be *.

The example returns:

<ticker-variance ticker="AAPL" variance="136.3814396049211"/> 
<ticker-variance ticker="ADBE" variance="302.2495900777491"/> 
<ticker-variance ticker="AMZN" variance="559.4292663498876"/> 
... 

Oracle Examples

This section presents examples of Oracle functions and describes their equivalents in XQuery.

Example: ddtek-sql:DECODE

The DECODE function behaves as a SQL IF-THEN-ELSE statement. The syntax of this function is:

DECODE(expr, search, result 
             [, search, result ]... 
       [, default ] 
      ) 

This function is overloaded; therefore, it must be declared multiple times, for example:

declare function  ddtek-sql:DECODE( 
  $p1 as xs:string,  
  $p2 as xs:string,  
  $p3 as xs:string,  
  $p4 as xs:string) as xs:string external; 
declare function ddtek-sql:DECODE( 
  $p1 as xs:string,  
  $p2 as xs:string,  
  $p3 as xs:string,  
  $p4 as xs:string,  
  $p5 as xs:string,  
  $p6 as xs:string) as xs:string external; 
for $h in collection('holdings')/holdings 
let $ticker := $h/stockticker 
let $description := ddtek-sql:DECODE( 
  $ticker, 
  'PRGS','Progress Software Cooperation',  
  fn:concat('Sorry but ',$ticker,' is not a recognized
    ticker')) 
let $holder := ddtek-sql:DECODE( 
  $h/userid, 
  'Jonathan', 'Mr John',  
  'Minollo', 'Senior Minollo',  
  '????') 
return 
  <who-owns-what  
     description='{$description}'  
     holder='{$holder}' /> 

This example returns:

<who-owns-what description="Progress Software Cooperation" holder="Mr John"/> 
<who-owns-what description="Progress Software Cooperation"  
  holder="Senior Minollo"/> 
<who-owns-what description="Sorry but AMZN is not a recognized ticker" 
  holder="Mr John"/> 
<who-owns-what description="Sorry but AMZN is not a recognized ticker" 
  holder="Senior Minollo"/> 
... 

Using User-Defined Functions

User-defined functions are available for all supported databases. This section provides examples for DB2, Microsoft SQL Server, and Oracle.

For the following examples, assume a user-defined function named FUNC_TAN.

Example: DB2

CREATE FUNCTION FUNC_TAN (X DOUBLE) RETURNS DOUBLE 
LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC 
RETURN SIN(X)/COS(X) 

Example: Microsoft SQL Server

CREATE FUNCTION FUNC_TAN (@X float) RETURNS float 
BEGIN RETURN (SIN(@X)/COS(@X)) END 

Example: Oracle

CREATE OR REPLACE FUNCTION FUNC_TAN (X IN FLOAT) RETURN
  FLOAT  
IS TMP FLOAT;  
BEGIN   
TMP := SIN(X)/COS(X);  
RETURN (TMP);  
END; 

The following XQuery expression declares and invokes the function:

declare function ddtek-sql:FUNC_TAN($x as xs:double?)  
  as xs:double? external;  
for $v in collection('some_table')//some_numeric_column 
return ddtek-sql:FUNC_TAN($v) 

Using JDBC Scalar Functions

In addition to allowing you to invoke any SQL function provided by any supported database, including built-in database functions, DataDirect XQuery allows you to use JDBC scalar functions, which are not database specific. Refer to the JDBC specification for a complete list of JDBC scalar functions.

You must declare a JDBC scalar function in the http://www.datadirect.com/xquery/sql-jdbc-escape-function namespace, which has a predefined prefix of ddtek-sql-jdbc.

For example:

declare function ddtek-sql-jdbc:user() as xs:string external; 
<holding-report generated-by='{ddtek-sql-jdbc:user()}'>{ 
  for $h in collection('holdings')/holdings 
  return 
    <holding-info userid="{$h/userid}" ticker="{$h/stockticker}" /> 
}</holding-report> 

Using SQL Table Functions

DataDirect XQuery supports table functions, including user-defined table functions, for DB2, Informix, Oracle, PostgreSQL, and Microsoft SQL Server.

To use SQL table functions, you must declare the function and the structure of the returned table using the tableFunction element in the source configuration file.

DB2 has a system table function, SYSPROC.DB_PARTITIONS, that returns system information of the DB2 instance. (Refer to your IBM DB2 documentation for details about this table function.) To invoke this function, configure the source configuration file as shown:

<schema name="SYSPROC"> 
... 
   <tableFunction name="DB_PARTITIONS"> 
     <resultSet> 
       <column name="PARTITION_NUMBER" schemaType="short"/> 
       <column name="HOST_NAME" schemaType="string"/> 
       <column name="PORT_NUMBER" schemaType="short"/> 
       <column name="SWITCH_NAME" schemaType="string"/> 
     </resultSet> 
   </tableFunction> 
... 
</schema> 

Once you have configured the source configuration file, declare the function in the ddtek-sql namespace (http://www.datadirect.com/xquery/sql-function) and use a return type of document-node(element()). For example:

declare function ddtek-sql:XVS.SYSPROC.DB_PARTITIONS() as 
  document-node(element()) external; 
ddtek-sql:XVS.SYSPROC.DB_PARTITIONS() 

One possible result for this example is:

<DB_PARTITIONS> 
  <PARTITION_NUMBER>0</PARTITION_NUMBER> 
  <HOST_NAME>the_host</HOST_NAME> 
  <PORT_NUMBER>0</PORT_NUMBER> 
  <SWITCH_NAME>the_switch_name</SWITCH_NAME> 
</DB_PARTITIONS> 

A second example:

declare function ddtek-sql:XVS.SYSPROC.DB_PARTITIONS() as 
  document-node(element()) external; 
for $x in ddtek-sql:XVS.SYSPROC.DB_PARTITIONS()/DB_PARTITIONS 
return 
  <partition number='{$x/PARTITION_NUMBER}' host='{$x/HOST_NAME}' /> 

One possible result for this example is:

<partition number="0" host="the_host"/>