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).

Using XQuery
 
The first step is to create a variable in SQL Server Query Analyser to hold our XML:
 
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:

image

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)')

image_thumb2

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

Advertisements

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff also holds MCPD, MCITP, MCDBA, MCSD and MCSE certifications.

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

  1. Ken Hull says:

    Thanks for a very helpful article.
    Are you able to tell me how to sort out 1 to 1 and 1 2 many relationship?
    As an example of what I mean here is a sample that show that each detail line is repeated for each error description. Note that the ErrorMessage node only occurs if there is actually an error with the line.

    DECLARE @xml XML
    SET @xml =

    M110517-113213

    2012-02-29
    01:34

    Suggested Order
    A

    KOU1450
    2012-02-29

    R

    KOUESTROOM BOUHANDELAARS
    18 VOORTREKKER STREET
    CITRASDAL

    0.00
    0.00
    0.00

    123456

    LIE
    2012-03-20

    Ship date 2012-03-20 is invalid or too early

    0001
    A
    PPAR4018
    PP ALUM RIVETS 4.0 X 18 P25

    8.000
    EA
    0.00000
    EA
    0.00
    0.00
    0.00

    0.00

    Stock code PPAR4018 not stocked in warehouse ZC

    0002
    A
    PPARW05
    P/P RIVET WASHERS M5 P25

    4.000
    EA
    0.00000
    EA
    0.00
    0.00
    0.00

    0.00

    Stock code PPARW05 not stocked in warehouse ZC

    0003
    A
    PPBRW05
    P/P BRASS WASHERS M5 P10

    7.000
    EA
    0.00000
    EA
    0.00
    0.00
    0.00

    0.00

    Stock code PPBRW05 not stocked in warehouse ZC

    Failed


    — get Order Header Details
    SELECT
    S.value(‘SalesOrder[1]’, ‘CHAR(6)’) SalesOrder,
    S.value(‘Customer[1]’, ‘CHAR(7)’) Customer,
    S.value(‘Salesperson[1]’, ‘CHAR(3)’) Salesperson,
    S.value(‘CustomerPoNumber[1]’, ‘CHAR(30)’) CustomerPoNumber,
    S.value(‘OrderDate[1]’,’DATETIME’) OrderDate,
    S.value(‘RequestedShipDate[1]’,’DATETIME’) RequestedShipDate
    FROM @xml.nodes(‘/SalesOrders/Orders/OrderHeader’) AS SalesOrder(S)

    — Get Line Details
    SELECT
    S.value(‘SalesOrder[1]’, ‘CHAR(6)’) SalesOrder,
    L.value(‘CustomerPoLine[1]’, ‘INT’) SalesOrderLine,
    L.value(‘StockCode[1]’, ‘CHAR(30)’) StockCode,
    L.value(‘StockDescription[1]’,’CHAR(30)’) Description,
    L.value(‘Warehouse[1]’,’CHAR(2)’) Warehouse,
    L.value(‘OrderQty[1]’, ‘DECIMAL(5,2)’) OrderQty,
    E.value(‘ErrorDescription[1]’, ‘VARCHAR(60)’) Error
    FROM @xml.nodes(‘/SalesOrders/Orders/OrderDetails/StockLine’) AS OrderDetails(L)
    CROSS APPLY @xml.nodes(‘/SalesOrders/Orders/OrderHeader’) AS SalesOrder(S)
    CROSS APPLY @xml.nodes(‘/SalesOrders/Orders/OrderDetails/StockLine/ErrorMessages’) as Errors(E)

  2. SEO公司 says:

    Hi there! I just wish to give a huge thumbs up for the nice information you’ve
    gotten right here on this post. I will probably be coming again to your blog for more soon.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: