XML and SQL Server

In this post i'll cover how you can get SQL Server to return data as XML by using the FOR XML command and how you can use XML as input for updating records and as a rich argument for row returning stored procedures using the OPENXML command.

There are lots of reasons you may want to get data out of a database as XML:

• You may be building an AJAX app and want to send XML to the client directly for processing by your client-side JavaScript
• You may want to use XSL to transform your data into some format such as HTML or a CSV
• You may want to export data and store it in a form which retains its original structure

These reasons also give you reasons for needing to pass XML into your database for example with the AJAX app you may want to receive changes as XML from the client and post them straight to a stored proc that updates your tables.

Examples are in VBScript using ASP and ADO.

Getting SQL Server to return XML

The key to getting SQL Server to return XML is the FOR XML command. It comes in three flavours:

FOR XML RAW

The least useful, RAW mode simply outputs the rows returned by your query as <row> nodes with the columns being either elements within this node or attributes of it as you define.

FOR XML AUTO

Automagically translates your SQL query, joins and all into suitable nested XML elements and attributes. For example if you are joining Orders to OrderItems the XML output will be OrderItem nodes nested within the associated Order node. You can alter the naming of the nodes by aliasing your table and column names but that's about it.

FOR XML EXPLICIT

Explicit mode allows the most customisability but it's also the most fiddly requiring you to alias all your columns names to a specific format which describes which nodes they should belong to.

You'll mostly use AUTO mode because it gives you the most useful results in the least amount of time so here it is in an example:

SELECT Order.*, OrderItem.*
FROM Order
INNER JOIN OrderItem
   ON Order.order_key = OrderItem.order_fkey
WHERE Order.customer_fkey = 1
FOR XML AUTO

All you do is tag FOR XML AUTO on to the end of your query, that's it! The output will look something like this:

<Order order_key="1" customer_fkey="48" date_placed="24/08/2006 12:31">
   <OrderItem orderitem_key="123" order_fkey="1" product_fkey="234" list_price="£14" />
   <OrderItem orderitem_key="124" order_fkey="1" product_fkey="64" list_price="£3" />
   <OrderItem orderitem_key="125" order_fkey="1" product_fkey="73" list_price="£27" />
</Order>

If you run this in Query Analyzer you'll notice in the results pane it looks like the XML has been split into rows. We need to use an ADODB.Stream object to get at the output properly, thus:

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
Set strm = Server.CreateObject("ADODB.Stream")

conn.Open "Provider=SQLOLEDB;Data Source=myServerAddress;" & _
   "Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

strm.Open

Set cmd.ActiveConnection = conn

cmd.Properties("Output Stream").Value = strm
cmd.Properties("Output Encoding") = "UTF-8"
cmd.Properties("XML Root") = "Root"  'this can be anything you want

cmd.CommandType = adCmdText
cmd.CommandText = strSQL

cmd.Execute , , adExecuteStream

Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"

xmlDoc.LoadXML(strm.ReadText)

strm.Close : Set strm = Nothing
Set cmd = Nothing

xmlDoc now contains our XML to do with as we will.

Passing XML into SQL Server

The easiest way to get XML into SQL Server is as a parameter of a stored procedure thus:

cmd.Parameters.Append cmd.CreateParameter("somexml", adVarChar,
adParamInput, 8000, xmlDoc.xml)

You then use two System Stored Procedures along with the OPENXML command to SELECT from the contents of the XML parameter as if it were a table:

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @somexml

SELECT * FROM OPENXML (@idoc, '/Root/Order') WITH (Order)

EXEC sp_xml_removedocument @idoc

OPENXML takes the prepared XML document and an XPath expression telling it which nodes it is taking into account. The WITH statement in this case tells OPENXML that the nodes it is working on are of the same schema type as the rows in the Order table.

The result of this call is a list of records of the same schema as the Order table but which have actually come from the passed in XML document. Because the schema is that of Order you can put an INSERT INTO [Order] in front of the SELECT and this will add the rows from the XML to the Order table. You probably wouldn't want to do that but you get the idea.

You don't have to have a table representing the schema of the XML you're passing in in your database. WITH also accepts a normal schema declaration i.e. comma delimited column names with their types and which node this maps to in the XML:

SELECT order_key, customer_fkey, description
FROM OPENXML (@idoc, '/Root/Order') 
WITH (
   order_key     int           '@order_key',
   customer_fkey int           '@customer_fkey',
   description   nvarchar(100) 'description'
)

The advantage of being able to do this is that you can pass complex structured criteria into one of your stored procedures and use OPENXML to turn it into a rowset which you can use to JOIN to the tables in your database. Powerful stuff with a large number of applications in both improving querying data and updating it.