Using Data Sources in Queries

In XQuery, data sources and query results are both represented using an XML data model. Physical inputs such as XML text files, DOM trees, and 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. These mappings are documented in this section.

The result of a query (the XML output) is also defined in the XML data model and must be mapped to a physical format such as DOM, SAX, StAX, or text in order 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 DataDirect XML Converters, you can convert 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:

Note: For support for specific versions, see Supported Databases. This information is also maintained on the DataDirect web site:

http://www.datadirect.com/support/product_info/
databasesupport/index.ssp

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. Using XMLFOREST is sufficient for most applications, but you can choose not to use it by setting the JdbcSqlXmlForest property of DDXQDataSource to false (see DDXQDataSource and DDXQJDBCConnection Properties).

When the value of the JdbcSqlXmlForest property is set to true (the default), 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 and contain an element for each non-null column in the row; DataDirect XQuery uses the column name as the element name for these elements. For example, when XML FOREST is used, 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, when XMLFOREST is not used, 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 Type Mappings for information about how database data types are mapped to XML schema data types.

Security Features

DataDirect XQuery supports authentication and data encryption security features for data source connections. For more information, see Chapter 7, Securing Data Source Connections.