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.

As XML applications mature and enter their second generation, application developers will leverage the built-in extensibility of XML more frequently, adding new data elements to existing XML formats that first-generation applications use. How well first-generation applications work with this additional data depends on whether the developer considered extensibility in the application's architecture. Luckily, SQL Server 2000 has built-in functionality to account for additional data, even when you don't know what or how much additional data you might need to receive or send from your application. Let's explore this functionality by way of an example.

SQL Server 2000 and XML for SQL Server 2000 Web releases (SQLXML) provide three ways in which you can store XML data. XML Bulk Load and Updategrams, two client-side technologies, use annotated schemas to specify the mapping between the contents of an XML document and the tables in your database. OpenXML is a server-side technology that lets you define a relational view on an XML document. With OpenXML's relational view, you can use T-SQL code to query the data in the XML document and store the results in your SQL Server database.

Well, welcome to XML for SQL Server 101. I'm Professor SQLXML, and for the remainder of this column, I am going to teach a survey of the XML features provided by SQL Server 2000. As with other survey classes, the point here is not to get into any great detail on any one topic, but to give you an idea of the XML features provided by SQL Server 2000, and how they can be used to solve some common application development problems and scenarios. At the end of the column, I will also discuss where you can get more information about the XML support provided by SQL Server 2000.

Learn how SQL Server's XML features can provide your databases with new functionality.

Front-end developers frequently want to add functionality to the presentation tier of an n-tier architecture, but such requests can require changes on all tiers just to get the data and present it. This process can be made easier and more flexible by using SQL Server stored procedures to automate the delivery of data in XML format from the database to the front-end components. In the component presented here, stored procedures are invoked by XML strings, XML is returned, converted using XSL, and presented to the client in HTML. The technique supports rapid changes yet doesn't sacrifice the n-tier approach. This approach can be used with either SQL Server 7.0 or SQL Server 2000.

How to optimize SQLXML performance for databases, including SQL Server 2000.

Introduces the SQLXML tier, a combination of XML-based communication to Microsoft SQL Server through an Internet Information Services (IIS) virtual directory.

Join discussions or ask questions in the SQLXML newsgroup.

How to use SQLXML Managed Classes for .NET code to retrieve XML from a Microsoft SQL Server database or to generate XML on the client side, using ad hoc queries, stored procedures, annotated schemas with XPath, or XML templates.

You can create XML views of relational data by using the XML Schema Definition (XSD) language. These views can then be queried by using XML Path language (XPath) queries. (This is similar to creating views by using CREATE VIEW statements and then specifying SQL queries against the view.)

You can write a client application by using SOAP Toolkit 2.0 and then send SOAP requests to the server computer that is running Microsoft® SQLXML. SQLXML executes the requested stored procedure or template and returns a SOAP response to the SOAP Toolkit client as an IXMLDOMNodeList parameter.

With the Web reference mechanism in Microsoft® Visual Studio®, you can call stored procedures, user-defined functions (UDFs), and templates just as you would call any other method on an object in your application.

Web services support in Microsoft® SQLXML 3.0 exposes Microsoft SQL Server™ 2000 as a Web service that offers SQL Server functionality to the clients. You can send SOAP HTTP requests to a server that is running SQLXML 3.0 to execute stored procedures, user-defined functions (UDFs), and templates.

How to take advantage of features in SQL Server 2000 and Excel 2002 in order to distribute enterprise data to Excel 2002 via XML.