XSD and SQL Server 2000

Pat Gannon
Solien Technology, Inc.

July 2002

Summary: This article provides an overview of XSD and an introduction to importing and exporting XML using SQL Server 2000.

Introduction

Since 1998, when the first version of the eXtensible Markup Language (XML) was officially adopted by the W3C, nearly every area of software has changed to leverage its power. Using XML, structured data can be exchanged between heterogeneous sources using standard, pre-built serialization and de-serialization libraries, eliminating the need for manually parsing data. With the popularization of this flexible language, it has become increasingly necessary to have a standard, easy-to-implement method of describing the structure of the data being represented in an XML document so that it can be validated. Succeeding DTDs, a more tedious language used for this purpose, the XSD (XML Schema Definition) language describes XML with XML.

One technology that has obvious uses for XML is software related to relational databases. Starting with SQL Server 2000, Microsoft is one of several database vendors leveraging XML in a variety of ways. Data in XML can be imported into or easily exported from a SQL Server database. If the format of the XML is not pre-determined and can be directly derived from the database schema, then importing and exporting can be done directly. If, however, business objects or other requirements dictate a particular structure for the XML, then an annotated XSD file may be used to instruct SQL Server how to translate between tables and columns in the database schema and tags in the XML data.

XSD Overview

Every node in an XSD document has a namespace prefix. The root node declares the locations of several XSD files, such as the one describing the XSD language itself and the Microsoft extensions to it if you're mapping to a database schema. An "element" tag in the XSD document represents a tag in the described XML. XML tags that contain other XML tags are associated with complex types, while tags that simply contain literal data, such as a string or a number are associated with simple types. Complex types contain a set of elements (most commonly a sequence) and some number of attributes (values specified inside the tag declaration). Several simple types such as string and decimal are already defined, but new simple types may be defined as well, such as patterns (e.g., legal license plate numbers).

An example XML document and the XSD document that describes it are provided below. The XML document describes a user on a fictional system. It contains their name, their email address, their login name and password, the status of their account and a number of roles that the user assumes. It also contains a resource sub-node (a user is a type of resource in this system), which contains an identifier for the account and a set of permissions on that account. For example, the first permission says that either the owner of the account, a manager, or an editor may view the account information. The resource sub-node also contains a number of local roles such as the "owner" of the account (where a manager is a manager for all user accounts, an owner is only an owner for a particular user account).

[XML]

<User>

<Inherit>False</Inherit>

<Role>Manager</Role>

<Role>Owner</Role>

<Role>Editor</Role>

</Permission>

<Inherit>False</Inherit>

<Role>Owner</Role>

<Role>Editor</Role>

</Permission>

<Name>MANAGE SECURITY</Name>

<Role>Administrator</Role>

</Permission>

<Inherit>False</Inherit>

<Role>Manager</Role>

<Role>HungryoUserogrammer</Role>

<Role>CheeseMaker</Role>

<Role>Tonto</Role>

<Role>Owner</Role>

<Role>Editor</Role>

</Permission>

<User>Larry</User>

<Role>Editor</Role>

</LocalRole>

<Role>Owner</Role>

</LocalRole>

</Resource>

<MiddleName>Merton</MiddleName>

<LastName>Roberts</LastName>

<Password>password</Password>

<Status>Pending</Status>

<Role>Manager</Role>

<Role>Editor</Role>

</User>

Code Listing 1. Example XML File - user.xml

[XSD]

<?xml version="1.0" encoding="utf-8" ?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:annotation>

<xsd:appinfo>

<sql:relationship name="User_ResourcePermission"

parent="USER"

parent-key="user_id"

child="RESOURCE_PERMISSION"

child-key="resource_id" />

<sql:relationship name="ResourcePermission_Permission"

parent="RESOURCE_PERMISSION"

parent-key="permission_name"

child="PERMISSION"

child-key="perminission_name" />

<sql:relationship name="ResourcePermission_Role"

parent="RESOURCE_PERMISSION"

parent-key="role_name"

child="ROLE"

child-key="role_name" />

<sql:relationship name="User_LocalRoles"

parent="USER"

parent-key="user_id"

child="LOCAL_ROLES"

child-key="user_id" />

<sql:relationship name="Resource_LocalRoles"

parent="USER"

parent-key="user_id"

child="LOCAL_ROLES"

child-key="resource_id" />

<sql:relationship name="LocalRoles_Roles"

parent="LOCAL_ROLES"

parent-key="role_name"

child="ROLE"

child-key="role_name" />

<sql:relationship name="LocalRoles_User"

parent="LOCAL_ROLES"

parent-key="user_id"

child="USER"

child-alias="rUSER"

child-key="user_id" />

</xsd:appinfo>

</xsd:annotation>

<xs:element name="User" sql:relation="USER">

<xs:complexType>

<xs:all>

<xs:element name="Resource" type="resource" />

<xs:element name="FirstName" type="xs:string" />

<xs:element name="MiddleName" type="xs:string" />

<xs:element name="LastName" type="xs:string" />

<xs:element name="Login" type="xs:string" />

<xs:element name="Password" type="xs:string" />

<xs:element name="Email" type="xs:string" />

<xs:element name="Status" type="xs:string" />

</xs:all>

</xs:complexType>

</xs:element>

<xs:complexType name="resource">

<xs:sequence>

<xs:element name="OID" type="xs:guid"

sql:field="user_id" />

<xs:element name="Permission" type="permission"

minOccurs="0" maxOccurs="unbounded" />

<xs:element name="LocalRole" type="localrole"

minOccurs="0" maxOccurs="unbounded" />

</xs:sequence>

</complexType>

<xs:complexType name="localrole"

sql:relation="ROLE rUSER"

sql:relationship=

"Resource_LocalRoles LocalRoles_Roles LocalRoles_User">

<xs:sequence>

<xs:element name="User" type="xs:string"

sql:field="login" />

<xs:element name="Role" type="xs:string"

sql:field="role_name" />

</xs:sequence>

</xs:complexType>

<xs:complexType name="permission"

sql:relation="PERMISSION"

sql:relationship=

"User_ResourcePermission ResourcePermission_Permission">

<xs:sequence>

<xs:element name="Name" type="xs:string"

sql:field="permission_name" />

<xs:element name="Role" type="xs:string" minOccurs="0"

maxOccurs="unbounded" sql:field="role_name" />

</xs:sequence>

</xs:complexType>

</xs:schema>

Code Listing 2. Example XSD File - user.xsd

Obtaining data from SQL Server as XML using the SQLXML classes for .NET

The following examples are written in C# and require installation of the Microsoft Core XML Library 4.0 (available free from Microsoft at http://download.microsoft.com/download/xml/SP/40SP1/WIN98MeXP/EN-US/msxml.msi) and Microsoft SQL XML 3.0 (also available free from Microsoft at http://download.microsoft.com/download/SQLSVR2000/SP/3.0SP1/WIN98MeXP/EN-US/sqlxml.msi). They also assume that the XSD file is saved in the same directory as the executable file (such as the "bin\debug" subdirectory of your VS.NET project while debugging) and that the following list of namespaces are referenced:

[C#]

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.IO;

using System.Data;

using System.Xml;

using System.Xml.Xsl;

using Microsoft.Data.SqlXml;

In this example, all users in the database are exported as XML using the SqlXmlCommand managed class provided in the Microsoft.Data.SqlXml library which must be added to the "references" section of the project (note: this is a non-running example in that it requires several database tables that are not supplied). On the first line, the command class is initialized with the connection string (not supplied). On the second line, the root tag of the XML document being generated is specified since the results of the query will come back from SQL Server as an XML fragment. On the third and fourth lines, the query string and type are specified: this example obtains all users (the X-Path query "User" returns all root nodes named "user", which translates into all rows in the user table, via the mappings specified in the above XSD file). An X-Path query to return only users in the city "Santa Rosa" would be "User[city='Santa Rosa']". SQL queries or template files may also be specified. On the fifth line, the XSD file that the command object will use to translate the data into XML is specified.

Two different methods of executing the query are shown, which differ in the type of output they produce. This example uses the 'ExecuteXmlReader' method to output the XML into a text box (lines ten through fourteen). The other method shown (on lines eighteen through twenty-four), 'ExecuteToStream' is used to output the XML to a file so that it can be loaded into the application that is registered in windows to open XML files (usually Internet Explorer as indicated in the message output in the text box). The location that the XML output is loaded in is chosen by a drop down control called 'OutputList' in this example. A label control called 'LabelUser' is also used to give the user a success message.

If an error occurs during query execution, the "catch block" is invoked (line twenty-seven). A description of the error is in the "Message" property of the SqlXmlException thrown, just as with most exceptions, but many times this message is extremely generic and you must get the detailed error from the object's error stream ("ErrorStream" property). In this example, both messages are concatenated and output to the label control (line thirty-five).

[C#]

SqlXmlCommand cmd = new SqlXmlCommand( sConnStr );

cmd.RootTag = "Users";

cmd.CommandText = "User";

cmd.CommandType = SqlXmlCommandType.XPath;

cmd.SchemaPath = @"user.xsd";

try

{

if(OutputList.SelectedIndex > 0)

{

XmlReader xr = cmd.ExecuteXmlReader();

XmlDocument xd = new XmlDocument();

xd.Load(xr);

XmlText.Text = xd.OuterXml;

LabelUser.Text = "Export successful";

}

else

{

FileStream f = new FileStream(

@"c:\users_generated.xml", FileMode.Create);

cmd.ExecuteToStream(f);

f.Close();

System.Diagnostics.Process.Start(@"c:\users_generated.xml");

LabelUser.Text = "Output is stored in " +

@"c:\users_generated.xml and has been launched in IE";

}

catch (SqlXmlException sqlxmlError)

{

string result = string.Empty;

if(sqlxmlError.ErrorStream != null)

{

sqlxmlError.ErrorStream.Position = 0;

result = new StreamReader(sqlxmlError.ErrorStream).ReadToEnd();

}

LabelUser.Text = sqlxmlError.Message +

"\n\nEmbedded Exception:\n" + result;

}

Code Listing 3. Exporting XML

Importing XML into SQL Server

The next example imports XML into a set of tables in SQL Server using the SqlXmlAdapter managed class and a data set. The first eight lines of code are virtually identical to the above example (except the try statement is a few lines higher to take full advantage of the generalized exception handler that is added). On the ninth line, the adapter is initialized with a reference to the command object. In the next section of code (lines twelve through fifteen), the data set is initialized and reads the schema in the XSD file (user.xsd) with the help of a standard stream reader. Next, on lines eighteen through twenty, the XML in the text box "XmlText" is read into a specialized XML reader and inserted into the data set. On line twenty-three, the adapter stores the data from the dataset in the actual tables in SQL Server then presents a success message to the user. Lastly, the error handling is the same as with the previous example except that there is a "catch all" clause (line thirty-eight) below the catch clause for SqlXmlExceptions because other types of exceptions may be thrown from the operations involving the data set.

[C#]

try

{

//Initialize SQLXML objects

SqlXmlCommand cmd = new SqlXmlCommand( sConnStr );

cmd.RootTag = "Users";

cmd.CommandText = "User";

cmd.CommandType = SqlXmlCommandType.XPath;

cmd.SchemaPath = @"user.xsd";

SqlXmlAdapter oAdapter = new SqlXmlAdapter( cmd );

//Initialize DataSet

DataSet oDataSet = new DataSet();

System.IO.StreamReader xmlStream =

new System.IO.StreamReader(@"user.xsd");

oDataSet.ReadXmlSchema(xmlStream);

//Read the XML into the dataset

XmlTextReader oReader = new XmlTextReader( XmlText.Text,

System.Xml.XmlNodeType.Element, null );

oDataSet.ReadXml( oReader, System.Data.XmlReadMode.Auto );

//Update the database

oAdapter.Update( oDataSet );

LabelUser.Text = "Import successful.";

}

catch (SqlXmlException sqlxmlError)

{

string result = string.Empty;

if(sqlxmlError.ErrorStream != null)

{

sqlxmlError.ErrorStream.Position = 0;

result = new StreamReader(

sqlxmlError.ErrorStream).ReadToEnd();

}

LabelUser.Text = sqlxmlError.Message +

"\n\nEmedded Exception:\n" + result;

}

catch( Exception e ) //the catch all

{

LabelUser.Text = e.Message;

}

Code Listing 4. Importing XML using a dataset

Another way to import data in XML format into a SQL Server database is using the XML Bulk Loader. Unfortunately, there is no managed (.NET) class for working with the bulk loader. To use it with a .NET project, create an interop to the "Microsoft SQLXML BulkLoad 3.0 Type library" COM component by adding a reference to it in your project file (if it is not in your list of available COM components, browse to xblkld3.dll). In the example below, the bulk loader object is initialized (line one), then its connection string and the location of the error log it is to create is set (lines four and five). On the last line (six), the data is actually imported using the execute function, which accepts the name of the schema file and the name of the file containing the XML to import. In addition to requiring a COM-interop, this technique is of limited utility because there is no instance of the execute method which can read the schema file from memory rather than from a file, and there is also no instance that will read the XML to import as a string, although there is a method that accepts the XML as a VB (as oppose to .NET) stream.

[C#]

SQLXMLBULKLOADLib.SQLXMLBulkLoadClass oBulkLoader = new SQLXMLBULKLOADLib.SQLXMLBulkLoadClass();

oBulkLoader.ConnectionString = sConnStr;

oBulkLoader.ErrorLogFile = @"c:\error.log";

oBulkLoader.Execute( @"user.xsd", @"user.xml" );

Code Listing 5. Importing XML using the SQL Bulk Loader

Conclusion

This article and the included code samples present a broad overview of XSD and the XML capabilities of SQL Server 2000. To find out more details about the XML/XSD capabilities of SQL Server 2000, please visit the Microsoft Developer Network website (http://msdn.microsoft.com). Some direct links to related articles are provided below.

Feedback and Support

Questions? Comments? Suggestions? For feedback on this article, please send an e-mail message to pat@solien.com.

More Information

SQLXML Managed Classes:

http://msdn.microsoft.com/library/en-us/dnsql2k/html/sqlxml_intromanagedclasses.asp

Loading a DataSet from XML:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconloadingdatasetfromxml.asp

SQL Server Bulk Load Object Model:

http://msdn.microsoft.com/library/en-us/sqlxml3/htm/bulkload_3g30.asp

Guidelines and Limitations of XML Bulk Load:

http://msdn.microsoft.com/library/en-us/sqlxml2/bulkload_5144.asp

Examples of Bulk Loading XML Documents:

http://msdn.microsoft.com/library/en-us/sqlxml3/htm/bulkload_6bos.asp