No standard way exists to access a relational table in XQuery, so each XQuery implementation defines its own way to do this. DataDirect XQuery uses fn:collection() to access a relational table. For example, the following query accesses the HOLDINGS database table:
Generally, DataDirect XQuery processes fn:collection() arguments in XQuery queries without problems if the fn:collection() argument is correct and the connection information specified in the connection associated with the XQuery query is correct. When DataDirect XQuery cannot resolve the fn:collection() argument to a specific database object, it raises an error.
This paper provides guidelines to help you troubleshoot and correct common causes of errors that occur when using fn:collection() in an XQuery query.
When DataDirect XQuery cannot resolve the fn:collection() argument to a specific database object, it raises an error, such as Collection not found or Multiple collections found. If you encounter these types of errors when using DataDirect XQuery, the following guidelines will help you troubleshoot and correct the cause of the error:
If you have multiple database tables with the same name or the default catalog and schema associated with the connection do not provide access to the database table, you can qualify the database table name in the fn:collection() argument to target the specific table. For example, if the default catalog (database) and schema (user) associated with the connection is FINANCIAL and JOSEPH, respectively, and the target table is owned by the schema MARY, qualify the table name in the fn:collection() argument:
To verify that you know the correct catalog name, schema name, and table name, start the SQL tool shipped with your database and connect to the database server. Once connected, execute the following SQL statement against the database:
For DB2, Microsoft SQL Server, and Sybase:
where catalog, schema, and table are the catalog name, schema name, and table name of the database object you are trying to access.
NOTE: Oracle databases do not have catalogs.
If the SQL statement returns an empty result, the values you entered correspond to the correct catalog name, schema name, and table name. Use these values in the fn:collection() argument to qualify the table name. If the SQL statement returns an error, the values you entered are incorrect.
IMPORTANT: The case of the values specified in the fn:collection() argument must match the case of the database.
On Microsoft SQL Server and Sybase, a user can have the special status of database owner. For example, if the SQL name of the target table is "FINANCIAL"."dbo"."HOLDINGS," qualify the table name in the fn:collection() argument with the schema name dbo:
If the table name in the fn:collection() argument is qualified with a catalog name or schema name (or both) and DataDirect XQuery returns an error indicating that multiple tables with the same name exist, you need to qualify the table name using a JDBC connection name. A JDBC connection name identifies a specific connection associated with the database table.
Suppose two tables of the same name, HOLDINGS, exist on different database servers with the same schema name, JOSEPH. In this case, the following query does not provide enough information for DataDirect XQuery to locate the target table:
To identify the correct table, you can qualify the table name in the fn:collection() argument with a JDBC connection name. Here's an example that shows a connection made explicitly in the application to two different databases; each connection is assigned a unique JDBC connection name, stocks1 and stocks2, respectively:
Here's an example showing the same connection information configured in a DataDirect XQuery source configuration file:
To target the HOLDINGS table on server1, qualify the table name with the JDBC connection name stocks1 in addition to the catalog name and schema name:
Refer to the DataDirect XQuery User's Guide and Reference for more information about connecting with DataDirect XQuery, including specifying JDBC connection names.
If the catalog name, schema name, or table name in the fn:collection() argument contains a period (.) or a colon (:), escape the character with a backslash (\) so that DataDirect XQuery can parse the argument into its different parts. For example, if the target table is named A.HOLDINGS and you specify the following query, DataDirect XQuery parses 'A' as the schema name, not as part of the table name:
Escaping the period (.) in the fn:collection() argument using the backslash character allows DataDirect XQuery to parse the argument correctly:
In addition, XQuery string literal syntax applies to the fn:collection() argument. If a table name contains double quotation marks, for example, A"HOLDINGS, and the fn:collection() argument uses double quotation mark delimiters, you must repeat the double quotation marks:
Using DataDirect XQuery, an application establishes a connection to the database to execute an XQuery query. The application can establish a connection to the database in multiple ways: explicitly specifying connection information in the application, using a data source registered with JNDI, or using a DataDirect XQuery source configuration file. If DataDirect XQuery cannot access the database because connection information is specified incorrectly or because the structure of the configuration file is incorrect, it raises an error.
Verify that the following information in your connection URL is correct:
Minor differences in the structure of the configuration file are required depending on the database accessed. If you are using a configuration file, make sure that it validates against the source_config.xsd schema shipped with DataDirect XQuery. This schema is located in the examples/config subdirectory of your DataDirect XQuery installation directory. You can validate the configuration file using a tool such as <oXygen>® XML Editor for Eclipse (DataDirect XQuery Edition) or Stylus Studio®.
In addition, make sure that the values of the following elements, if specified in your configuration file, are correct:
If you encounter an error when using fn:collection() with DataDirect XQuery, examine the following checklist to resolve the problem: