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

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.

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

  1. Rifle Scope says:

    I’m impressed, I must say. Rarely do I encounter a blog that’s
    both equally educative and engaging, and let me tell you,
    you’ve hit the nail on the head. The issue is something too few folks are speaking intelligently about. Now i’m very happy
    I found this in my hunt for something relating to this.

  2. Mr. Wharty's Ramblings says:

    I’ve just checked it out using IE, Firefox, Chrome and Safari and can’t see the problem you’re referring to. What browser and version are you using?

  3. Antoine says:

    Incredible! This blog looks just like my old one!
    It’s on a totally different topic but it has pretty much the same layout and design. Great choice of colors!

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: