Part 3 – XQuery: Please don’t use OpenXML!

Introduction

This is the third post in a series of five on Using TVP’s and XML to Transport Relational Data.

In this post we’ll take a look at how to use XQuery within Transact-SQL to shred XML data. We’ll also look at how .Net Developers can use XQuery to reduce the number of SQL Server calls required to handle data modifications.

For details of other posts in this series, please refer to Using TVP’s and XML to Transport Relational Data.

Brief History

XQuery was first introduced in SQL Server 2005 along with the XML Data Type.  Microsoft’s implementation of XQuery was based on the existing XPath query language, with support added for better iteration, better sorting results, and the ability to construct XML.

In SQL Server 2005 there were four XQuery methods (called accessor methods) that could be used with the XML Data Type:

query(): Returns XML data type output

query ('XQuery')

exists(): Returns bit and is used to check based on an expression.

exist (XQuery)

value(): Returns single value from XML, cast to SQL type 

value (XQuery, SQLType)

nodes(): Decomposes XML to a rowset – similar to OpenXML

nodes (XQuery) as Table(Column)

Microsoft added a fifth method (called a Mutator Method) with the release of SQL Server 2008: 

modify(): Performs a modification of XML instance.

modify (insert….) – inserts a single node
modify (replace value of….) – updates a single element/attribute value
modify (delete….) – deletes all nodes that match expression

(Please refer to http://msdn.microsoft.com/en-us/library/ms190798.aspx for full syntax detail and argument options)

In this post I will be using the value() and nodes() methods as all I’m interested in at this stage is demonstrating the basics or shredding XML and storing it in a relational database.

Generating XML with .Net

In Part 2 – OpenXML: What is it and how do I use it? I used an ADO.NET Dataset (dsPassenger) to generate an XML representation of data and then used the OpenXML Rowset Provider to shred this XML into a rowset.  I will be using the exact same XML in this example as it highlights how easy it is to switch between the two technologies, 

Now that I’ve got my XML, I can start work on creating a stored procedure to process the XML (We’ll come back to the .Net side of things a bit later on).

DECLARE @xml XML = '
<dsPassengers>
  <Passenger>
    <Newsletter>false</Newsletter>
    <PersonID>7a7c18a0-ad47-e011-9c71-00155d991421</PersonID>
    <FirstName>Jeff</FirstName>
    <LastName>Wharton</LastName>
  </Passenger>
  <PAX>
    <PAXID>868b8fde-068a-4468-b9b6-9660166e630d</PAXID>
    <FirstName>Lily</FirstName>
    <LastName>Wharton</LastName>
    <PaxOrder>3</PaxOrder>
  </PAX>
  <PAX>
    <PAXID>bb2770e0-b0e0-479f-8c03-1330ce87cb64</PAXID>
    <FirstName>Ben</FirstName>
    <LastName>Wharton</LastName>
    <PaxOrder>2</PaxOrder>
  </PAX>
</dsPassengers>'

Now I need to write the code that takes the XML in my variable and uses the nodes() and value() methods to shred my XML into relational data:

SELECT P.value('PersonID[1]', 'VARCHAR(50)') PersonID,
	P.value('FirstName[1]', 'VARCHAR(20)') FirstName,
	P.value('LastName[1]', 'VARCHAR(20)') LastName,
	P.value('Newsletter[1]', 'BIT') Newsletter             
FROM @xml.nodes('/dsPassengers/Passenger') AS Passenger(P)

SELECT X.value('PAXID[1]', 'VARCHAR(50)') PAXID,
	X.value('FirstName[1]', 'VARCHAR(20)') FirstName,
	X.value('LastName[1]', 'VARCHAR(20)') LastName,
	X.value('PaxOrder[1]', 'INT') PaxOrder,
	P.value('PersonID[1]', 'VARCHAR(50)') PersonID               
FROM @xml.nodes('/dsPassengers/PAX') AS PAX(X)
CROSS APPLY @xml.nodes('/dsPassengers/Passenger') as Passenger(P) 

That’s it. If I execute the above code, it will return the following:

Because XQuery has turned our XML data into a rowset, I can use the results in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear. For example:

SELECT CruiseCode             
FROM @xml.nodes('/dsPassengers/Passenger') AS Passenger(P)
INNER JOIN Booking.Actual BA ON 
	P.value('PersonID[1]', 'VARCHAR(50)') = BA.PersonID

UPDATE Person.Detail
SET FirstName = P.value('FirstName[1]', 'VARCHAR(20)'),
	LastName = P.value('LastName[1]', 'VARCHAR(20)'),
	Newsletter = P.value('Newsletter[1]', 'BIT')             
FROM @xml.nodes('/dsPassengers/Passenger') AS Passenger(P)
WHERE Person.Detail.PersonID = 
	P.value('PersonID[1]', 'VARCHAR(50)')

Namespaces

In Part 2 – OpenXML: What is it and how do I use it? I discussed what affect the Namespace property of an ADO.NET Dataset has on shredding XML with the OpenXML Rowset Provider.  Well, it has a similar affect on XQuery.

Here is my XML with a Namespace

<dsPassengers xmlns="http://tempuri.org/dsPassengers.xsd">
  <Passenger>
    <Newsletter>false</Newsletter>
    <PersonID>7a7c18a0-ad47-e011-9c71-00155d991421</PersonID>
    <FirstName>Jeff</FirstName>
    <LastName>Wharton</LastName>
  </Passenger>
</dsPassengers>

I now need to account for this Namespace when I code my XQuery statement

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/dsPassengers.xsd')
SELECT P.value('(PersonID)[1]', 'VARCHAR(50)') PersonID,
	P.value('FirstName[1]', 'VARCHAR(20)') FirstName,
	P.value('LastName[1]', 'VARCHAR(20)') LastName,
	P.value('Newsletter[1]', 'BIT') Newsletter             
FROM @PassengerDetail.nodes('dsPassengers/Passenger') AS Passenger(P)

Nowhere near as many changes needed for XQuery compared to OpenXML.

Stored Procedure

So now that I know my code is working and I’ve removed all the ‘”Namespace” changes, let’s turn it into a Stored Procedure.

CREATE PROC [Person].[usp_UpdatePassengerDetailXQuery]
(           
	@PassengerDetail XML
)
AS

SET NOCOUNT ON 

UPDATE Person.Detail
SET FirstName = P.value('FirstName[1]', 'VARCHAR(20)'),
	LastName = P.value('LastName[1]', 'VARCHAR(20)'),
	Newsletter = P.value('Newsletter[1]', 'BIT')             
FROM @PassengerDetail.nodes('/dsPassengers/Passenger') AS Passenger(P)
WHERE Person.Detail.PersonID = 
	P.value('PersonID[1]', 'VARCHAR(50)') 

UPDATE Person.PAX
SET FirstName = X.value('FirstName[1]', 'VARCHAR(20)'),
	LastName = X.value('LastName[1]', 'VARCHAR(20)'),
	PaxOrder = X.value('PaxOrder[1]', 'INT'),
	PersonID = P.value('PersonID[1]', 'VARCHAR(50)')                
FROM @PassengerDetail.nodes('/dsPassengers/PAX') AS PAXT(X)
CROSS APPLY @PassengerDetail.nodes('/dsPassengers/Passenger') as Passenger(P) 
WHERE Person.PAX.PAXID = X.value('PAXID[1]', 'VARCHAR(50)') 

IF @@ERROR <> 0 
    -- Return 99 to the calling program to indicate failure.
    RETURN 99
ELSE
    -- Return 0 to the calling program to indicate success.
    RETURN 0

SET NOCOUNT OFF

I now have a stored procedure which accepts 1 parameter of type XML so let’s take a quick look at how I call it from a .Net application.

Putting It All Together

The .Net code I need to use to call my stored procedure is exactly the same as discussed in Part 2 – OpenXML: What is it and how do I use it?.  All I need to do is change the stored procedure name from [Person].[usp_UpdatePassengerDetail] to [Person].[usp_UpdatePassengerDetailXQuery]

Conclusion

As you can see from the above example, using XML to send multiple rows of data to SQL Server is very simple to implement. That said, there are a number of tips and tricks that can be used to limit the impact shredding XML data has on SQL Server and I’ll discuss these in Part 5 – OpenXML and XQuery Optimisation Tips.

If you want to learn more about XQuery, have a look at this series XQuery Labs – A Collection of XQuery Sample Scripts