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

首页 | 图形图象 | 编程开发 | 操作系统 | 数据库 | 服务器 | 网络安全 | 人才酷 | 招聘 | 资源 | 酷站 | 顶尖周刊 |

热门文章

编辑推荐

相关文章
用MailSpy拦截局域网内危险的病毒让Ghostbusters为电脑保驾护航破译E-mail账号的技巧破解Email账号三种方法教你强力破解E-mail密码的三种方 Linux/Unix环境下的make和makefi 使用netfilter/iptables构建防火透明防火墙架设的完全攻略（brid 带SMTP认证功能的qmail配置 qmail+vpopmail+sqwebmail的安装

您现在的位置：顶尖设计 >> IT学院 >> 数据库 >> MS SQL >> 文章正文

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

作者：佚名来源：不详点击：更新：2006-12-20

简介：

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 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:
```
   <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <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

[1] [2] [3] 下一页