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