by mgordon
25. June 2007 04:41
OK, I admit it. When the Xml support first started showing up in Sql Server, I was a skeptic. I wondered why anyone would want to do all that parsing and string manipulation on the database tier. It made me wince to think about it. So now, a few years later, and I'm wondering what I ever did without it. Especially since the Xml data type has been added to Sql Server 2005.
As an example of its usefulness, consider a recent situation I found myself in. I had a collection of objects that represented changed that needed to be made to the state in the database. I could have iterated through the collection and called a stored procedure for each object and applied the change sets one at a time, but I tend to try to make any call across the network as chunky as possible and thought how nice it would be if I could send the entire collection of changes to the database at once. It's entirely possible to do just that with the Xml data type.
First, I serialized each of the objects into Xml and gathered the individually object's state into a single Xml document. As an illustration, let's suppose the Xml looked like this.
<occs>
<occ>
<service_order_id>1</service_order_id>
<otc_id>2</otc_id>
<amount>100.00</amount>
</occ>
<occ>
<service_order_id>3</service_order_id>
<otc_id>4</otc_id>
<amount>200.00</amount>
</occ>
</occs>
I can pass this xml into a stored procedure as a string where the proc takes a single parameter of type Xml.
CREATE PROCEDURE [dbo].[MySp]
@OtcXml Xml
Inside the stored procedure I can parse through the Xml and place the data it contains into a temporary table that I can use any way I like.
SELECT ItemData.row.value('Service_Order_Id[1]', 'int') as Service_Order_id,
ItemData.row.value('OTC_ID[1]', 'int') as OTC_ID,
ItemData.row.value('Amount[1]', 'money') as amount
INTO #OCCData
FROM @OtcXml.nodes('/occs/occ') ItemData(row)
Starting with the FROM clause, I've basically specified an Xpath expression to the level within the XmlDocument which corresponds with the level at which my "rows" are defined and I'm saying that each element returned by the Xpath expression is to be treated as a row and each element below that level should be treated as a column in that row. In the select statement, I'm specifying each column by telling Sql Server the name of the element within the row I want the value from and I'm specifying the data type to cast the data as.
As I said before, I'm pulling the data out of the Xml into a temporary table so that I can run set-based Sql against the data to perform the updates I needed.
Subscribe