SQL Server Magazine

PLEASE NOTE: The authors based their SQL Server 2000 articles for this issue on pre-Beta 2 versions, so you might notice some differences between Beta 2 and the behavior or interfaces we describe in this issue. In particular, please note that the indexed views feature will be available only in SQL Server 2000 Enterprise Edition. However, you can install Enterprise Edition on NT 4 Server and Windows 2000 (Win2K) Server. You don't need NT 4.0 Enterprise or Win2K Advanced Server.

One of the most eagerly awaited features in SQL Server 2000, support for XML, is also one of the most nebulous in terms of immediate, practical value. Everyone's heard the hype about a language to bridge all languages, and nearly every relational database management system (RDBMS) now claims XML support. But when and where do you use XML and why?

XML, an Internet standard for information exchange, lets you publish data types in a platform-independent manner, facilitating interoperability and e-commerce. XML also separates data from presentation information within Web pages, giving you a standard way to define and exchange data between applications and databases. (The sidebar "XML—The Current Big Thing," page 42, explores the business benefits of using XML to separate data from its presentation.)

As a language for defining markup languages, XML's primary value will come from either the widespread acceptance of a particular language defined in XML or the widespread acceptance of XML and the availability of utilities, tools, and infrastructure to support its use. Although XML has several excellent defined languages—such as BizTalk, Directory Services Markup Language (DSML), and Simple Object Access Protocol (SOAP)—it isn't for everyone, especially if you're in a pure Microsoft shop developing 32-bit Windows applications. For data transfer across a LAN, ADO result sets are the obvious choice. However, in this Internet age, few organizations are an island unto themselves. And even within organizations, a pure environment of any one server, platform, or language is rare.

Although SQL Server 2000 is the first SQL Server version to feature XML support, Microsoft's XML technology preview runs under versions 7.0 and 6.5. (You can download the technology preview from Microsoft's SQL Server Web site at http://msdn .microsoft.com/workshop/xml/articles/xmlsql/.)You can also provide XML support in SQL Server 7.0, 6.x, and 4.2 by writing extended stored procedures and standard stored procedures, although standard stored procedures can drag down performance for large data sets with a complex structure. In addition, certain SQL Server 7.0 functions, such as full-text search, let you store XML as text. So what features make SQL Server 2000 officially XML-enabled?

Traditionally, you can request two kinds of XML from a database: static XML stored within the database and dynamic XML generated from data within the database. Even the first version of SQL Server could serve as an XML repository, and developers have used pre-SQL Server 2000 versions to create applications that generate XML. But SQL Server 2000 supports an XML data type, letting you store XML natively in the database. Having a native XML data type is just one of the criteria that makes SQL Server 2000 XML-enabled. SQL Server 2000 also lets you use

• URLs to access SQL Server through HTTP
• the FOR XML clause in the SELECT statement to retrieve results in XML format
• system stored procedures for manipulating XML data
• update grams for batch operations
• SQL Server OLE DB provider extensions for XML

Let's look at SQL Server 2000's XML enhancements and an example of how you can XML-enable your existing systems by extracting data from SQL Server 2000 as XML, formatting the data the way you want, then using that data in your applications.

Accessing SQL Server Through HTTP
With SQL Server 2000, you can enter an SQL statement into a URL in Microsoft Internet Explorer (IE) 5.0 and retrieve records in XML format. The following pseudo URL query shows the query's various components:

http:///

?sql=+FOR+XML+RAW

First, the query uses the HTTP protocol, giving you a large client base for any system you might produce. The query then directs its request to the Web server, Microsoft Internet Information Server (IIS), through a virtual root on the IIS server that you must configure to use the SQL Server XML extensions.

Next, the query requests the data you want. When requesting data from a Web server, you typically use an HTML FORM with one of two standard HTML FORM methods: GET or POST. GET passes FORM data from the client to the Web server as name/value pairs appended to the URL. However, this mechanism has several well-known drawbacks, including a limited URL length and limited security. POST, the preferred method in application development, passes FORM data as name/value pairs stored inside the body of the HTTP request. This article's examples use the GET method to send queries to the database so that you can see the data request.

You can also use SQL statements or stored procedures to access data in XML format. Or you can run an XML template, an XML document that contains one or more valid queries, statements, or update grams. Templates follow the form

sql statement(s)

You can invoke templates by including them in the GET or POST request or by calling them as files saved in the Web server's virtual directory.

If your SQL queries are long, IE can't display them in its location bar and you'll have a hard time typing them again and again during testing. For these and other reasons, you can store template queries in a file in the virtual directory and reference them as follows:

http://auril/Northwind/myquery.xml

   Prev. page   [1] 2 3     next page