XML and the SQL 2000 Server part 3: XML Data with XPath Queries

This third article in the series on using XML with SQL Server 2000 covers more information on using Xpath quesries. Jayaram Krishnaswamy writes about using the XML View Mapper to create XML views to use in Xpath queries and how to use Xpath queries with the database.

In Part 1, I discussed the infrastructural details for accessing data from SQL 2000 Server using XML over HTTP. Also I discussed the T-SQL language extensions for facilitating data access using XML. Part 2 focussed on the practical aspects of querying over HTTP, in particular, using URL queries and template queries for both SQL Stataments. I also spoke about stored procedures for data access.

In this tutorial, yet another way of accessing data with Xpath queries will be my topic of discussion. SQL 2000 server’s support for XDR schemas provide the ability to specify Xpath queries against these schemas. In order to facilitate the derivation of these XDR schemas, Microsoft also provides an XML tool: XML View Mapper (not bundled with SQL 2000 Server, but a separate download). The bulk of the tutorial is about the basics of using this tool to create XML views that can be used in Xpath queries and using Xpath queries to query the database. For the purposes of discussion, a very small database is chosen to make the screen shots more viewer friendly. It may be helpful to review parts 1 and 2 referenced above as well as visit the w3c site to get a good understanding of the Xpath basics and syntax.

What are Xpath queries?

Xpath is a XML navigational language developed by the w3c organization. XML documents are based on hierarchical tree-like structure with nodes. Xpath provides a means to navigate to node/nodes within an XML document. Xpath queries are not the same type of implementation as URL queries and queries with template files considered in the previous tutorial. Direct URL queries and template based queries access data based on SQL. However, Xpath queries use a different mechanism.

{mospagebreak title=Xpath Queries}

The XML View allows production of XML content from relational data and queries of relational data as if it were XML, without converting it to XML. XML view is an XML document with additional information added to it called the annotations. This is a construct that allows the SQL Server to determine the tables to use when presented with an Xpath query that uses the XDR Schema. Microsoft’s XDR schema is a subset of the more comprehensive w3c’s XPath specification [XDR antedates W3C's version]. In the previous article, I showed that of all the T-SQL extensions, the for xml Explicit provides by far the finest control over the XML produced. However, creating for xml explicit queries is not straight forward, and that is where XDR annotated schemas comes to the rescue. Xpath query is not supported directly by the SQL 2000 Server, but using the OleDB Provider. The OleDB Provider converts XPath queries into SQL statements and appends the forXML Explicit clause to them. SQL Server 2000 operates on this SQL formatted statements.

In XPath queries, Tables and views in SQL Server are represented(mapped to) as <elements> and Columns as “attributes” of the <elements>. The annotations mentioned earlier are the mappings between relational database objects to the XML documents. The next picture shows a table stored in the relational database. The picture that follows is the XML view of the same table created using the XML View Mapper. The XML View Mapper will be discussed in detail later in the tutorial. The authors table is mapped to the <ElementType> with name attribute set to the name of the table, namely authors. The various column names are mapped to the different attributes with the Data Type in the table mapped to dt:type in the XML View. Column names go over to AttributeType names exactly. This is implied mapping-when the SQL item and the XDR item have the same name.

Table Design
 
XML View

Example with Two Tables Having A Relationship

This next picture shows two tables previously considered in parts 1 and 2 of this series. The picture that follows is the XMLView created using the XML View Mapper. Some features of the XDR Schema and the relationship of the relational tables to vis-a-vis the XML View will be explained.


{mospagebreak title=XDR Schema and XML View Mapper}

XDR Schema, Annotated XDR Schema, and XML View

First of all the XML View is an XML document and therefore the first line is the reference to an XML document of the current version and encoding. The root element of the XDR schema is always Schema as shown in the picture derived from the namespace urn:schemas-microsoft-com:xml-data . This is followed by three name spaces, for data, datatype and SQL that provide the annotations. This is followed by the elements[which is analogous to tables in the relational database] and the columns contained represented as attributes. For each of the columns, the datatype of the column is also specified. Finally since the tables are joined by a parent-child relationship, the annotation regarding this is also present in the XML View. Although there are 3 kinds of annotations in this,
there are several others[ please refer to documentation, In the SQL 2000 Server's Book on line look under the Active subset SQL 2000:XML] defined in the xml-sql namespace.

SQL:relation
SQL:field
SQL:relationship with attributes, key-relation, key, foreign-relation, foreign-key

From these two examples, if you get an impression that you can manually[with a text editor] write an XML View or an annotated XDR schema, you are not wrong in that assumption. But for more than 2 tables with relationships between them, manual crafting will be both tedious and error prone. This is where the XML View Mapper, a GUI steps in.

XML View Mapper

Simply stated the XML View Mapper takes in a SQL Data’s Schema and produce an XML View that can be used in not only posing queries that do not violate Xpath query rules, but also provides a means to test the Xpath queries. XML View Mapper can import an SQL Schema by connecting to an SQL Server 2000 and accessing the tables and columns in the database together with any defined relationships. Using this schema, it can produce an XDR annotated Schema / XML View. It also has inbuilt programs to validate a schema, edit a schema and run Xpath queries. It can also import an annotated XDR schema that produces an SQL Schema as well. XML View Mapper may be down loaded from several places,this is one of them. The installation is fast and easy and you can choose either a complete install, or a custom install. For documenting this tutorial a complete install was chosen. This installs the XMLMapper.exe and several ActiveX controls and dll’s into the install directory with a couple of samples. The installation also adds a short cut to All Programs as shown in this picture as well as documentation. The tool can be activated by double clicking this shortcut.

XML View Mapper Project

When the XML Viw Mapper program starts up, you will be presented with this screen wherein you may choose to create a new XML View Mapper project, or highlight one that is all ready present and open it up. Project files have the extension .smp.

The Map Editor form is where the mappings between the SQL objects on the left panel and the XML elements/attributes on the right panel is implemented. When the mapping is being made lines will be connecting from the SQL side to the XML side crossing the middle panel as will be seen later. In an empty [now it is empty] area in either of these panels, clicking inside the panel, allows us to import an SQL schema or an XDR schema. If such schemas already exist in the SQL Module, or the XDR module then they can be dragged and dropped into these panels.

{mospagebreak title=Adding an SQL Module}

To begin you may highlight, for example the SQL module, and right click which brings up this Add Module screen where you may choose to add the module. Or as shown in the picture that follows use the menubar item to add a module.

Assuming that an SQL module is added, familiar Data Link Properties window shows up as in the next picture-connecting to the data source. In step 1, the SQL 2000 Server is chosen, in step 2, the authentication information is supplied, and in step 3, the database to be connected to is specified. The connection may also be tested.

The database may have any number of tables, but you can choose the tables for which you want to create the XDR schema/XML View. These can be chosen by clicking the table names in the Available list on the left to the Selected list on the right.

Clicking OK will import these tables into the Project’s SQL Modules folder creating a file with .smt extension. Drag and drop this item into the left pane in the Map Editor.

The tables may be expanded to show the columns contained therein as shown in this picture. In the color table, although three columns were imported, an additional relationship is also imported due to an existing relationship in the database[between the color and nums tables].

{mospagebreak title=Generating and Mapping an XDR Schema}

Generating an XDR Schema

Now going to the main menu and clicking on Tools->Utilities->Generate XDR Module will automatically generate an XDR schema, and will be added to the XDR Modulesfolder in the Project explorer as shown in the picture that follows with the extension .smx.

Mapping Schema

Now drag and drop the XDR schema into the right panel in the Map Editor. This creates an untitled Map in the Map Module in the project explorer with the extension .smm, an internal format type used by Microsoft[which includes .smt and .smx discussed earlier].

By expanding all the nodes you can verify the type of mapping that is made. If you right click any of the connecting links between the SQL schema and the XDR you will bring up a menu item from which you may choose to carry out a number of things. The broken lines represent what is called the implicit mappings. It is possible to make an explicit mapping by keeping the mouse on the SQL object and draw a line[called the mapping line] to the XDR element. Any forbidden mappings [that are not allowed by Xpath queries] that you may attempt will be notified by a message with a warning icon.

For example, an explicit connection was made from NumID in the SQL’s nums table to the numid in the XDR’s Color elements numid attribute, and this brings up the XML View Mapper Path finder as shown and if accepted, this will create an explicit relationship as shown in the picture that follows. Explicit relationships may be deleted and edited. The settings for Map Editor, Mapping Format, and Mapping Filter can be found in Tools->Options and the information on the mapping lines is stored in the Map Modules.

The XDR Schema can be edited using a text editor which can accessed by right clicking on a mapping line and choosing the XDR Editor. To close this window you may use the Update Project and Close from the File Menu’s drop down list.

{mospagebreak title=Testing an Xpath Query Using the Internal Tester}

As mentioned previously the XML View Mapper also has a Xpath query tester program that can be invoked by clicking the XPath query Tester from the Tools drop down menu [or key F5] seen in an earlier screen shot. The Xpath query tester can also be invoked by right cliking the Mappings .smm file and choosing the Xpath query Tester. This brings up the following screen which initiates and completes the loading of the SQL information.

Clicking OK to this screen now brings up the query tester where in you can enter an Xpath query as shown in the next picture. When you click execute it will return an XML document which is produced by your query. The earlier SQL queries in Part 1, and Part 2 returned rowsets. Also notice the XPath query is posed against the database, and that is the reason you see the connection information. If the server is not running you will be asked to make a new connection. It is best to verify the connection before running the query.

{mospagebreak title=Exporting Schema and Query in URL}

Exporting the XDR Schema

Once the Xpath query verification is finished, you can save the project and export the XDR file by right clicking on the .smm file, or from
Tools->Export XDR Schema…, which produces an XML document. Now you save this document in the Schema folder of the SQL 2000 Server specific virtual directory discussed in
Part 1. The Xpath queries can now be executed by including them in the URL, or by including them in a template file.

Virtual directory for testing the Xpath queries

In order to test the Xpath queries using the XML View, a virtual directory was created in the SQL 2000 specific IIS with the following settings:

IIS Virtual Directory Server: XPHTEK
Virtual Directory Name: TestPath  Local Path:c:TestXPathxschema
Windows Integrated Security
SQL Server (local)
Database: TestWiz
Settings: Allow URL Queries, Allow template queries, Allow XPath
Virtual names: 
xobj
xschema  C:TestXPathxschema
xtemplate C:TestXPathxtemplate.

Xpath query in a URL

The syntax for specifying an Xpath query in an URL is as follows:
http://IISServer/VirtualRoot/SchemaVirtualName/
SchemaFile/XPathQuery[?root=ROOT]

For the virtual directory settings shown above this will be:
http://XPHTEK/TestPath/xschema/TestWizView.xml/color?root=root. The browser display for this query is shown in the next picture. The TestWizView.xml file[produced by the XML Viw Mapper] is saved in the c:TestXPath xschema folder. This query returns all the elements from the color table

If the query returns a single record, then it can be made to return an xml document by issuing the following query:
http://XPHTEK/TestPath/xschema/TestWizView.xml/color[@colid=2]
The Xpath query is color[@colid=2].This would then result in the following returned result shown in this picture.

{mospagebreak title=Xpath Query With a Template File} 

In Part 2, template files were discussed and how to use them for both SQL statements embedded in them, or for stored procedures. In a similar fashion, Xpath queries can be embedded in template files. These files are then placed in the template folder of the virtual directory. Once this is done, the Xpath queries can be executed by specifying the following in the URL:
http://IISServer/VirtualRoot/VirtualName/TemplateFile.xml
For the present setting of the virtual directory, the appropriate URL string is:
http://xphtek/Testpath/xtemplate/TestWizTempl.xml
The details of the TestWizTempl.xml template file is shown in the next picture:

Now the result of executing this query is shown in the next picture. If the mapping-schema file is not in the template folder, then an error will be generated. Note that the Xpath query must be enclosed by <SQL:xpath-query mapping-schema=”xxx.xml”></sql:xpath-query>

This tutorial is Part 3 of the XML Data related tutorials and draws upon the information from Part 1 as it relates to setting up the Virtual directory and Part 2 as it relates to notions of URL queries and template queries.

[gp-comments width="770" linklove="off" ]