QuerySurge Supports XML
QuerySurge supports XML with an XML JDBC driver that lets QuerySurge “see” your XML as a database table. To set up QuerySurge with the JDBC driver, some manual setup is required. This is because the driver needs some information about the structure of your XML in order to process it.
Setting up the XML Schema file
1) In order to set up an XML for querying, the JDBC driver requires an XML-to-table mapping, so that QuerySurge can view the XML as a database table. The mapping process consists of identifying “major” XML tags that will constitute “rows” in the table view of the XML, and child tags of the “major” tags, which will be viewed as columns of the table. We’ll use the sample “rates” XML below to illustrate how this is done. 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 this example, there are 7 columns (CD, Country, Currency, ISO, YESTERDAY, TODAY_ and PCTCHANGE). For this illustration, this XML will be stored in a file named:
curr_xchg_rate_data.xml.
<?xml version="1.0" encoding="UTF-8"?>
<rates>
<rate>
<CD>A1</CD>
<Country>GERMANY</Country>
<Currency>Euro</Currency>
<ISO>EUR</ISO>
<YESTERDAY>0.761645</YESTERDAY>
<TODAY_>0.763246</TODAY_>
<PCTCHANGE>0.002102</PCTCHANGE>
</rate>
<rate>
<CD>H2</CD>
<Country>GREECE</Country>
<Currency>Euro</Currency>
<ISO>EUR</ISO>
<YESTERDAY>0.761645</YESTERDAY>
<TODAY_>0.763256</TODAY_>
<PCTCHANGE>0.002102</PCTCHANGE>
</rate>
</rates>
2) The mapping information that the XML driver requires is stored in a “Schema”, which is a separate, custom XML file that the driver reads to process your XML data file. The Schema XML for each 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, containing a <table> tag which itself contains one or more child <column> tags. Each of these tags has attributes that define the specifics of the XML-to-table mapping. For our sample XML (shown above), the Schema file is:
<schema>
<table name="curr_xchg_rates" file="C:\<path>\curr_xchg_rate_data.xml" path="/rates/rate">
<column name = "CD" path="CD" type = "Varchar" size = "128"/>
<column name = "Country" path="Country" type = "Varchar" size = "128"/>
<column name = "Currency" path="Currency" type = "Varchar" size = "128"/>
<column name = "ISO" path="ISO" type = "Varchar" size="128"/>
<column name = "YESTERDAY" path="YESTERDAY" type = "Decimal"/>
<column name = "TODAY_" path="TODAY_" type = "Decimal"/>
<column name = "PCTCHANGE" path="PCTCHANGE" type = "Decimal"/>
</table>
</schema>
There are several things to note about the Schema file. First, in the <table> tag, the name attribute designates the name you will use to query your XML when you write SQL against it. The file attribute gives the location of the XML data file. Finally, the path attribute (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, as the name implies. The name attribute gives the column name (you can use it in your SQL). The path attribute is an XPath that locates the tag corresponding to a value in the column. The type attribute gives the data type (both 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 (2GB), which will make all your Varchar columns into CLOB types in QuerySurge. In addition, using the large default will most likely cause the processing to take up significant amount disk space during the run.
Save your Schema file and make note of the location. We’ll call the file curr_xchg_rate_schema.xml.
Setting up the XML Connection
1) Login to QuerySurge as an Administrator and navigate to the Administration view.
2) Select Connections in the left nav tree and click the Add button
3) Click Next
4) Enter a ‘Connection Name’ and select ‘All Other JDBC Connections (Connection Extensibility)’ on the drop-down for ‘Data Source’.
5) Click Next and enter “jstels.jdbc.xml.XMLDriver2” into the ‘Driver Class’ field.
6) Click ‘Next’ and enter the following into the ‘Connection URL’ field:
jdbc:jstels:xml:<Path to XML Schema File>?dbInMemory=falseNote: In the URL, “<Path to XML Schema File>” refers to the full file path to the Schema file (including the file name and extension), not to the XML test data file.
7) If desired, create a simple test query in the ‘Test Query’ field as shown above.
8) Click Next to review the connection information.
9) Click the Test Connection button if you entered a test query (the button will be disabled if you did not enter a test query). Close the ‘Connection Test’ popup.
10) Click Save.
11) You have created your QuerySurge XML connection.
12) You can query your XML using a standard SQL dialect. The driver uses the H2 Database SQL dialect. For the sample XML shown in this document, an example query is (note that the table name is enclosed in back-tics; this is required when the table name contains spaces, dashes or other special characters):
SELECT CD,Country,Currency,YESTERDAY,TODAY_ from curr_xchg_rates where YESTERDAY > 80.0
13) If you have XMLs of different structure, repeat the steps above to create connections for querying them.
Implementation Notes
1) Please note that you’ll need to deploy the driver files to all Agents if the driver was not deployed when each Agent was installed. Instructions on deploying a driver to an Agent are available in this article.
2) Since file paths are involved, all of the Agents must have access to the file paths (storing them locally at an identical file path is the easiest way to do this; if they are stored on network share drives, all Agents will need proper access and rights).
3) Sample files for the specific setup discussed in this article are available for download in the Resources section below.
Resources