Specifying Table Names in SQL Statements

When defining locations, you specify a name for the location along with a directory, or path and file name. For example, suppose you define two locations for a data source, a Folder location and an XML Document location. The Folder location is on a local filing system and the XML Document location is on a web server with a URL prefix of http://www.acme.com/xmldata.

For example:

The Folder location:
c:\xmldata\xmlsample as LOC1

The XML Document location: http://www.acme.com/xmldata/doc.xml as LOC2

For complete information about how to configure locations in an XML data source, see “Configuring and Connecting to Data Sources”.

If you are connected to this data source and the data source had the "Show Manufactured Schemas" option set as the Schema Mode (see the Schema Mode option under “Configuring and Connecting to Data Sources”) and then you performed an unqualified SQLTables operation, you would get the following results.

Schema name	Table name
LOC1#	FILE1
LOC1#	FILE2
LOC2#	TABLE1
LOC2#	TABLE2

Location names are fabricated into the schema name by adding a # symbol to the end of the location name.

NOTE: If you had the "Show Virtual Schemas" option set, the above table would have "XML" listed in the Schema name column.

To fully qualify a table name in a SQL statement, you could use the following:

LOC1#.FILE1
or
XML.FILE1

LOC2#.TABLE2
or
XML.TABLE2

This design gives you a simpler table name qualifier. This is an important advantage given the complexity of URL names, and the requirement to double quote them in SQL statements. For example, the following query uses a fully qualified table name for an XML Document location:

SELECT * FROM "http://www.acme.com/xmldata/doc.xml#TABLE2" WHERE productName=’lawnmower’

Compare that to the same query using a location name:

SELECT * FROM LOC2#.TABLE2 WHERE productName=’lawnmower’

Another example demonstrating the Folder location is as follows:

SELECT * FROM "c:\xmldata\xmlsample\FILE1.XML" WHERE productName=’lawnmower’

Compare that to the same query using a location name:

SELECT * FROM LOC1#.FILE1 WHERE productName=’lawnmower’