When data needs to be exchanged between distributed systems, transformed to target multiple devices, or converted into other data structures, XML provides a very flexible and extensible medium. Accessing relational data as XML can be useful in a variety of situations.

This article discusses the XML support built into SQL Server 2005. It shows how this support integrates with the client side programming support in the .NET Framework V2.0 and in native code such as OLEDB and SQLXML.

Discover the many different ways SQL Server 2000 supports XML with a comprehensive look at both out-of-the-box support and the SQLXML 3.0 add-on.

ASP.NET provides powerful capabilities for engineering highly scalable web portals for businesses. The newly released SQLXML 3.0 enables convenient access to data from SQL Server 2000 databases with the data delivered as XML. XSL files can be applied to transform the delivered XML into dynamically-generated web pages and portions of web pages contained within <div> elements, or the XML can be delivered to an ASP.NET XML control.

Microsoft's SQLXML 3.0 includes many different aspects, including Managed Classes, capability for accessing SQL Server 2000 using IIS virtual directories, and support for templates, updategrams, and diffgrams. This article focuses on the advanced SQLXML features, and shows how to develop an ASP.NET application that uses SQLXML to access and modify SQL Server tables using virtual directories, templates, and updategrams.

SQLXML allows developers to bridge the gap between XML and relational data. Using SQLXML you can create XML View of your existing relational data and work with it as if it was an XML file. In comparison with SQL Server 2000's native XML, the SQLXML 3.0 has many features (such as XML bulk load, updategrams, XSD mapping schemas and so on) and provides better performance.

SQL Server is an XML-enabled DBMS, which can read and write XML data, return data from databases in XML format, and read and update data stored in XML documents. In fact, SQL Server has eight different ways to use XML. Learn how each of them works, and how they interoperate.

With XML support in SQL Server 2000, you can query SQL over HTTP with a URL, bring the data down to the browser, and manipulate it on the client machine. By adding Internet Explorer 5.0 to the mix and using XSL to convert the XML to HTML, you can lighten the load on your database server. Going still one step further, by using Vector Markup Language you can even create drawings on the fly using the data from your SQL queries. This article illustrates this combination of technologies by leading you through the creation of a Web app that queries a digitized street map database that's been imported into a SQL Server database, sorts and displays the data using XML, and draws maps using VML.

Using XML for data access allows you to separate the data from the presentation, and promotes reuse, extensibility, and division of labor. XML also has a simplified data model, which promotes easier testing. This article presents and compares five data access approaches, using a variety of technologies including ASP and ADO, XSLT, and DirectXML. Once built, the solutions are compared on the basis of their speed and efficiency.

Microsoft has recently started embracing Extensible Markup Language (XML), but embracing XML is different from putting it natively into a product. SQL Server administrators and database developers haven't missed full integration of XML in SQL Server, but they've now come to realize that they needed XML all along. In the next version of SQL Server, XML output will be a standard option, letting you create a channel of information-including information pulled directly from the SQL Server-that you can pass to other systems. If you combine XML and Data Transformation Services (DTS), bulk copy program (bcp) will continue to slip out of use, to the delight of most database developers and administrators.

XML is fast becoming the lingua franca of the new technology world the universal markup language you can use to represent any other language running on any platform. Although SQL Server 2000 is the first SQL Server release to provide integrated XML support, Microsoft's XML technology preview, available at http://msdn.microsoft.com/downloads/default.asp?URL=/code/topic.asp?URL=/msdn-files/028/000/072/topic.xml, runs under any SQL Server release. The technology preview, refined through 11 revisions, is robust, and many SQL Server installations are using it to integrate XML and SQL Server.

With the release of SQL Server 2000, Microsoft has started to make good on its new mission statement: to provide access to data and applications "anywhere, anytime, and on any device." At the center of this vision is support for XML, which Microsoft integrated into SQL Server 2000. XML's self-describing nature and cross-platform capabilities give SQL Server the ability to easily exchange data with Web applications and other systems. Paul Burke's "XML and SQL Server 2000," May 2000, and Bob Beauchemin's "The XML Files," September 2000, gave you overviews of SQL Server 2000's XML features. Now, let's dig into how OLE DB and ADO 2.6 support three key XML features: retrieving XML from stored procedures, executing templates, and executing XPath queries against XML views.

The IT community's burgeoning interest in anything XML has spilled over to SQL Server 2000, which ships with XML capability. Developers are using SQL Server 2000's XML features to write queries that use the FOR XML clause to return results in XML format, to query SQL Server through HTTP, and to program in XML's XPath query language. (For an overview of SQL Server 2000's XML features, see Bob Beauchemin, "The XML Files," September 2000.) In this article, I show you how to engage one of these XML features—SQL Server 2000's FOR XML clause—to produce a well-formed XML document that another system can easily read and process. The FOR XML clause, which you use in a T-SQL SELECT statement, lets you write a SQL query that returns XML directly from the SQL Server query engine. Without the FOR XML clause, you must parse the query results through an XML parser or generate XML in a custom application.

Microsoft's XML for SQL Server 2000 Web Release 2 (WR2) Beta 2 at http://www.microsoft.com/sql/downloads/default.asp can help you solve your problem. An important feature of WR2 is its support for XML schemas through the XML Schema Definition (XSD) language. Let's look at an example that uses an XML Data Reduced (XDR) schema, Microsoft's original XML-based schema language, to query SQL Server 2000, then modify the example so that it uses XSD instead.

SQL Server 2000 is an XML-enabled database server. If you set up your Microsoft IIS system to take advantage of SQL Server 2000's XML capabilities, you can use a browser to access database records—without writing a single line of ADO code.

I have an XML document generated from a Recordset through the ADO Save method. I want to use XML Bulk Load to upload the data in the XML document into another database, but the upload fails when the XML contains date fields. How can I use XML Bulk Load to load ADO-generated date fields?

If you're writing an application that uses XML as a data-interchange format, you need a way to extract the data from the XML document and store that data in your database. T-SQL's OpenXML keyword is a convenient way to insert, delete, and update data in SQL Server 2000. OpenXML creates one or more relational views (or rowsets) of the XML document within a T-SQL stored procedure. You can access the data in those views to perform relational operations on your database. Although OpenXML is fairly easy to work with, you need to overcome a few hurdles before you can use it in real-world applications. I've discussed the largest stumbling block—the difficulty in passing an XML document to a stored procedure—in my Exploring XML columns in July 2001, InstantDoc ID 21077, and August 2001, InstantDoc ID 21259. Now let's look at two other common OpenXML obstacles you might encounter.

If you need to extract data from your SQL Server database in a specific XML vocabulary, XML views are a good technology to choose. By XML vocabulary, I mean XML that corresponds to an agreed-upon formatting of the elements and attributes that make up an XML document. You define the vocabulary by building an XML Schema Definition (XSD) schema or using a prebuilt schema. You construct XML views by adding annotations (additional elements and attributes that locate data within your relational database) to any standard XSD schema. The annotations define the mapping between the XML and your relational database's schema. You can think of the resulting XML view as a virtual XML document that contains the data stored in your database. SQL Server 2000 and SQL Server 2000 Web Release 3 (SQLXML) support a variety of annotations for mapping data stored in SQL Server into an instance of an XML document that the schema describes.

XML views define an XML-centric view of a subset of the data stored in your relational database. You define an XML view by adding annotations to an XML schema to form a mapping schema. In "Filtering Values in XML Views," November 2002, InstantDoc ID 26715, and "Defining XML Views," December 2002, InstantDoc ID 27106, I showed you how to use several annotations to map data from the rows and columns of database tables to a virtual XML document that your mapping schema defines. In this column, I show you how to use XML's built-in support for ID and IDREF attributes, which function like keys and foreign keys in your database, and IDREFS, which lets you specify one-to-many (1:M) relationships.