SQLXML 3.0
Build Data-Driven Web Services with Updated XML Support for SQL Server 2000 |
Christian Thilmany |
This article assumes you're familiar with XML, SOAP, and SQL Server 2000 |
Level of Difficulty 1 2 3 |
Download the code for this article: SQLXML3.exe (239KB) |
SUMMARY XML is becoming the ubiquitous data format on the Web, and XML support in SQL Server is evolving to meet the additional demand. Using XML, SOAP, HTTP, and SQL Server, you can now build powerful Web Services easily. To show just how simple it is with SQLXML 3.0, this article walks the reader through the process step by step, from setting up a virtual directory enabling data access via HTTP to executing queries and building Web Services. Finally, the author illustrates the creation of two Web Services clients—one with C# that works with the Microsoft .NET Framework and one with the SOAP Toolkit 2.0 for anyone still using earlier development tools. |
t's hard to believe that XML support in SQL Server™ 2000 has been around for over two years. In the software world, that's a lifetime. SQL Server 2000 was the first version to provide native support, and this was limited to the more basic XML feature set (template queries, mapping schemas, and OPENXML). Using simple HTTP queries you could retrieve formatted relational data in XML format. With a little help and some Extensible Stylesheet Language (XSL) magic, you could spit out the data in a formatted, HTML-friendly manner. Later, with the introduction of features like updategrams, you could easily submit an XML-based SQL template to insert or update rows of information in SQL Server with little effort.
Initially, I thought that some would consider XML support a frivolous addition to an already powerful product. If a developer wasn't displaying SQL data in a Web page or feeding a system that only speaks XML, were these features all that useful?
Previously, the only viable approach for accessing data, for the middle-tier anyway, was through a traditional data access layer built with ODBC, OLE DB, or ADO. Now with SQLXML 3.0, SQL Server 2000, SOAP, BizTalk®, and the .NET Framework, XML is no longer a frivolous addition—it's the data language of choice.
Using SQLXML 3.0 for Data Access
SQLXML 3.0 is the third iteration of XML support for SQL Server. The biggest difference between the old way of representing data and the way it's represented with XML is how the rowset is created, where it is created (server-side or client-side), and how it is formatted (raw, nested, element-based, or attribute-based). For more on raw and explicit formats, refer to the information listed in the article summary.
For those of you already working with some of the .NET server products such as BizTalk, managed classes, and the like, you already know how important it is to use XML as your data format. If using XML for data access is new to you, this may take some getting used to. If you choose to use XML as your data format, you must take into account the subtle differences between relational and hierarchical representation and how you can exploit the benefits of a hierarchy.
If you are upgrading from a previous version, you can still run SQLXML 3.0 side by side with your current version. (See the sidebars "Side-by-side Support" and "Evolution of XML Support" for more information.)
Querying SQL Server with XML
The fastest way to begin accessing SQL Server 2000 using XML is through your browser. This is a great way to check whether you have everything set up correctly, and is also your first means of diagnosing problems should they appear. To access SQL Server using a URL via the browser or any HTTP client, you must first set up a virtual directory for SQL Server using the Microsoft® Management Console (MMC) snap-in provided with any of the releases.
If you want to set up a virtual directory to perform template queries, you can still use the MMC snap-in provided with the original installation of SQL Server. This can be found in the SQL Server 2000 program group under Configure SQL XML Support in IIS. However, to take advantage of SQLXML 3.0 features, I recommend selecting the MMC snap-in found in the SQLXML 3.0 program group under Configure IIS Support. Here you can configure all features up to and including those of version 3.0.
To set up a virtual directory, first you need to set up a directory structure with a main directory (I called mine projects) that has two subdirectories: template and SOAP. The template directory will contain your XML template files and will be used for all template operations (for example, file-based SQL, XPath, updategrams, and so on). The SOAP directory will contain all files required for accessing SQL Server via Web Services. If you want to experiment with mapping schemas (via the schema type) and/or direct database object access (via the dbobject type), then you may add directories for each of those as well. Follow these steps for testing your installation with a simple XML query.
- To create the template virtual directory in the MMC, select Default Web Site, then New Virtual Directory.
- On the General tab, name the virtual directory to match the database you will be accessing. I simply use Northwind (see Figure 1). This becomes the virtual directory upon which you will access any XML feature. Set this root directory to contain all templates.
Figure 1Good Ol'Northwind
- On the Security tab, select the authentication scheme you will use to access the database.
- On the Data Source tab, select your data source.
- On the Settings tab, select "Allow sql= ..." and select "Allow template queries." These two will be enough to get you going. Later you will select "Allow Post" to enable calls to SQL Server as a Web Service.
- On the Virtual Names tab (see Figure 2), select <New virtual name>, call it "template," specify the template type, and point it to the template subdirectory that should now reside under your main directory.
Figure 2Defining a New Virtual Name
- Name the template "Customers.xml" and save it under your template subdirectory. Any SQL command can be added to this file. Both updategrams and bulk loading can be used for updates or inserts from the template directory as well. Here you can see a sample XML query template for retrieving all customers from the Northwind database:
|
|