XML and Web Services for Microsoft Developers - Part 1

XML and Databases

The primary problem with HTML is that it combines data with presentation. On the other hand, XML is all about data. XML has become the de facto format for portable data. One of the primary sources of data is still the relational databases. Keeping these facts in mind, Microsoft introduced support for XML in their relational DBMS, SQL Server 2000.

SQL Server 2000 allows relational data to be retrieved as XML, and XML to be directly imported into relational database.

The FOR XML clause was introduced to be used with the standard SELECT Transact SQL statement. This clause allows the retrieval of relational data as XML. Try out the following example:

Start SQL Server Query Analyzer tool, select the Northwind sample database and execute the following query:

SELECT * FROM [Customers] FOR XML AUTO

Instead of returning the standard relational rowset, you'll notice that the data is returned as XML nodes.

To complement the FOR XML clause, the OPENXML function was introduced. This allows XML data to be used as a relational rowset, which can be SELECTed, INSERTed, or used for the relational UPDATE statement. There are three steps involved in using OPENXML. The first is to load the XML document and get the document handle. Then use OPENXML to convert the XML document into a relational rowset. And finally, free the XML document handle. Two system stored procedures, sp_xml_removedocument and sp_xml_preparedocument are used to work with the handles.

DECLARE @idoc int    
   
DECLARE @doc varchar (1000)    
   
SET @doc ='    
<ROOT>    
<ShipperRec Company="ABC Shippers" Ph="(503) 555-9191" />    
</ROOT>'    
   
--Create an internal representation of the XML document    
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc    
   
-- Execute a SELECT statement that uses the OPENXML rowset provider    
SELECT *    
FROM OPENXML (@idoc, '/ROOT/ShipperRec',1)    
WITH (Company varchar(80),    
Ph varchar(48)    
)    
   
-- Clear the XML document from memory    
EXEC sp_xml_removedocument @idoc

Run the above commands in Query Analyzer, and you should see a record in the output window, with column names and data values from the XML document defined by the @doc variable above.

SQL Server 2000 also introduced ability to access relational data as XML over HTTP. A tool known as Configure SQL XML Support in IIS was added that allows configuring IIS virtual directories that map to relational database. This virtual directory then can be used to access the database over HTTP.

See SQL Server 2000 Books Online (Start | Programs | Microsoft SQL Server | Books Online) for more details on native XML support in SQL Server 2000.

To keep up with the fast-evolving XML standards, and to enhance the XML support in SQL Server 2000, Microsoft followed the Web release model (like with MSXML) and frequently releases the SQLXML extension via the MSDN Website. The current SQLXML 3.0 release supports ability to update the database over HTTP using XML (Updategrams), XML Bulk Import, exposing SQL Server stored procedure and functions as Web service methods, and more. Visit http://msdn.microsoft.com/sqlxml for more details on this.