.NET & OPENXML

Besides returning results in XML format SQL Server 2000 also can insert data directly from an XML document – well almost. Actually we are still issuing an INSERT statement, but we select the data to insert directly from an XML document. SQL Server 2000 parses the XML document with a new system stored procedure, sp_xml_preparedocument. After we called that stored procedure, we can create a rowset view of the document with the OPENXML statement and then SELECT data from the rowset. Finally we need to clean up the server memory after we finished accessing the XML data. SQL Server 2000 provides another stored procedure sp_xml_removedocument we need to call let let SQL Server know when it can release all resources help to provide access to the XML document.

Does that sound too complicated? It’s really not. Let’s look at a concrete example and see how it works. Imagine you need to add a new customer record to your system. Currently you have the customer data in the following XML document:

<Customer>

  <CustomerID>NEWCUS1</CustomerID>

  <ContactName>Joe H. Buyer</ContactName>

</Customer>

To access the data in that document you have to follow the steps we outlined above: prepare the document and open it as a rowset:

-- @doc holds the XML document to process

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT CustomerID, ContactName

FROM OPENXML (@idoc, '/Customer', 2)

      WITH (CustomerID  varchar(10),

            ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

First we call the stored procedure to obtain a handle to the parsed XML document. The only thing we can do with this handle is pass it to the OPENXML statement to open a rowset like view on the data in the XML document.

NOTE: sp_xml_preparedocument requires well-formed XML If we need to insert multiple records we have to group them under a common parent element.

The next parameter of the OPENXML statement is an XPath expression to tell SQL Server which part of the XML document we would like to view. The third parameter specifies if the rowset is made up from the attributes of the element(s) we selected, the immediate child elements or both. Table 15.5 shows the possible values and explains their effect on the results of the query in more detail. The final piece of the anatomy of an OPENXML statement is the WITH clause. This one is particularly interesting because it controls the columns of the returned rowset. Only columns listed in the WITH clause are part of the returned rowset. The third parameter of OPENXML specifies the default mappings available to the WITH clause. Table 15.5 lists the possible values.

1.5            Conversion options of the OPENXML statement. The option specifies which XML nodes OPENXML maps by default.

If we omit the WITH clause from the statement then the returned rowset will contain meta information like the node’s local name, the namespace prefix and the namespace uri, about the matching nodes. The returned rowset format is called an “edge table”. By default each row maps to a mapped attribute or element with the same name, but we can override the default by adding an XPath expression to map a column to any other attribute or element throughout the XML document or to the meta properties from the edge table.

 The SQL Server Books online collection installed with SQL Server contains all the details about the edge table. You can read everything else there. We move on to use OPENXML to insert data into SQL Server tables. After all, this section is titled “Inserting Data with OPENXML” and we have not inserted anything so far.

Actually, doing the INSERT is a piece of cake – now that we know how to access the data in the XML document. We simply need to select the columns we want to insert from a rowset we obtain from a SELECT … OPENXML query. To insert the customer data into a Customers table we would add an INSERT command like shown below:

INSERT Customers ( CustomerID, ContactName )

SELECT CustomerID, ContactName

FROM OPENXML (@idoc, '/Customer', 2)

      WITH (CustomerID  varchar(10),

            ContactName varchar(20))

This concludes our overview of the original XML feature set of SQL Server 2000. Next we’ll go over the features that have been added since then with several web releases.

SQLXML Web Release

Since the initial release of SQL Server 2000, Microsoft released several XML feature packs – known as SQLXML – over the internet. These feature packs extend SQL Server’s XML support and offer seamless integration of the XML features with the .NET Framework. These additional features include:

• Client-side XML formatting to offload processing cycles from the database server. When you issue a FOR XML clause to SQL Server it must process the results and format them according to the FOR XML clause. This takes valuable processor time away from SQL Server. SQLXML has the ability to return the results to the client and format the XML client side thus reducing server load.
• Creating XML views of relational data using annotated XSD schemas instead of XDR schemas. The schema annotations define in detail how SQL Server tables map to the schema’s XML data types.
• Support for XML-based syntax to insert, update and delete data from the database. The syntax is called Updategram
• Bulk loading of XML data from an XML document and an annotated XSD describing how the XML format maps to tables and columns in the database.
• A SQLXMLOLEDB OLEDB provider to support features like client-side formatting.
• Managed classes to seamlessly integrate SQLXML with pluggable XML architecture of the .NET Framework.
• Integration with ADO.NET via the direct execution of DiffGrams to insert, update and delete data from the database.
• Web Services support which allows a SQL Server to be exposed as a web service. SOAP messages can be sent and processed on SQL Server and returned as SOAP messages. Also a WSDL service definition is provided to help with the automatic generation of proxy classes to call procedures and user defined functions on the SQL Server.

Again, we try to stay with the focus of this book and only discuss the features that immediately help us to develop an XML enabled application using the .NET Framework. You can check out other features like the Web Service support, XML templates and bulk loading in the SQLXML documentation or web sites dedicated to SQLXML on the internet. The following three subsections introduce the different concepts to access SQL Server with SQLXML. After that, in section 15.2.4 we will specifically look at the managed classes we can call from our .NET applications to interact with SQLXML and SQL Server. Feel free to skip ahead if you are already familiar with Mapping Schemas and Updategrams and you want to find out how to integrate SQLXML into your .NET applications.