MSDN India - Article

Click Here to Install Silverlight

India

Change

Understanding and Using XML Views in SQL Server

By Srinivas Sampath

Article

Posted: September 02, 2003

SQL Server 2000 provides excellent support for XML. Since the SQL feature set of SQL Server keeps changing, rather than updating the core database engine, Microsoft opted to segregate the XML features into a separate toolkit called SQLXML. In its current version, SQLXML allows you to define XML views over relational data. What this means is, data in your tables can be visualized as XML documents and queried using XPath. The good thing is, your data access need not use the XML enhancements of SQL Server (like FOR XML AUTO). SQLXML handles the conversion of your relational data into XML documents at the client side (or the machine where SQLXML is installed).

In order to define these XML views, you need to use XML Schemas. Simply put, an XML schema explains how the resultant XML document will look like and what the constraints are on the data. Earlier versions of SQLXML supported a reduced version of the XML Schema language (called XDR schemas, or XML Data Reduced Schemas), but the current version of SQLXML supports the full XML Schemas specification.

SQL Server has also added its own extensions to the XML Schema specification to allow the mapping of the schema to actual SQL Server tables. This is required since the generated XML document need not have the same names for the nodes as that of SQL Server tables. If no mapping is specified, default mapping occurs. For example, a SQL Server table called authors might be represented as AuthorList. Schemas that are thus decorated with the extensions are called Annotated Schemas.

In this article, we will see how to define simple schemas for relational tables and query the resultant XML document using XPath. I'll be using the pubs database in all the samples.

What you will need

Before you attempt the samples in this article, you need to have SQLXML installed and also should have configured it to work with schema files. Refer to the following links for more information:

1.	SQLXML Download
2.	Configuring SQLXML.
3.	Playing around with SQLXML. This contains a comprehensive list of XML articles that myself and Vinod (MSDN Community Star) have written.

Sample A - List of Authors (Element Fashion)

The best way to learn to use XML Schemas is to work on an example. In this example, we will create an XML document of all the authors in the pubs database and query this XML document using XPath. For this example, we will want to create an XML document like this:

Before we start, it is apt to say that I prefer to use Visual Studio .NET to create XML Schema files (XSD extension), but you could use any editor of your choice. Here is the XSD file that we will need for the above example.

<?xml version="1.0" encoding="utf-8" ?>
    <xs:schema
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
            xmlns:xs="http://www.w3.org/2001/XMLSchema">
            <xs:element name="authors">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element name="au_id" type="xs:string"/>
                        <xs:element name="au_fname" type="xs:string"/>
                        <xs:element name="au_lname" type="xs:string"/>
                    </xs:sequence>
                </xs:complexType>
        </xs:element>
    </xs:schema>

Create the above XSD file and save it as authors.xsd in the template folder created in the pubs virtual directory (as cited in the links above).

Let's create the template file that will query this XML document. Create the following file and name it as authors.xml. Store this file also in the template folder for pubs.

<myAuthors xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="authors.xsd">
            /authors
    </sql:xpath-query>
</myAuthors>

Now, you can run the following query in Internet Explorer: http://localhost/pubs/template/authors.xml

If everything went well, you should see the following output (results abridged):

Ok, now having seen it work, let's dissect the code and see what we achieved.

	All XML Schema files are contained within the <xs:schema> and </xs:schema> tags. The xs is a namespace prefix.
	For annotated schemas, we need to add the sql namespace of urn:schemas-microsoft-com:mapping-schema. Note that sql is only an optional name. You can use any name that you want.
	Next, we define the layout of the XML document. In our example, we have defined that we will have an authors element as indicated by the <xs:element/> tag.
	Within an element, if you want to define other elements, you need to embed them inside a <xs:complexType/> node and an <xs:sequence/> node. In the example above, we have done this to define the remaining content of the required XML document.
	That's all there is to the XSD. Quite simple actually...
	Once the XSD is done, the next step is to setup a template file that uses SQLXML to connect to SQL Server and generate the XML document based on the information in the XSD.
	In the template file, note that we are specifying the root element of the XML document as myAuthors. We also add a namespace reference sql to the URN urn:schemas-microsoft-com:xml-sql.
	We then write the query that we want to make inside a <xpath-query/> tag and also specifying the associated schema file using the mapping-schema attribute.
	Within the <xpath-query/> tag, we specify an XPATH expression to return the matching rows. In our example, we have specified /authors. This means that we want all the elements with tag name of authors which potentially is the entire table content.

Now, how did SQLXML know that the table from which data is to be fetched is called authors? As mentioned earlier, if no mapping between the schema and SQL Server is specified, default mapping takes place. Thus, for our example, since in the schema file we specified authors as the element name, it was tied by default to the authors table. The same holds good for the fields too.

Sample B - List of Authors (Attribute Fashion)

To play around the schema file a bit, let's say that we now want our XML document to look like the following:

Note that we now want all the details about an author as attributes. So how does the schema file change?? Here is the revised schema file.

<?xml version="1.0" encoding="utf-8" ?>
    <xs:schema
        xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
        xmlns:xs="http://www.w3.org/2001/XMLSchema">
        <xs:element name="authors">
            <xs:complexType>
                <xs:attribute name="au_id" type="xs:string"/>
                <xs:attribute name="au_fname" type="xs:string"/>
                <xs:attribute name="au_lname" type="xs:string"/>
            </xs:complexType>
        </xs:element>
    </xs:schema>

The template file remains the same as shown above. Now, you can run the following query in Internet Explorer: http://localhost/pubs/template/authors.xml

If everything went well, you should see the following output (results abridged):

The changes made to the schema file are very simple. Within the <xs:complexType/> tag, we just defined a series of <xs:attribute/> tags along with the name and the data type. Note again that default mapping happens to the field names and the table name to the authors table.

So what should you do if you want a mix of attributes and elements. For example, what if we want the output as:

I leave it as an exercise to the reader to try this out.

Sample C - List of Authors (with Mapping specification)

Let's assume that we now want our XML structure like this:

Note that the names of the tags and attributes are all different from the underlying authors table. In this case, we need to specify a mapping between the required name and the actual table/field name. Here is the modified schema file.

<?xml version="1.0" encoding="utf-8" ?>
    <xs:schema
        xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
        xmlns:xs="http://www.w3.org/2001/XMLSchema">
        <xs:element name="distinguishedAuthor" sql:relation="authors">
            <xs:complexType>
                <xs:attribute name="authorID" type="xs:string" sql:field="au_id"/>
                <xs:attribute name="authorFirstName" type="xs:string" sql:field="au_fname"/>
                <xs:attribute name="authorLastName" type="xs:string" sql:field="au_lname"/>
            </xs:complexType>
        </xs:element>
    </xs:schema>

Now that we have changed the name of the node, we need to change the template file also. Here is the updated template file.

<myAuthors xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="authors.xsd">
        /distinguishedAuthor
    </sql:xpath-query>
</myAuthors>

Now, you can run the following query in Internet Explorer: http://localhost/pubs/template/authors.xml

If everything went well, you should see the following output (results abridged):

The specifiction of the relationship is very easy.

	For tables, we use the sql:relation attribute and specify the name of the table as the value.
	For fields, we use the sql:field attribute and sepcify the name of the field as the value.

Sample D - List of Authors and Titles (master-child relationships)

The examples that we specified till now are quite simple and work off only one table. But what do you do if you want to represent a relationship (for example a master - child relationship). For example, let us assume that you want to produce an output like the following:

In the above example, we need the list of titles that the author has written. In the pubs database, this relationship can be dervied by joining 3 tables viz: authors, title, titleauthors. Joining 3 tables is a little more involved, so for now we will look at joining only 2 tables and take the 3 table scenario later. Here is the revised schema file.

<?xml version="1.0" encoding="utf-8" ?>
    <xs:schema
    xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
    xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:annotation>
        <xs:appinfo>
            <sql:relationship
                name="authors2titleauthor"
                parent="authors"
                parent-key="au_id"
                child="titleauthor"
                child-key="au_id"/>
        </xs:appinfo>
    </xs:annotation>
    <xs:element name="authors">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="au_fname" type="xs:string"/>
                <xs:element name="au_lname" type="xs:string"/>
                <xs:element name="titles"
                        sql:relation="titleauthor"
                        sql:key-fields="au_id"
                        sql:relationship="authors2titleauthor">
                    <xs:complexType>
                        <xs:attribute name="title_id" type="xs:string"/>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
            <xs:attribute name="au_id" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>

The template file also has to be changed (since we changed the tag name in the previous example). The revised template file is as follows.

<myAuthors xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="authors.xsd">
        /authors
    </sql:xpath-query>
</myAuthors>

Now, you can run the following query in Internet Explorer: http://localhost/pubs/template/authors.xml

If everything went well, you should see the following output (results abridged):

The XSD file needs some explanation.

	The first thing is to ensure that we create a relationship between the authors table and the titleauthor table. This is done by the <sql:relationship/> node in the <xs:appinfo/> section.
	The rest of the file is not complex, except that place where we display the titles for an author.
	We define the titles node as usual, but specify that this node set will get all the data from the titleauthor table as governed by the relationship defined earlier. This is in effect saying that we need a statement like the following:

SELECT
authors.au_id, authors.au_fname, authors.au_lname,
titleauthor.title_id
FROM
authors
LEFT OUTER JOIN titleauthor ON authors.au_id = titleauthor.au_id

Let's just extrapolate the example so that we now have the list of actual titles instead of the key value. This now requires a join between titleauthor and the " table also. Here is the updated schema file.

<?xml version="1.0" encoding="utf-8" ?>
    <xs:schema
        xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
        xmlns:xs="http://www.w3.org/2001/XMLSchema">
        <xs:annotation>
            <xs:appinfo>
                <sql:relationship
                    name="authors2titleauthor"
                    parent="authors"
                    parent-key="au_id"
                    child="titleauthor"
                    child-key="au_id"/>
                <sql:relationship
                    name="titleauthor2titles"
                    parent="titleauthor"
                    parent-key="title_id"
                    child="titles"
                    child-key="title_id"/>
            </xs:appinfo>
        </xs:annotation>
        <xs:element name="authors" sql:key-fields="au_id">
            <xs:complexType>
                <xs:sequence>
                    <xs:element name="au_fname" type="xs:string"/>
                    <xs:element name="au_lname" type="xs:string"/>
                    <xs:element name="titles"
                        sql:relation="titles"
                        sql:key-fields="title_id"
                        sql:relationship="authors2titleauthor titleauthor2titles">
                        <xs:complexType>
                            <xs:attribute name="title_id" type="xs:string"/>
                            <xs:attribute name="title" type="xs:string"/>
                        </xs:complexType>
                    </xs:element>
            </xs:sequence>
                <xs:attribute name="au_id" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>

There is no change to the template file. Now, you can run the following query in Internet Explorer: http://localhost/pubs/template/authors.xml

If everything went well, you should see the following output (results abridged):

The main difference we see here is the definition of a second <sql:relationship/> element in the <xs:appinfo/> tag. This specifies the relationship between titleauthor and the titles table. Finally, in the titles element, we change the sql:relation attribute to point to the titles table and change the sql:relationship attribute to use both the above defined relationship. This effectively joins all the required tables.

A useful example

You quickly need to get the list of all tables, their columns and datatypes is a nice HTML format. What will you do?? Well, many things actually :-) But foremost, you need to decide how to get this information. SQLXML, XSD Schemas along with SQL Server information schema views can help you here. Here is a simple example that dumps the required information into HTML tables.

Here is the schema file:

<?xml version="1.0" encoding="utf-8" ?>
    <xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"         xmlns:xs="http://www.w3.org/2001/XMLSchema">
        <xs:annotation>
            <xs:appinfo>
                <sql:relationship
                    name="tableColumns"
                    parent="information_schema.tables"
                    parent-key="table_name"
                    child="information_schema.columns"
                    child-key="table_name"/>
            </xs:appinfo>
        </xs:annotation>

    <xs:element
        name="table"
        sql:relation="information_schema.tables"
        sql:key-fields="table_name"
        type="tableType"/>

    <xs:complexType name="tableType">
        <xs:sequence>
            <xs:element
                name="columns"
                sql:relation="information_schema.columns"
                sql:key-fields="table_name"
                sql:relationship="tableColumns">
                <xs:complexType>
                    <xs:attribute name="columnName" sql:field="column_name"/>
                    <xs:attribute name="position" sql:field="ordinal_position"/>
                    <xs:attribute name="dataType" sql:field="data_type"/>
                </xs:complexType>
            </xs:element>
        </xs:sequence>
        <xs:attribute name="name" sql:field="table_name"
            sql:limit-field="table_type"
            sql:limit-value="BASE TABLE"/>
    </xs:complexType>
</xs:schema>

Here is the template file:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="DatabaseView.xsl">
    <sql:xpath-query mapping-schema="DatabaseSchema.xsd">
        /table
    </sql:xpath-query>
</root>

Here is the XSL file that formats the output:

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="root">
        <HTML>
            <BODY>
                <H1>Pubs Data Dictionary</H1>
                <HR/>
                <xsl:apply-templates select='table'/>
            </BODY>
        </HTML>
    </xsl:template>

    <xsl:template match="table">
        <TABLE WIDTH="20%" BORDER="1" STYLE="font-family:verdana;font-size:8pt;">
            <TR>
                <TD COLSPAN="2" STYLE="background-color:blue;color=yellow;">
                    <B><xsl:value-of select="@name"/></B>
                </TD>
            </TR>
            <xsl:apply-templates select="columns">
                <xsl:sort select="@position"/>
            </xsl:apply-templates>
        </TABLE>
        <BR/>
    </xsl:template>

    <xsl:template match="columns">
        <TR STYLE="background-color=lightyellow;">
            <TD WIDTH="50%"><xsl:value-of select="@columnName"/></TD>
            <TD WIDTH="50%"><xsl:value-of select="@dataType"/></TD>
        </TR>
    </xsl:template>
</xsl:stylesheet>

You can see that I've named the files as follows:

	Schema file (DatabaseSchema.xsd)
	Template file (DatabaseSchema.xml)
	XSL stylesheet (DatabaseView.xsl)

You can execute this sample using the following URL: http://localhost/pubs/template/DatabaseSchema.xml?contentType=text/html

The output will be like the following (results abridged)

Reading XML in client applications

We will not be going into this topic in detail, but suffice to say that SQLXML comes with a set of .NET managed classes that help you to connect to templates and execute queries. The return data can be placed into a dataset and then updated directly back to the database using the default diffgram features of the data set. For a sample project, refer to the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_projandtaskssample.asp. The application presented here shows how .NET applications can integrate with SQLXML effortlessly.

Conclusion

Well that brings us to the end of this rather long article :-) Its length because of the amount of code samples that I've pasted, but otherwise it is quite short!! Hopefully, I've evoked your interest in terms of the capabilities of SQL Server with XML. SQLXML really augments the XML capabilities of SQL Server and helps you write very rich applications that communicate entirely in XML.

For more questions, you can mail me at srisamp@hotmail.com or visit my web site at http://www32.brinkster.com/srisamp.

Manage Your Profile |Contact us