Using Data Sources in Queries

This section describes the XML and relational sources and how they are represented the XML data model. In XQuery, data sources and query results are both represented using an XML data model. Physical inputs such as XML text files, DOM trees, or relational databases are mapped into the XML data model when they are queried. In the case of relational databases, the mapping is logical, and relational data is not materialized as XML. The mappings are documented in this section.

The result of a query (the output) is also defined in the XML data model and must be mapped to a physical format such as DOM, SAX, StAX, or text for an application to use the results. See "Accessing XML Results" for more information about the mapping of results.

In addition, when you use DataDirect XQuery with the DataDirect XML Converters, you can convert many non-XML formats into XML, including EDI messages, tab-delimited and comma-separated text files, dBASE files, RTF files, and many more. Once these non-XML data sources are converted to XML, they are accessed the same as XML documents.

XML Data Sources

The XML data sources that DataDirect XQuery can access have the following physical formats:

Data Model Representation of XML Documents

For XQuery queries that access XML using fn:doc() or external variables in the query, DataDirect XQuery implements the Infoset mapping described in the XQuery 1.0 and XPath 2.0 Data Model specification located at: http://www.w3.org/TR/2005/CR-xpath-datamodel-20051103/

Relational Data Sources

DataDirect XQuery provides support for the following relational databases:

Specifying Relational Database Tables

Using DataDirect XQuery, you specify relational database tables in a query using fn:collection(). The following example specifies a database table named holdings: 

collection('holdings')

NOTES FOR SPECIFYING DATABASE TABLE NAMES:

See "Path Expressions for Relational Sources" for examples of using fn:collection(). See "Resolving fn:collection() Errors" for information about troubleshooting fn:collection() errors.

Data Model Representation of Relational Tables

In DataDirect XQuery, XML views of relational data are based on the SQL/XML mappings specified in the SQL 2003 standard. These are logical XML views, not a physical format.

SQL/XML provides different ways to parameterize views. DataDirect XQuery uses the XMLFOREST variable to parameterize views. For most applications, this default setting is sufficient; however, you can change the value of this variable by setting the JdbcSqlXmlForest property of DDXQDataSource (see "DDXQDataSource and DDXQJDBCConnection Properties").

The default setting of true for the XMLFOREST variable means that DataDirect XQuery represents each database table by a sequence of row elements in a document node. The row elements use the table name as the element name, are contained within a document node, and contain an element for each non-null column in the row that uses the column name as the element name. For example, the result for collection('users') is a document node containing the following elements: 

<users> 
  <userid>Minollo</userid> 
  <firstname>Carlo</firstname> 
  <lastname>Innocenti</lastname> 
  <membersince>2004-06-16T00:00:00</membersince> 
</users> 
<users> 
  <userid>Jonathan</userid> 
  <firstname>Jonathan</firstname> 
  <lastname>Robie</lastname> 
  <othername>William</othername> 
  <membersince>2004-03-03T00:00:00</membersince> 
</users>

Suppose we wanted to write a FLWOR expression to bind each row of the preceding table to a variable. In this case, we add an argument to fn:collection() that defines a path matching the users elements: 

collection('users')/users

Here is a FLWOR expression that iterates over the rows returned by the preceding path expression: 

for $u in collection('users')/users 
where $u/lastname = 'Robie' 
return $u/membersince

When the XMLFOREST variable is false, the result is a document node. Inside the document node is a single element that represents the table. Inside the single element node is a sequence of elements named row, each representing one row of the table. For example, a result for collection('users') might look like this: 

<users> 
  <row> 
    <userid>Minollo</userid> 
    <firstname>Carlo</firstname> 
    <lastname>Innocenti</lastname> 
    <membersince>2004-06-16T00:00:00</membersince> 
  </row> 
  <row> 
    <userid>Jonathan</userid> 
    <firstname>Jonathan</firstname> 
    <lastname>Robie</lastname> 
    <othername>William</othername> 
    <membersince>2004-03-03T00:00:00</membersince> 
  </row> 
</users>

Suppose we wanted to write a FLWOR expression to bind each row of the preceding table to a variable. In this case, we would need to add a path expression to fn:collection(). The following path expression defines a path that matches the row elements: 

collection('users')/users/row

Here is a FLWOR expression that iterates over the rows returned by the preceding path expression: 

for $u in collection('users')/users/row 
where $u/lastname = 'Robie' 
return $u/membersince

Case Sensitivity

XML element and attribute names are case-sensitive. When SQL column and table names are mapped into XML elements, the case depends on a number of factors that can vary by database vendor and the parameters used to create a database.

The case sensitivity of the argument to fn:collection() depends on the database.

Data Type Mappings

See "Data Types" for information about how database data types are mapped to XML schema data types.