Build Data-Driven Web Services with Updated XML Support for SQL Server 2000

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梠ne 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梚t'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 1 Good 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 2 Defining 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:

<sql:query client-side-xml="0">

SELECT *

FROM Customers

FOR XML AUTO

</sql:query>

</ROOT>

Now execute the following in your browser: http://localhost/northwind/template/customers.xml. You should see the XML query results shown in Figure 3 (not fancy but functional). If so, your queries are working and now you can proceed to the more advanced features of SQLXML 3.0.

Getting Started with SQLXML Web Services

If you are already doing .NET development, then you know that building Web Services is quite simple. Through Visual Studio?.NET and the runtime's use of attributes such as WebService and WebMethod, you can quickly produce reliable Web Services. Even more advanced functionality such as passing SOAP headers or hooking SOAP requests passed into a .NET Web Service (trace extensions) becomes less daunting with .NET.

If you aren't using the .NET runtime in your environment yet (sense my bias?), a little more elbow grease may be required. You can use the SOAP Toolkit 2.0, but that requires more background in how SOAP is used to send and receive data from a Web Service. Overall, however, building a non-.NET client is very similar to working with a Web Service proxy in .NET. If you don't have .NET, or you don't want to build an entire data access/Web Services framework, SQLXML 3.0 is for you.

SQLXML 3.0 provides a Web Service middle tier in the form of an ISAPI library (sqlis3.dll). All you need to do is configure SQLXML and provide a Web Services client. With SQLXML you can now send SOAP HTTP requests to a server running SQLXML 3.0 to execute a stored procedure, XML template, or UDF directly. The requested operation is executed at the data source and a SOAP response is returned to the client. The Web Services magic, at least on the server, is all taken care of by SQLXML. Just configure the Web Service using the same MMC snap-in as I demonstrated in the previous section for templates. The only code required is on the client. This can be an ASP or ASP.NET application, a Microsoft Windows?application, a console application, or whatever. The client can be built using C#, a standard SOAP client using straight XML, or even the SOAP Toolkit 2.0. In this article I will demonstrate client development by building a simple C# client (using the Visual Studio-generated Web Services proxy) and a Visual Basic?client (using the SOAP Toolkit).

Setting Up a SQLXML Web Service

If you are following along with my sample, use these steps to configure the Web Service:

Select the Northwind virtual directory that you created in the previous section and display its properties.

Select the Settings tab and make sure Allow Post is checked so that SOAP requests can be posted from the client.

Select the Virtual Names tab and select <New virtual name> as you did to create the template type.

Select the SOAP type and give it a name. I called mine "soapprocedures." You can name it anything you want and you can have as many defined SOAP types as you like. For each defined SOAP type, SQLXML creates a corresponding configuration file (.ssc) and a Web Services Description Language (WSDL) file that are used to access the Web Service. It is important to note that these files are named after the Web Service you provided, not the SOAP type. The SOAP type's name is used to retrieve the generated WSDL file, which describes the service and the operations (stored procedures, UDFs, and templates) that a client can then request.

Select a directory to map this SOAP type. Use the SOAP directory created earlier. This is where the .ssc and WSDL files will be created. Select Save.

Finally, give your Web Service a name. I called mine "procedures." This is the name you will use from your client code to instantiate the Web Service using the proxy in .NET. Figure 4 shows the WSDL output for this Web Service. You'll notice that under IIS, your virtual directory (Northwind) will have a SOAP directory and two files: procedures.ssc and procedures.wsdl. Note that by default you can't select the WSDL file directly from the browser. You need this URI: http://localhost/northwind/SOAPprocedures?wsdl.

When the new SOAP type is selected, select Configure.

Under the Soap Virtual Name Configuration dialog, select <New method mapping>.

Select a mapping type (SP for stored procedures and user-defined functions).

Select the stored procedure using the browse button, and give it a method name. The name will be the invokable Web method you will use from the client. Keep the remaining defaults. For my example I selected two stored procedures from the Northwind database, SalesByCategory and CustOrderHist, and kept the default, which simply uses the stored procedure name.

Test the WSDL file that contains the Web methods created from your browser as you did in the section "Setting Up a SQLXML Virtual Directory." Now let's build the clients.

A SQLXML Web Services Client Using C#

The quickest way to get up and running with Web Services is to write your client using the .NET Framework. As you will see, it isn't the amount of code saved that makes .NET simpler to use. Most of you can get away without knowing the underpinnings of the SOAP protocol since the proxy generated from Visual Studio does all of the work. However, learning some of the basic elements of SOAP would be smart. For my simple example, I use C# to call the newly configured Web Service.

I created a new client application using Visual Studio .NET. The client can be any type of application. For this example, I am using a C# application for Windows. SQLXML Web Services can be called like any other Web Service. Add a Web reference from the Add Web Reference dialog type in the same URL you used to test the WSDL file (http://localhost/northwind/SOAPprocedures?wsdl). In Figure 5 the GetAllCustomers template has been called as a Web Service and its XML results used in a DataSet grid. Figure 6 shows the sample client using ASP.NET.

The WSDL output should appear in the left pane of the Visual Studio IDE. From here, you can add the reference to your project. I added the procedures.wsdl reference, allowing me to declare a variable of this Web reference type. Once declared, I treat this type like any other class type in .NET by instantiating it. After the Web Service object is created, I can invoke its operations by calling any of its exposed methods. IntelliSense?should now display each of these Web methods in the editor.

The following C# code shows how to call a stored procedure wrapped as a Web Service. I've omitted a few details that I will explain shortly. You can see that calling a Web Service at this point is very similar to calling into any other object type:

localhost.procedures oWSProcs = new localhost.procedures();

int nReturnValue;

晻?

= oWSProcs.CustOrderHist("ALFKI", out nReturnValue);

You'll notice that this call differs from standard calls to Web Services in the return values. When using SQLXML Web Services, the data returned from the Web method takes the form of an object array, which must then be cast into a workable type like XMLElement or SqlMessage.

XMLElement objects include the result that is successfully returned by SQLXML after executing any operations (stored procedure, template, or UDF). In the WSDL file this is defined as having a SqlXML complex type. Error messages returned from SQLXML are of type SqlMessage. If SQL Server returns one or more errors, this SqlMessage complex type is returned as part of the object array and is also defined in the WSDL file. (More on this later.)

The System.XML.XMLElement complex type maps directly into an XML node class type from the .NET class library. If you have worked with .NET and XML you should already be familiar with this stock type. SqlMessage is a custom type specific to SQLXML and contains any error messages generated during transport. To make sense of the returned object array from a SQLXML Web Service, I created the XMLElement method. In Figure 7 you can see how the object array is handled.

This method takes any returned object array and either returns an array of XMLElement types or throws an exception, filling in the values from the SqlMessage type. To determine if the object array contains an error or XML instance data, the type is determined by using GetType and the value is cast appropriately. XMLElements are simply returned to the caller. Figure 8 shows the calling code in its entirety. (This is slightly different from this article's downloadable code for clarity.)

I have not yet mentioned the System.Data.DataSet type. Just because data is being transported via XML, SOAP, and ultimately SQLXML doesn't mean you cannot use DataSets to your advantage. DataSets are terrific at providing the perfect data container, not to mention being handy for purposes such as displaying data in a grid.

It's easy to return XML instance data from a stored procedure (callable from a SQLXML Web Service) and turn it into an XML schema-based DataSet, ready to be consumed as you please. To perform this conversion I created a method called GetDataSetFromXMLFragment which takes any XML fragment, infers an XML schema, and hydrates its data. The managed SQLXML classes can also be used in similar fashion.

The following code shows how the System.XML.XMLReader and the DataSet's ReadXML work together to fill a DataSet:

public static DataSet GetDataSetFromXmlFragment(XmlElement oXml)

{

DataSet ds = new DataSet();

XmlTextReader oReader = new XmlTextReader(oXml.OuterXml,

XmlNodeType.Element, new XmlParserContext(null, null, null,

XmlSpace.None));

// now lets create a schema off of the instance data

ds.ReadXml(oReader, XmlReadMode.InferSchema);

return ds;

}

Don't forget, value types such as integer and float cannot be passed or returned as a null value when using the proxy classes that are generated by Visual Studio .NET. To do so, you must create your own Web Service proxy class (which is not recommended). Reference types and string types can be null.

Calling Templates and UDFs as Web Services

Along with stored procedures, SQLXML also allows Web Services to call XML templates and UDFs. Configuring these types is not very different from working with stored procedures. The configuration process establishes the necessary mapping in a WSDL file as before. Once configured, the mapping is used to execute the corresponding template or UDF just like you do with stored procedures. If you want to configure a template to use with my sample, complete the following steps:

Go to the properties dialog of the Northwind virtual directory.

On the Virtual Names tab, select the soapprocedures SOAP type created earlier and select Configure.

Select template as the Edit/New mapping type.

Select the browse button. From there you can find any previously built XML template.

Select the customers.xml template that you have used already to test the installation of SQLXML 3.0 and call it GetAllCustomers.

That's it. You can now call GetAllCustomers as a Web Service just like you'd call the stored procedures. GetAllCustomers will return all of the records from the Customers table as XML, but instead of using a browser I can now capture this in code. I believe this is where this release really shines. Those of you who have invoked templates in code via HTTP or through one of the OLE DB providers as I discussed in my article "BizTalk and XML: Add E-Commerce to Your App with XML and SQL Server 2000," (MSDN Magazine January 2002) will now appreciate the simplicity of uniformly invoking all operations as Web Services.

Invoking a UDF is no different. You can build the UDF shown in Figure 9 by following the same steps just outlined and selecting SP as the Edit/New mapping type as you did when configuring a callable stored procedure. All UDFs and stored procedures should appear in the browse dialog. Make sure you update your Web reference from Visual Studio .NET. (IntelliSense will tell you when it is there, or you can look at the generated WSDL.)

Using the SOAP Toolkit 2.0

Many of you may not yet have the option of using .NET technology in your development environment. If that's the case, you can invoke any SQLXML feature using plain old Visual Basic?6.0. The only additional component required prior to running the following sample code is the SOAP Toolkit 2.0. I invoke the exact same operations I created here already except I will do it from Visual Basic 6.0. Familiarity with the MSXML Document Object Model (DOM) would be helpful, but it's not required. The only two interfaces that are required are the IXMLDOMNodeList and IXMLDOMNode interfaces from MSXML 4.0.

Figure 10 looks amazingly similar to the C# sample. The major difference here is that I am doing this from a Visual Basic 6.0-based client and I am using the soapclient component from the Soap Toolkit 2.0 for the proxy. Soapclient is used exactly like the generated proxy from Visual Studio .NET. Instead of binding the return values from an object array to a data type, you will always be using an IXMLDomNodeList from MSXML 4.0 to iterate through each returned IXMLDOMNode. Here you are simply working with the MSXML Node interfaces. The output from running this code is not quite as neat as you saw in the .NET example. It could be much improved with a little XSL. I'll leave the rest up to you.

Conclusion

In this article I introduced SQLXML 3.0 and its most powerful application: Web Services using SOAP. For environments not ready for .NET, or those of you without the inclination to build a custom middle tier, SQLXML 3.0 provides a simple yet effective way to access SQL Server over the wire. Hierarchical data in the form of XML has become the data format of choice among developers. XML and SOAP will give you an advantage in the loosely coupled world of Web Services. To download the latest Web release (SQLXML Version 3.0) or to find more information on the new features offered in the XML for SQL Server Web Releases, see http://msdn.microsoft.com/xml.