THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

Practical SQL Server XML: Part 2

In response to a challenge from Pinal Dave, a fellow MVP and a winner of an award for his blog at the MVP Summit 2009, I am writing a couple of articles to demonstrate practical uses for SQL Server’s XML capabilities.  (Dave’s great blog is at http://blog.sqlauthority.com/).

Dave posted the first post in this series over at http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/, describing how to shred the XML query plans in the SQL Server query plan cache.  In this post I’ll talk about sending multiple parameters, and even parameter sets, to SQL Server via XML. SQL Server MVP Jacob Sebastian is finishing up the series with a post on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

One of the most common requests in the SQL Server newsgroups is “how do I send a delimited list of values to my stored procedures?”  There are many creative answers, including SQL MVP Erland Sommarskog’s popular article “Arrays and Lists in SQL Server” (http://www.sommarskog.se/arrays-in-sql-2005.html).  Erland touches on the method of XML shredding that I’ll cover here.

For this example I’ll use the AdventureWorks 2008 database.  This sample database is available for download from CodePlex (http://sqlserversamples.codeplex.com/) for SQL Server 2008.  For this example I’ll first create a procedure that inserts a single record that must be put in two separate tables:

CREATE PROCEDURE Person.AddPerson

      @PersonType nchar(2) = NULL,

      @NameStyle dbo.NameStyle = NULL,

      @Title nvarchar(8) = NULL,

      @FirstName dbo.Name = NULL,

      @MiddleName dbo.Name = NULL,

      @LastName dbo.Name = NULL,

      @Suffix nvarchar(10) = NULL,

      @EmailPromotion int = NULL,

      @EmailAddress nvarchar(50) = NULL

AS

BEGIN

    -- Get a new BusinessEntityID

      DECLARE @BusinessEntityID int;

      INSERT INTO Person.BusinessEntity(rowguid)

      VALUES (NEWID());

      SET @BusinessEntityID = SCOPE_IDENTITY();

      -- Insert Person

      INSERT INTO Person.Person

      (

            BusinessEntityID,

            PersonType,

            NameStyle,

            Title,

            FirstName,

            MiddleName,

            LastName,

            Suffix,

            EmailPromotion

      )

      VALUES

      (

            @BusinessEntityID,

            @PersonType,

            @NameStyle,

            @Title,

            @FirstName,

            @MiddleName,

            @LastName,

            @Suffix,

            @EmailPromotion        

      );

      -- Insert Email Address

      INSERT INTO Person.EmailAddress

      (

            BusinessEntityID,

            EmailAddress

      )

      VALUES

      (

            @BusinessEntityID,

            @EmailAddress

      );

END

The procedure simply accepts several parameters that reflect a single Person record.  The first step is to insert a row into the Person.BusinessEntity table to get a business entity ID.  Then the remaining parameter values are inserted into the Person.Person and Person.EmailAddress tables.  Simple enough.  But what if you want to insert 2 Person records?  You can round-trip to the server twice with two calls to this procedure, or you can double up the number of parameters and add the extra code in the procedure to deal with it.

But then again, what if you want to add 100 Person records?  How about a thousand or a million?  That’s a lot of round trips to the server or a lot of extra parameters to add (SQL Server actually maxes out at around 2,100 parameters).  If you have SQL Server 2008 and you’re using a .NET client you can use table-valued parameters.  But what if you’re not using a .NET client or SQL 2005 is your target database?  In that case you can use XML to accomplish the same thing.

Using XML to add data to a single table is normally a pretty straightforward shredding operation, but we’re actually touching three different tables in this case. And one of the tables generates an identity value that the other two tables need.  This means the code is not so simple as a straight single-table insert, especially if you decide not to use cursors.  Here’s my XML version of adding a Person:

CREATE PROCEDURE Person.AddPersonXML

      @People xml = NULL

AS

BEGIN

      -- Generate and store all new business entity IDs

      DECLARE @TemporaryIDs TABLE

      (

            BusinessEntityID int NOT NULL

      );

     

      -- Number the XML nodes and generate business entity IDs

      WITH NodeNumbers

      AS

      (

            SELECT

                  ROW_NUMBER() OVER (ORDER BY C.value('PersonType', 'nchar(2)')) AS NodeNumber

            FROM @People.nodes('//Person') T(C)

      )

      INSERT INTO Person.BusinessEntity

      (

            rowguid

      )

      OUTPUT

            inserted.BusinessEntityID

            INTO @TemporaryIDs

            (

                  BusinessEntityID

            )

      SELECT NEWID()

      FROM NodeNumbers nn;

     

      --

      WITH PersonNodes

      AS

      (

            SELECT

                  ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

                  C.query('.') AS PersonData

            FROM @People.nodes('//Person') T(C)

      ),

      BusinessEntityIDs

      AS

      (

            SELECT

                  ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

                  BusinessEntityID

            FROM @TemporaryIDs

      ),

      GetPersonData

      AS

      (

            SELECT

                  bei.BusinessEntityID,

                  pn.PersonData.value(N'(Person/PersonType)[1]', N'nchar(2)') AS PersonType,

                  pn.PersonData.value(N'(Person/NameStyle)[1]', N'dbo.NameStyle') AS NameStyle,

                  pn.PersonData.value(N'(Person/Title)[1]', N'nvarchar(8)') AS Title,

                  pn.PersonData.value(N'(Person/FirstName)[1]', N'dbo.Name') AS FirstName,

                  pn.PersonData.value(N'(Person/MiddleName)[1]', N'dbo.Name') AS MiddleName,

                  pn.PersonData.value(N'(Person/LastName)[1]', N'dbo.Name') AS LastName,

                  pn.PersonData.value(N'(Person/Suffix)[1]', N'nvarchar(10)') AS Suffix,

                  pn.PersonData.value(N'(Person/EmailPromotion)[1]', N'int') AS EmailPromotion

            FROM PersonNodes pn

            INNER JOIN BusinessEntityIDs bei

                  ON pn.JoinID = bei.JoinID

      )

      INSERT INTO Person.Person

      (

            BusinessEntityID,

            PersonType,

            NameStyle,

            Title,

            FirstName,

            MiddleName,

            LastName,

            Suffix,

            EmailPromotion

      )

      SELECT

            BusinessEntityID,

            PersonType,

            NameStyle,

            Title,

            FirstName,

            MiddleName,

            LastName,

            Suffix,

            EmailPromotion

      FROM GetPersonData;

 

      WITH PersonNodes

      AS

      (

            SELECT

                  ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

                  C.query('.') AS PersonData

            FROM @People.nodes('//Person') T(C)

      ),

      BusinessEntityIDs

      AS

      (

            SELECT

                  ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

                  BusinessEntityID

            FROM @TemporaryIDs

      ),

      GetPersonData

      AS

      (

            SELECT

                  bei.BusinessEntityID,

                  pn.PersonData.value(N'(Person/EmailAddress)[1]', N'nvarchar(50)') AS EmailAddress

            FROM PersonNodes pn

            INNER JOIN BusinessEntityIDs bei

                  ON pn.JoinID = bei.JoinID

      )

      INSERT INTO Person.EmailAddress

      (

            BusinessEntityID,

            EmailAddress

      )

      SELECT

            BusinessEntityID,

            EmailAddress

      FROM GetPersonData;

END

 

You notice in this example that we first have to get business entity IDs for each person.  I use a table variable and the DML OUTPUT clause to grab the business entity IDs ahead of time. Then I shred the XML in two steps, once to put relevant data in the Person.Person table and another to put the email addresses in Person.EmailAddress.  The procedure call looks like this:

EXEC Person.AddPersonXML N'<Person>

      <PersonType>SC</PersonType>

      <NameStyle>0</NameStyle>

      <Title>Mr.</Title>

      <FirstName>George</FirstName>

      <MiddleName/>

      <LastName>Jetson</LastName>

      <Suffix/>

      <EmailPromotion>0</EmailPromotion>

      <EmailAddress>George.Jetson@spacelysprockets.com</EmailAddress>

</Person>

<Person>

      <PersonType>SC</PersonType>

      <NameStyle>0</NameStyle>

      <Title>Mr.</Title>

      <FirstName>Fred</FirstName>

      <MiddleName>J.</MiddleName>

      <LastName>Flintstone</LastName>

      <Suffix/>

      <EmailPromotion>1</EmailPromotion>

      <EmailAddress>Fred.Flintstone@sladegravelpit.com</EmailAddress>

</Person>';

 

As you can see, we can easily add as many people as you want with a single XML parameter.  That is, so long as you respect the 2.1 GB size limit on SQL Server LOB data types like XML. Also since you are using XML you can use any client application in any language that can generate XML character data; you’re not limited to just any .NET-specific functionality.

You can verify that the people above were added with a few simple queries:

SELECT *
FROM Person.Person
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.EmailAddress
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.BusinessEntity
WHERE BusinessEntityID > 20777;

 

The results look like the following:

Results of XML-based parameter "set" insert

In the third article of this series at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html, MVP and author Jacob Sebastian will show you how to add XML Schema validations to your xml data type parameter sets.

Published Tuesday, March 03, 2009 10:28 PM by Mike C

Attachment(s): Image3.png

Comments

 

KUMAR said:

i like new things in my life and vi love this post

March 17, 2009 8:12 AM
 

Elisabeth Redei said:

Lies damned lies and statistics - Part II In previous post I listed situations where your statistics

August 10, 2009 8:44 AM
New Comments to this post are disabled

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement