Part 4 – Table-Valued Parameters (TVP’s): Table what?


Introduction

This is the fourth 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 Table-Valued Parameters (TVP’s) can be used to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure.  We’ll also look at how .Net Developers can use TVP’s 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

Table-valued parameters are declared by using user-defined table types and were introduced in SQL Server 2008.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.

Restrictions

Table-valued parameters have the following restrictions:

  • SQL Server does not maintain statistics on columns of table-valued parameters.
  • Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  • You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

Creating Tables with .Net

In Part 2 – OpenXML: What is it and how do I use it? I created an ADO.NET Dataset (dsPassenger) consisting of two tables; Passenger and PAX.  I will use these same tables to pass data to a stored procedure that accepts Table-valued Parameters.

Creating and Using Table-Valued Parameters

The first step is to create the required table types and define their table structure (For full details on how to create a SQL Server type, see User-Defined Table Types).
 
image
 
CREATE TYPE [Person].[udtt_Passenger] AS TABLE
(
	[Newsletter] [bit] NOT NULL,
	[PersonID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[PersonID] ASC
	)
)
GO

CREATE TYPE [Person].[udtt_PAX] AS TABLE
(
	[PaxID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[PaxOrder] [tinyint] NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[PaxID] ASC	)
)
GO
Hint: An easier way to do this is to generate a Create Table Script based on an existing table and then modify this script to use the CREATE TYPE syntax.
 
The next step is to create a stored procedure that accepts the Passenger and PAX UDTT’s as parameters
 
CREATE PROCEDURE [Person].[usp_UpdatePassengerDetailTVP]
(
	@Passenger [Person].[udtt_Passenger] READONLY,
	@Pax [Person].[udtt_Pax] READONLY
)
AS 

SET NOCOUNT ON

UPDATE	[Person].[Detail]
SET		FirstName = P.FirstName,LastName = P.LastName,
		Newsletter = P.Newsletter
FROM	@Passenger P
WHERE	[Person].[Detail].PersonID = P.PersonID

UPDATE	[Person].[Pax]
SET		FirstName = P.FirstName,LastName = P.LastName,
		PaxOrder = P.PaxOrder
FROM	@Pax P
WHERE	[Person].[Pax].PAXID = P.PAXID

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 ON
Now that I’ve created the stored procedure, I can test it with some simple code.
 
-- Get current record details
SELECT	PersonID,FirstName,LastName,Newsletter
FROM	[Person].[Detail]
WHERE	PersonID = '7a7c18a0-ad47-e011-9c71-00155d991421'
  
SELECT	PaxID,FirstName,LastName,PaxOrder
FROM	[Person].[PAX]
WHERE	PaxID IN ('868b8fde-068a-4468-b9b6-9660166e630d',
		'bb2770e0-b0e0-479f-8c03-1330ce87cb64')
		
-- Create local table type variables and populate
-- with data
DECLARE @Passenger [Person].[udtt_Passenger]
DECLARE @Pax [Person].[udtt_Pax]

INSERT INTO @Passenger (PersonID,FirstName,LastName,Newsletter)
VALUES ('7a7c18a0-ad47-e011-9c71-00155d991421','Jeff','Wharton',0)

INSERT INTO @Pax (PaxID,FirstName,LastName,PaxOrder)
VALUES ('868b8fde-068a-4468-b9b6-9660166e630d','Lily','Wharton',3),
('bb2770e0-b0e0-479f-8c03-1330ce87cb64','Ben','Wharton',2)

-- Execute the stored procedure
EXEC [Person].[usp_UpdatePassengerDetailTVP] @Passenger, @PAX

-- Get updated record details
SELECT	PersonID,FirstName,LastName,Newsletter
FROM	[Person].[Detail]
WHERE	PersonID = '7a7c18a0-ad47-e011-9c71-00155d991421'
  
SELECT	PaxID,FirstName,LastName,PaxOrder
FROM	[Person].[PAX]
WHERE	PaxID IN ('868b8fde-068a-4468-b9b6-9660166e630d',
		'bb2770e0-b0e0-479f-8c03-1330ce87cb64')
image
 
Now that I know the stored procedure functions correctly,  let’s take a look at the .Net code required to pass data to the stored procedure.

Using .NET to Pass Table-Valued Parameters

The .Net code I need to use to call the stored procedure is practically the same as discussed in Part 2 – OpenXML: What is it and how do I use it?.  All I need to do is is change the stored procedure name to [Person].[usp_UpdatePassengerDetailTVP]  and replace the existing XML parameter with two new parameters of type SqlDbType.Structured:

db.AddInParameter(dbCommand, "@Passenger", SqlDbType.Structured, 
    dsPassengers.PassengerDataTable);
db.AddInParameter(dbCommand, "@Pax", SqlDbType.Structured, 
    dsPassengers.PaxDataTable);

Conclusion

As you can see from the above example, using Table-valued Parameters to send multiple rows of data to SQL Server is very simple to implement.  That said, there are a couple of issues that make using TVP’s a bit of a pain (if not a waste of time):

  • Once you’ve created a user-defined table types you can’t modify it.  You need to drop and recreate.
  • You can’t drop a user-defined table types if it’s being referenced by another object i.e. in this example, I cannot drop my UDDT’s as they are being referenced by stored procedure [Person].[usp_UpdatePassengerDetailTVP], thus I can not modify the UDDT’s unless I remove all references
  • The order of columns in a Datatable need to be in the exact same order as the UDDT as no column mapping is applied.

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

Part 2 – OpenXML: What is it and how do I use it?


Introduction

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

In this post we’ll look at how to use the OpenXML Rowset Provider to shred an XML document into a rowset which can then be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear. We’ll also look at how .Net Developers can use OpenXML 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

The OpenXML Rowest Provider was first introduced in SQL Server 2000 where it could be used to shred an XML string into columns of a rowset by evaluating XPath 1.0 expressions. In SQL Server 2005 Microsoft enhanced OpenXML so that it could accept the newly introduced XML Data Type in sp_xml_preparedocument, and to generate XML and new SQL type columns in a rowset.

Syntax

The basic OpenXML syntax is:

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
(Please refer to http://msdn.microsoft.com/en-us/library/ms186918.aspx for full syntax detail and argument options.)
Now that all the boring stuff is out of the way, let’s look at a very simple example of shredding an XML document into a rowest and then using that rowset to update records in our database.
 

Generating XML with .Net

For the purpose of this example, a Passenger is the primary person on a cruise booking and PAX are additional persons traveling with this passenger. Now, I know you’re probably already thinking “Why has he got FirstName and LastName in both tables?”. I’ve done this to make the example easier to follow.

Figure 1 represents an ADO.NET Dataset called dsPassengers which contains two tables (Passenger and PAX) and it has been created based on two tables in our SQL Server database called Passenger and PAX. As this is an internal use dataset only, I have blanked out the Namespace property for this dataset (I will elaborate on my reasons for doing this later).

The PersonID, PAXID, FirstName and LastName columns are all defined as System.String, the Newsletter column has been defined as System.Boolean, and the PAXOrder column has been defined as a System.Byte.

image

Figure 1

Let’s assume that I have a .Net application running in the background which has populated this dataset with the following records:

Passenger

PersonID: 7a7c18a0-ad47-e011-9c71-00155d991421

FirstName: Jeff

LastName: Wharton

Newsletter: False

PAX

PAXID: 868b8fde-068a-4468-b9b6-9660166e630d

FirstName: Lily

LastName: Wharton

PAXOrder: 3

PAXID: bb2770e0-b0e0-479f-8c03-1330ce87cb64

FirstName: Ben

LastName: Wharton

PAXOrder: 2

This gives me a total of 3 records (rows) that I’d like to send to SQL Server.

Given this situation, most people would probably set up a loop to traverse the tables in the dataset, set up another loop to traverse the records in each table, and send each row to the database one at a time resulting in 3 calls being made. This is where the power of XML comes into play.

If you call the GetXML() method of an ADO.NET Dataset, it returns an XML representation of the data held within it.  This is one of the benefits of using ADO.NET Datasets; it looks after all the messy work involved in creating an XML representation of your data.  So, for our dataset, the XML would look like:

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

You can see from the returned XML that the Passenger and PAX tables are nested within dsPassenger and that there is 1 Passenger record (row) and two PAX records (rows).

Now that I’ve got this 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 OpenXML

The first step is to create two variables in SQL Server Query Analyser; one to hold the XML returned above and another to be used with OpenXML:

DECLARE @idoc int
DECLARE @doc 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 reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

sp_xml_preparedocument returns a handle (assigned to the @idoc variable I created earlier) that can be used to access the newly created internal representation of the XML document.

Now I need to write the code that takes this handle and passes it to the OpenXML Rowset Provider:

SELECT    PersonID, FirstName, LastName, Newsletter
FROM       OPENXML (@idoc, '/dsPassengers/Passenger',2)
            WITH (PersonID  varchar(50),
                  FirstName varchar(20),
                  LastName varchar(20),
                  Newsletter BIT)

SELECT    PersonID, PAXID, FirstName, LastName
FROM       OPENXML (@idoc, '/dsPassengers/PAX',2)
            WITH (PAXID  varchar(50),
                  FirstName varchar(20),
                  LastName varchar(20),
                  PaxOrder int,
                  -- If we use the optional ColPattern option, we can 
                  -- access data anywhere within the XML
                  PersonID varchar(50) '../Passenger/PersonID')
ORDER BY PaxOrder

I then need to write the code that removes the internal representation of the XML document specified by the document handle and invalidates the document handle.

EXEC sp_xml_removedocument @idoc

It is very important that you execute sp_xml_removedocument as soon as you can as a parsed document is stored in the internal cache of SQL Server and the MSXML parser (Msxmlsql.dll) uses one-eighth of the total memory available for SQL Server so we don’t want these little buggers hanging around for too long!

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

image

Because OpenXML has turned my 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       OPENXML (@idoc, '/dsPassengers/Passenger',2)
            WITH (PersonID  varchar(50),
                  FirstName varchar(20),
                  LastName varchar(20),
                  Newsletter BIT) XMLData
INNER JOIN Booking.Actual BA ON XMLData.PersonID = BA.PersonID

UPDATE Person.Detail
SET FirstName = XMLData.FirstName, LastName = XMLData.LastName,
Newsletter = XMLData.Newsletter
FROM       OPENXML (@idoc, '/dsPassengers/Passenger',2)
            WITH (PersonID  varchar(50),
                  FirstName varchar(20),
                  LastName varchar(20),
                  Newsletter BIT) XMLData
WHERE Person.Detail.PersonID = XMLData.PersonID

image

Namespaces

Earlier on I stated that I was blanking out the Namespace property of dsPassenger. Here’s the reason why.

If I add a Namespace to our dataset (I’ve used the default assigned by ADO.NET), the XML returned from the GetXML() method looks this:

<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 our sp_xml_preparedocument statement

EXEC sp_xml_preparedocument @hdoc OUTPUT, @PassengerDetail,
  '<dsPassengers xmlns:a="http://tempuri.org/dsPassengers.xsd"/>'

and also in our OpenXML statement

SELECT [a:PersonID] PersonID, [a:FirstName] FirstName, 
[a:LastName] LastName, [a:Newsletter] Newsletter
FROM OPENXML(@hDoc, '//a:Passenger', 2)
WITH ([a:PersonID]  varchar(50),
                  [a:FirstName] varchar(20),
                  [a:LastName] varchar(20),
                  [a:Newsletter] BIT)

Now, for the XML purists out there, this is great.  For someone whose not sharing their XML outside of their organisation and just wants to shred-and-store, all this extra work is a pain in the backside so I never use a Namespace.

Stored Procedure

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

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

SET NOCOUNT ON 

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @PassengerDetail

UPDATE Person.Detail
SET FirstName = XMLData.FirstName, LastName = XMLData.LastName,
Newsletter = XMLData.Newsletter
FROM       OPENXML (@idoc, '/dsPassengers/Passenger',2)
            WITH (PersonID  varchar(50),
                  FirstName varchar(20),
                  LastName varchar(20),
                  Newsletter BIT) XMLData
WHERE Person.Detail.PersonID = XMLData.PersonID 

UPDATE Person.PAX
SET PersonID = XMLData.PersonID, FirstName = XMLData.FirstName,
LastName = XMLData.LastName
FROM       OPENXML (@idoc, '/dsPassengers/PAX',2)
            WITH (PAXID  varchar(50),
                  FirstName varchar(20),
                  LastName varchar(20),
                  PaxOrder int,
                  PersonID varchar(50) '../Passenger/PersonID') XMLData
WHERE Person.PAX.PAXID = XMLData.PAXID 

EXEC sp_xml_removedocument @idoc

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 to call it from a .Net application.

Putting It All Together

To call the stored procedure from .Net and pass my our XML data is very straight forward.

I’m using the Microsoft Enterprise Library 5.0 as it handles the majority of the database code required to connect to SQL Server and execute the stored procedure.

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
// Custom Dataset Schema Class
//****************************
using Ozcruising.Schemas;
//****************************
using System.Data.Common;
using System;
using System.Data;

class OpenXML
{
    public int UpdatePassengerPaxDetails(dsPassengers ds)
    {
        SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

        // This is the stored procedure we created earlier
        DbCommand dbCommand = 
            db.GetStoredProcCommand("[Person].[usp_UpdatePassengerDetail]");

        // Add a paramter of type XML and assign the 
        // XML from the dsPassengers dataset
        db.AddInParameter(dbCommand, "@PassengerDetail", DbType.Xml, ds.GetXml());
        // Add a ReturnValue paramter of type INT for error checking
        db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int16, 0, 
            ParameterDirection.ReturnValue, false, 0, 0, "", 
            DataRowVersion.Current, 0);
            
        // Execute the stored procedure
        db.ExecuteNonQuery(dbCommand);
            
        //Get error indicator
        int erroCode = 
            Convert.ToInt32(db.GetParameterValue(dbCommand, "@RETURN_VALUE"));

        return erroCode;
    }
}

How simple is that!   I’ve replaced 3 calls to the database with one.

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.

Part 1 – Using TVP’s and XML to Transport Relational Data


Introduction

I’ve been using XML as a means of sending multiple rows of data to SQL Server ever since OpenXML was released in SQL Server 2000.  Although 2000 didn’t have an XML data type, the OpenXML Rowset Provider enabled SQL Server to shred a XML string into a rowset which could then be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.  Gone were the days of looping through rows of data and sending each row to SQL Server one at a time.  Just when I thought things couldn’t get any better, Microsoft released SQL Server 2005.

One of the new features of 2005 was the introduction of the native XML data type. This new type allowed users to create tables that had one or more columns of type XML in addition to relational columns, create XML variables and parameters, and use a subset of the XQuery Language to query XML data.  The XML data type could also be used with the OpenXML Rowset Provider.  Developers now had two easy methods of sending multiple rows of data to SQL Server.

With the release of SQL Server 2008 came Table-valued parameters (TVP).  Table-valued parameters are declared by using user-defined table types and can be used to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.  You guessed it; another method.

The Problem

Developers can now use a number of different methods for sending multiple rows of data to SQL Server and sometimes choosing the correct method to use can be frustrating.  Which one is faster?  Which one is easier to implement?  Can I use a mixture of both? Why isn’t there somewhere I can look for a comparison on all these technologies?  These are some of the questions I’ve been asking myself over the last couple of weeks.

What’s This Post All About?

I originally thought of documenting many of the things I discovered about these technologies in a word document to be stored away and potentially lost over the coming months.  I also thought about throwing it all away and sticking with my current method (OpenXML) as I found I had more question than answers and it was all starting to sound too difficult.  In the end I was convinced (by a colleague) to blog about my findings.

The purpose of this series of posts is to provide other developers, DBD’s and DBA’s with an insight into the above mentioned technologies and hopefully assist them with choosing and implementing a technology (or technologies) to use within their applications.  I’m also hoping that others will provide comment on their experiences with these technologies.

Scope

OpenXML:  What is it and how do I use it?
XQuery: Please don’t use OpenXML!
Table-Valued Parameters (TVP’s): Table what?
OpenXML and XQuery Optimisation Tips

Final Word

Estimated post dates:

OpenXML: 27th May 2011
XQuery: 28th May 2011
Table-Valued Parameters (TVP’s): 29th May 2011
OpenXML and XQuery Optimisation Tips: 12th June 2011

A New Beginning


While researching content on OPENXML vs. XQuery vs. Table-Valued Parameters, I stumbled across the SQL Server Forums; a place I used to spend quite a lot of time. I also visited a number of blogs created by friends and fellow SQL Server enthusiasts. At the time I was quite happy to lap-up the copious amounts of knowledge offered by these medium and move on. However, as the days passed, I felt something I haven’t felt for a very long time; the urge(need) to participate.

It has been quite some time since I last contributed to a forum or participated in the SQL Server Community, and it has been even longer since I last blogged (a little over three years I believe). Given this, I’m hoping the transition back into the SQL Server Community will be relatively painless.

%d bloggers like this: