QuerySurge Support for XML Namespaces
“XML namespaces provide a simple method for qualifying element and attribute names used in Extensible Markup Language documents by associating them with namespaces identified by URI references” (http://www.w3.org/TR/REC-xml-names/). In other words, namespaces are used to provide a unique property to XML elements, so that name collisions are avoided. If you need an introduction to how XML namespaces work, there are numerous web resources and publications that deal with namespace definitions and rules. QuerySurge can handle XMLs with namespaces; in this discussion, we show how to handle XMLs with default and non-default namespaces.
Note: For the basics of setting up an XML Connection in QuerySurge, see this article.
The Default Namespace
The default XML namespace has the prefix xmlns. Consider a simple XML defining the default namespace (see Sample file curr-xchg-rate-data_ns_1.xml). The default namespace is defined as an attribute of the root element <rates>. The default namespace applies to the declaring element and its descendants.
|
<?xml version="1.0" encoding="UTF-8"?> |
Sample 1. A Single Default Namespace
For QuerySurge to properly read this XML, information about its namespaces must be entered into its Schema. Two types of entries must be made in the Schema: a) the namespace(s) must be defined in the <table> element of the Schema, and b) any path attributes containing elements in the namespace must explicitly contain the namespace.
Namespace definitions are added to the <table> element by adding a namespaces attribute, which defines one or more namespaces for the XML. Then, path attributes in the Schema must be written with the namespace so that all XPath searches for elements include the namepace. Following is a Schema for the Sample 1 above:
|
<schema> |
Sample 2. Schema File showing XML node-to-column and attribute-to-column mappings (see Sample 1)
There are a few important things to note about the Schema file. First, in the <table> tag, the namespaces attribute designates our new default namespace. Notice that the namespace is defined by prepending the namespace prefix (xmlns), followed by a colon, to the namespace definition:
xmlns:http://www.foo.com
The full Schema namespaces attribute is therefore:
namespaces="xmlns:http://www.foo.com"
Note that this notation differs from the notation of the namespace in our data XML, where an equals sign (=) appears and the quotes are used differently (see Sample 1).
Also, note that the xmlns prefix notation appears in all of the path attributes, in both the <table> and the <column> elements. Omitting these prefixes will probably result in a query that runs, but returns a resultset with no rows. (The Schema shown in Sample 2 is in file: curr-xchg-rate-schema_ns_1.xml.)
Finally, each of the <column> tags defines a column in the table view of the XML, as the name implies. In this example, we will map all of the XML nodes to columns, and in addition, we will map three attributes to columns. The name attribute of each <column> tag assigns the column a name that you can use in your SQL. The path attribute is an XPath instruction that locates the XML object being mapped to the specified column. The type attribute gives the data type (Varchar and Decimal types are shown above; standard type designations may be used, including other numeric types, date types, etc.). The size attribute gives the Varchar size of the column.
Note: If you omit a size attribute for a Varchar column, the driver will use its default size, which will make all your Varchar columns into CLOB types in QuerySurge (2 GB wide).
XML with Two Namespaces
The next example that we will consider is an XML with two namespaces, a default namespace and a custom namespace. The default namespace is the same as in Sample 1; we have added a non-default namespace designated by the prefix bar. Our XML looks like the following (Sample 3):
|
<?xml version="1.0" encoding="UTF-8"?> |
Sample 3. A Default Namespace and a Non-default Namespace
The Schema file for this XML is where we need to introduce some additional syntax, so that QuerySurge can read it. The rules for handling this situation are very much like those for the default namespace with some minor, but important, additions. Sample 4 shows the Schema for the Sample 3 XML:
|
<schema> |
Sample 4. Schema with Two Namespaces
The major addition here is that we have defined two namespaces in the namespaces attribute. The Schema namespace definition now looks like:
namespaces="xmlns:http://www.foo.com|bar:http://www.bar.com"
Note that the two namespaces are both represented in the attribute, separated by a pipe symbol (|). You can add as many namespaces to the Schema namespaces attribute as you like using this pipe notation.
Because the XML (see Sample 3) only uses the default namespace (xmlns) in this example, we do not have to modify the xmlns prefix in the path attributes of the Schema file. (Sample 4 appears in file curr-xchg-rate-schema_ns_2.xml.)
XML Explicitly Using a Non-default Namespace
Our final example is an XML with two namespaces defined, a default and a non-default namespace, similar to the previous example. In this example, however, the default namespace is used implicitly and the non-default namespace is used explicitly. Sample 5 uses the same two namespaces as are found in Sample 3, but we have modified the XML to use the bar namespace for one of its elements, the <CD> element. Note that the bar prefix appears in both the opening and closing tags of the element, and on every <CD> element in the XML. In this example, all elements are in the default namespace as usual, except the <bar:CD> elements, which are in the namespace denoted by bar. (Sample 5 appears in file curr-xchg-rate-schema_ns_3.xml, and Sample 6 appears in file curr-xchg-rate-schema_ns_3.xml.)
|
<?xml version="1.0" encoding="UTF-8"?> |
Sample 5. A Nondefault Namespace
Again the “magic” for reading this XML is in the Schema file, which appears in Sample 6. The modifications in Sample 6 as compared to Sample 4 simply are the use of the bar prefix in the path for the <column> tag for the <bar:CD> element of the XML:
|
<schema> |
Sample 6. A Nondefault Namespace Used in the Schema file
By specifying the bar prefix on the path attribute of the <column>, QuerySurge can find all the <bar:CD> elements that are in this namespace. It is important to note that if the namespace qualification is omitted from the path attribute of the <column>, the column will not be omitted (because it is defined in the Schema), but all values in the column will be returned as null. Also note that if the definition of the bar namespace definition is omitted from the namespaces attribute of <table>, an exception will result.
Note that what we mainly have done in these examples is to use XPath statements to specify the components of a "column" for our query against the XML. That really is all that is involved - once you specify the columns that you are interested in via XPath, QuerySurge will "read" your XML according to your specification, and you're ready to write SQL against the XML.
Sample XML Files
Sample XML and schema files for this article can be downloaded here.