QuerySurge Support for XML Attributes
XML documents typically store data in nodes of the XML ‘tree’; however, they may also store important values in node attributes. QuerySurge can handle data in either of these objects; in this discussion, we show how to set up an XML to pull data back both from nodes as well as from node attributes.
Setting up the XML Schema File
We’ll use the sample “rates” XML below to illustrate the setup. In the sample XML, the <rate> tag is the “major” tag; therefore each <rate> tag will be viewed as a row. Each child tag of <rate> will be a column. In addition, some of the child tags have attributes; for example, the <Country> tag has two attributes, “iso” and “ison”, which contain the 3-letter ISO code for the country, and the numeric ISO code, respectively. In this example, there are 6 nodes that we can map as columns (CD, Country, Currency, YESTERDAY, TODAY_ and PCTCHANGE), and there are 4 attributes (“iso” and “ison” on <Currency>, and “iso” on <YESTERDAY> and <TODAY_>.
<?xml version="1.0" encoding="UTF-8"?>
Sample 1. An XML with attributes
The mapping information that the XML driver requires is stored in a “Schema” file, which is a separate, custom XML file that the driver reads to process your XML data file. The Schema XML for each “data” XML must be created by hand and deployed with the driver. You can create a Schema file using any text or XML editor. A Schema XML starts with a root <schema> tag, which contains a <table> tag that itself contains one or more child <column> tags. Each of these tags defines the specifics of the XML-to-table mapping. For our sample XML (above), the Schema file is:
Sample 2. Schema File illustrating XML mappings (see Sample 1)
There are several things to note about the Schema file. First, in the <table> tag, the name attribute designates the name you will use in your SQL to query your XML. The file attribute gives the location of the XML data file (if the Schema XML and the data XML are both in the same directory, no file path is required for the data XML). Finally, the path attribute of <table> tag (actually an XPath instruction) defines the tag that will be considered a “row” in the table view of the XML.
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, Integer 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).
To define a node-to-column mapping, the Schema XML simply uses the tag name for the path attribute of the <column>. For example, consider the Country column:
<column name="Country" path="Country/name" type="Varchar" size="128"/>
By setting the path attribute to “Country”, we have identified the <Country> tag in the data XML with the column name Country.
Now, consider an attribute-to-column mapping, which we can illustrate with the CountryISO column:
<column name="CountryISO" path="Country/@iso" type="Varchar" size="3"/>
Here, we have set the path attribute to “Country/@iso”, which maps the iso attribute of the <Country> tag in the data XML to the column CountryISO.
For this example, save the Schema XML to a file curr-xchg-rate-schema_attr.xml and the data XML to a file curr-xchg-rate-data_attr.xml. Put both files in the same directory, and make a note of the directory path, which will be required to set up the XML Connection in QuerySurge.
Note that what we have done in this example 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.
Querying your XML with SQL
When you query your XML, QuerySurge uses your XPath instructions to read your XML as if it were a database table. As noted, you use the name attribute of the table tag for your table name in your SQL. A robust SQL syntax is available. A sample query for the example shown here might be:
from `curr-xchg-rates_hier` where YESTERDAY > 80.0
Sample XML Files
Sample XML and schema files for this article can be downloaded here.