Passing lists to SQL Server 2005 with XML Parameters - Jon Galloway

Passing lists to SQL Server 2005 with XML Parameters

 

Overview

SQL Server 2005's XML capabilities make it a easier to pass lists to SQL Server procedures.

Background

I recently needed to write a stored procedure which took a list of ID's as a parameter. That's one of those things that seems like it would be really simple, but isn't. You'd think you could just pass in a comma delimited string of id's: @ids = '3,5,7,8' and use something like 'SELECT * FROM Products WHERE ID IN (@ids)'. Nope, it doesn't work. I still remember my surprise when I ran into that six or seven years ago.

There are a huge variety of workarounds for this issue - see Erland's comprehensive list ranging form SQL Server 6.5 to 2000. I've used several of these, and while they worked I never liked them. Probably the best method is to just use a SPLIT table valued function which splits your string and returns a table. It's clean, but all of your procedures depend on the existence of that function.

It was also possible to use OPENXML in SQL Server 2000. The syntax was obviously put together by C++ programmers (you have to prepare a document and work with an integer handle, which feels a lot like a pointer), and there were some limitations to be aware of, but it pretty much worked.

This time around, I decided to try this with SQL Server 2005's XML capabilities and see if it was any easier. It is.

Getting started with SQL Server 2005's XML Syntax

XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.

 

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

SELECT
ParamValues.ID.value(
'.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

 

Which gives us the following three rows:

3
6
15

Alright, just show me how to pass a list in a procedure parameter already!

Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.

CREATE PROCEDURE SelectByIdList(@productIds xml) AS

DECLARE @Products TABLE (ID int)

INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

SELECT * FROM
Products
INNER JOIN
@Products p
ON Products.ProductID = p.ID

Now we can call it as follows:

 

EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

 

Which gives us the following:

ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued ID
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10 13 100 25 0 3
6 Grandma's Boysenberry Spread 3 2 12 - 8 oz jars 25 120 0 25 0 6
15 Genen Shouyu 6 2 24 - 250 ml bottles 15.5 39 0 5 0 15

In order to use this, you'll need to an XML string with your ID's. In our application, Steve was handling the application code, and I talked him into doing this via quick and dirty string concatenation. His method worked great:

 

public static string BuildXmlString(string xmlRootName, string[] values)
{
StringBuilder xmlString
= new StringBuilder();

xmlString.AppendFormat(
"<{0}>", xmlRootName);
for (int i = 0; i < values.Length; i++)
{
xmlString.AppendFormat(
"<value>{0}</value>", values[i]);
}
xmlString.AppendFormat(
"</{0}>", xmlRootName);

return xmlString.ToString();
}

What's next?

This is a very simple use of XML in SQL Server. You can pass complex XML documents containing business objects to insert and update in your relational tables, for instance. If you're going to do that with a large amount of data, have a look at Ayende's clever use of SqlBulkCopy to handle that more efficiently.

Published Friday, February 16, 2007 1:01 AM by Jon Galloway
Filed under: ,

Comments

# re: Passing lists to SQL Server 2005 with XML Parameters

cross apply would also be available if you just wanted to write a single query - no messing with temp tables - just 'join' against the xml.nodes() function directly...

Monday, February 19, 2007 9:31 AM by chrisb

# re: Passing lists to SQL Server 2005 with XML Parameters

Jon,

  How do we do it with ADODB. I have a C++ dll connecting to SQL 2005.

  ADODB does not recognise xml data type, so I am trying to pass with varchar. I keep hitting Input error 80040E14. My input String is _bstr_t bstrDeviceList = _T("'<AMS>\r\n<DeviceList>\r\n<Device AmsTag=\"PDT1\"/>\r\n</DeviceList>\r\n</AMS>'");

If I pass a string like "Test" it works but not with XML.

What could be possible?

Friday, March 2, 2007 2:35 PM by Prasanna

# re: Passing lists to SQL Server 2005 with XML Parameters

@Prasanna - I haven't used ADODB for long time, so I'm not sure how you'd do this. I believe that ADODB can provide more detailed error messages via another property, which might give a better idea of what's wrong.

Probably the easiest way to deal with this is to run a trace on the database (in SSMS, go to Tools, then SQL Server Profiler) and see exactly what SQL is being executed. You can copy the SQL statement and try to execute it in a query window and see what syntax error is reported.

Friday, March 2, 2007 3:44 PM by Jon Galloway

# re: Passing lists to SQL Server 2005 with XML Parameters

Not being funny but I'm sure SQL does support a comma separated varchar value being passed in.  We did this in SQL Server 2000 when we wanted to do a search for multiple words returned from a variant generator and all we passed in was a comma delimited string of the words, which the stored procedure then interrogated in the where clause.

Seeing that the LIKE statement works with a variable, why wouldn't this?!

Thursday, March 8, 2007 5:29 AM by GH

# re: Passing lists to SQL Server 2005 with XML Parameters

How can I pass multiple nodes into a table like

<Products><id>3<name>Joe</name><age>22</age></id><id>6<name>mike</name><age>50</age></id><id>15<name></name><age></age></id></Products>'

Tuesday, June 19, 2007 1:29 PM by mike

# re: Passing lists to SQL Server 2005 with XML Parameters

I tried calling SqlAdapter.Fill(Datatable) after assigning the stored procedure as the select command to the adapter. Select command takes SqlXml variable as a parameter. It gives me "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods." I did set ARITHABORT to ON in the stored procedure, but didn't help. Executing stored procedure (exe stored_proc @xml) with an xml parameter works fine.

Friday, June 22, 2007 4:29 PM by Gaurav Solanki

# re: Passing lists to SQL Server 2005 with XML Parameters

This is in response to Guarav Solanki.  I am using a customized version of this stored procedure (thanks very much, Mr. Galloway!) and i ran into the same 'ARITHABORT' issue.

After much wrangling with it, i finally resolved the problem by adding the following line to the stored procedure, directly FOLLOWING the 'AS' keyword:

set ARITHABORT ON

Again, this needs to be within the stored procedure, not above it like the 'set ANSI_NULLS ON' and 'set QUOTED_IDENTIFIER ON' statements usually are by default.

Hope this helps.

Tuesday, August 14, 2007 4:18 PM by Jesse

# re: Passing lists to SQL Server 2005 with XML Parameters

How can I form a query to select multiple nodes by using above format?

Thursday, August 23, 2007 6:49 AM by chaitanya

# re: Passing lists to SQL Server 2005 with XML Parameters

I am facing a problem while i pass xml as an intput to stored procedure.

The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @XMLString  XML

Set @XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >

</Company>'

Exec sproc_Insert_Company @XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2

XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

How to resolve it??

plzz do help at the earliest...

Tuesday, September 11, 2007 10:48 AM by Supriya

# re: Passing lists to SQL Server 2005 with XML Parameters

@Supriya - you need to escape the following characters:

& (should be &amp;)

< (should be &lt;)

> (should be &gt;)

You can do that with a simple string.Replace call:

xmlString = xmlString.Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;")

Tuesday, September 11, 2007 11:55 PM by Jon Galloway

# re: Passing lists to SQL Server 2005 with XML Parameters

Thanx for the reply.........

Thursday, September 13, 2007 1:47 AM by Supriya

# re: Passing lists to SQL Server 2005 with XML Parameters

Thanks so much for this. It is most useful!!!

Being new to using xml (let alone xml and store procedure together!!) I have two questions:

1.  ParamValues.ID.value('.','VARCHAR(20)'):  What is the parameter '.'?  What does it stand for?  I tried looking for it in the web but could not find an answer.

2. @productIds.nodes('/Products/id') as ParamValues(ID):   Is there a way to make node specification dynamic instead of relying on a string literal?  I guess the answer is 'no,' because when I tried to substitute it with a @Variable, SQL2005 gives me an error.  It would be nice, though, if that could be made dynamic. . .

Thanks again!

Friday, December 14, 2007 1:28 AM by Justin

# re: Passing lists to SQL Server 2005 with XML Parameters

this is very good and informative

thanx

Thursday, January 31, 2008 12:16 AM by Ehsan Ahmed

# re: Passing lists to SQL Server 2005 with XML Parameters

Hi,

  This is very good and helpfull.

Thursday, February 14, 2008 8:47 AM by Ritesh Arya

# re: Passing lists to SQL Server 2005 with XML Parameters

In ParamValues.ID.value('.','VARCHAR(20)'):  The parameter '.' is an XQuery expression.  In this case it means "whatever you find in this node".

Sunday, February 17, 2008 5:09 PM by Mark

# re: Passing lists to SQL Server 2005 with XML Parameters

@Pradeep You'll want to take a look at bulk copy for large XML sets. See Ayende's post: www.ayende.com/.../7661.aspx

Thursday, February 21, 2008 1:11 PM by Jon Galloway

# re: Passing lists to SQL Server 2005 with XML Parameters

Hi I have one table with five coloumns I used dataset to write xml now this xml doc I want to use for the stored proc which will update the values into the database tried lot But Giving error in Sql executing Pleasae help me out : my xml file is like

- <GPIData>

- <Values>

 <customerId>1</customerId>

 <UserId>1</UserId>

 </Values>

- <Values>

 <customerId>2</customerId>

 <UserId>2</UserId>

 </Values>

- <Values>

 <customerId>3</customerId>

 <UserId>3</UserId>

 </Values>

 </GPIData>

example how can I insert it to DB using SP Please tell

Wednesday, February 27, 2008 7:18 AM by Swapnil

# re: Passing lists to SQL Server 2005 with XML Parameters

What if I want to use the XML data in the INSERT statement in the stored procedure ? How do I write the INSERT statement ?

Wednesday, March 5, 2008 9:25 PM by Wyatt Wong

# re: Passing lists to SQL Server 2005 with XML Parameters

I think I finally have something worth using in place of lists of bound parameters (thats the way I used to do it from cold fusion by specifying list=yes on a cfqueryparam tag - simple to use; but, inefficient for longer lists)

Create FUNCTION CSVToXML

(

/* Written by Ed Sanford of ODI Consulting (c) 2008 */

@CSVStr varchar(Max)

)

RETURNS XML

AS

BEGIN

DECLARE @Result XML

SELECT @Result = '<Vals><V>' + Replace(@CSVStr,',','</V><V>') + '</V></Vals>'

RETURN @Result

END

Thursday, March 13, 2008 7:30 PM by Ed Sanford

# re: Passing lists to SQL Server 2005 with XML Parameters

cool it works like charm

Tuesday, March 25, 2008 6:28 PM by jay

# re: Passing lists to SQL Server 2005 with XML Parameters

I have to pass the xml to the stored procedure from vb.net code.

I have a string variable ids="<CT><id>55</id></CT>"

This xml I am getting from another stored procedure ( for xml auto)

I take this xml output in a string variable and passing it to another stored procedure which is like your example.

It is not returning anything. But if I write the parameter value manually , while passing <CT><id>55</id></CT> it works and also when I run the stored procedure and pass this string it works fine.

  Dim cXml As String = String.Empty

cxml = datareader.items(0) 'This is the xml output from the stored procedure

Then I pass this to the other stored procedure that takes the xml parameter

 oParams(0) = New SqlClient.SqlParameter("@Ids", cxml)

but it doent return.

I am selecting some counts from tables where ids are like Ids in the xml it gives all 0

Thursday, April 17, 2008 11:05 AM by Naina

# re: Passing lists to SQL Server 2005 with XML Parameters

Hi,

Oh, so many problems...

Problem #1

I have xml with multiple nodes something like this:  <Products><id>3<name>Joe</name><age>22</age></id><id>6<name>mike</name><age>50</age></id><id>15<name></name><age></age></id></Products>'

When I use your example, I can only insert 1 table field at a time.  If I try to do more I get syntax errors in sql.

I can only do:

INSERT INTO #DBTable (name)

SELECT ParamValues.name.value('.','varchar(20)')

FROM @XmlString.nodes('/Products/id/name') as ParamValues(name)

Then I can do another insert on the next field in this examples case 'age'.

Problem #2:

In my c#.net program, I'm using Microsoft's Data Access Application Block for .NET code.  I run the ExecuteScalar procedure but I get the error message: XML parsing: line 1, character 305, end tag does not match start tag

I tried to look at my xmlString that was built with StringBuilder to see if in fact there was a tag problem but it only shows the values that I put into xmlString and not the <sectionNames></sectionNames>.

Any help is appreciated!

Thanks,

Carolyn

Friday, May 2, 2008 5:25 PM by Carolyn

# re: Passing lists to SQL Server 2005 with XML Parameters

Hello,

I solved my 2 problems.  

Problem #2:  there was a mismatching closing tag in my XML.

Problem #1: I had to construct my sql as follows:

Set @variable = (SELECT Cast(ParamValues.variable.value('.','int') as int)

FROM @XmlString.nodes('/tagname/variable') as ParamValues(variable))

Then I do a separate insert statement using the @variable name.

Hope this helps someone else,

carolyn

Monday, May 5, 2008 4:17 PM by Carolyn

# re: Passing lists to SQL Server 2005 with XML Parameters

Great post!

so I noticed some people were having problems with accessing child nodes.

I have a solution with the following script

@xml = <Products><id>3<name>Joe</name><age>22</age></id><id>6<name>mike</name><age>50</age></id><id>15<name></name><age></age></id></Products>

SELECT ParamValues.ID.query('name').value('.','VARCHAR(20)')

ParamValues.ID.query('age').value('.','VARCHAR(20)')

FROM @productIds.nodes('/Products/id') as ParamValues(ID)  

HTH

later,

Channa

Tuesday, May 6, 2008 7:39 PM by channa

# re: Passing lists to SQL Server 2005 with XML Parameters

Cannot insert the value NULL into column 'CustomerID', table 'samplexmlinsert.dbo.Orders'; column does not allow nulls. INSERT fails.

Wednesday, June 4, 2008 5:10 AM by naveen

# re: Passing lists to SQL Server 2005 with XML Parameters

Hi channa,

It worked like a charm.

ProductIDs need to be declared as XML: heres the full snippet

declare @ProductIDs xml

set @ProductIDs = '<Products><id><name>Joe</name><age>22</age></id><id><name>mike</name><age>50</age></id><id><name></name><age></age></id></Products>'

SELECT

ParamValues.ID.query('name').value('.','VARCHAR(20)') as PName,

ParamValues.ID.query('age').value('.','int') as PAge

FROM @ProductIDs.nodes('/Products/id') as ParamValues(ID)  

Thursday, August 7, 2008 9:19 AM by Maulik

# re: Passing lists to SQL Server 2005 with XML Parameters

Hi

  Can I get help on how to fire Update statement when I pass a XML Parameters. I want to update some values in table.

  The data contais <UserDetails><User><ID>5</ID><Name>ABC</Name></User><User><ID>6</ID><Name>PQR</Name></User><UserDetails>. Now I want to update the records for ID 5 and 6.

Thanks in advance

Saturday, August 9, 2008 3:22 AM by Shrihari

# re: Passing lists to SQL Server 2005 with XML Parameters

HI,

how cna I display just id from below xml in select statement?

@xml = '<Products><id>3<name>Joe</name><age>22</age></id><id>6<name>mike</name><age>50</age></id><id>15<name></name><age></age></id></Products>'

Thanks in advance

Thursday, August 21, 2008 2:43 PM by ali

# re: Passing lists to SQL Server 2005 with XML Parameters

Interesting article. I also like the concept that Paul showed in his comment about using a comma separated list as the input. I will be trying both. Thanks!

Friday, September 12, 2008 10:37 AM by ryanoc

# re: Passing lists to SQL Server 2005 with XML Parameters

I am getting result of 2rows with 4 columns using a select statement, now i want to pass each row values to a function using while loop, can anyone tell me how to pass the values to the function using while loop.

ID   |  Name |  FROMDATE  | TODATE

-----------------------------------

10   |  xyz  | 09/12/2008 | 10/12/2008

11   |  abc  | 09/15/2008 | 11/10/2008

and want to insert record in #temp table

function returns table

thanks in advance

Friday, September 12, 2008 10:38 AM by girishms

# re: Passing lists to SQL Server 2005 with XML Parameters

Hey Channa,

Thanks for the info.

Friday, December 12, 2008 5:26 PM by RexM

# re: Passing lists to SQL Server 2005 with XML Parameters

hi there,

its a great article, i just waana know the performance cost of passing xml datatype with a single item say 'id', but around 50000 and more records..

Is it possible to pass xml datatype with that many records......

Saturday, March 14, 2009 1:25 AM by Vijay

# re: Passing lists to SQL Server 2005 with XML Parameters

Just came across this.  Great stuff.  Works like a charm.  Instead of declaring a table and populating it I just use the SELECT in an IN

Thursday, August 27, 2009 1:17 PM by jl

# re: Passing lists to SQL Server 2005 with XML Parameters

How can I pass functions like GetDate() from a XML string?

I get error saying:

Msg 241, Level 16, State 1, Procedure spInsertData, Line 21

Conversion failed when converting date and/or time from character string.

Any help would be great. thanks!!!

Monday, September 14, 2009 2:07 PM by Samuel

# re: Passing lists to SQL Server 2005 with XML Parameters

<UpdatedReminders><Reminder ReminderId="23" OldDueDate="09/21/2009" NewDueDate="09/25/2009" /></UpdatedReminders>

How can i parse these date attributes

Thursday, September 24, 2009 2:08 PM by Giri

# re: Passing lists to SQL Server 2005 with XML Parameters

<Products><id>3<name>Joe</name><age>22</age></id><id>6<name>mike</name><age>50</age></id><id>15<name></name><age></age></id></Products>'

How to  insert?

Wednesday, November 25, 2009 3:19 AM by Selva

# re: Passing lists to SQL Server 2005 with XML Parameters

Really it helped me a lot

Tuesday, December 29, 2009 1:01 AM by Phani

# re: Passing lists to SQL Server 2005 with XML Parameters

Hi,

it really helped to solve one of my big issues for passing xml as I/P parameter

Monday, January 11, 2010 6:01 AM by Divya Ram

# re: Passing lists to SQL Server 2005 with XML Parameters

Great post.  Great technique.  This is a great way update or insert large sets of data into the database.  

For example if you have combinations of hundreds of client selected options for saving states or something like that.  This comes in really handy and it works very fast compared to looping through the code updating the database everytime through a loop.  

Monday, March 15, 2010 2:25 PM by doctork

# re: Passing lists to SQL Server 2005 with XML Parameters

Here's my string in C# in case that helps (from comment above):

String testStr = "<Branches><branch>12</branch><branch>14</branch></Branches>";

       SqlParameter parms = new SqlParameter("@distList", testStr);

Tuesday, March 30, 2010 1:39 PM by Josey

# re: Passing lists to SQL Server 2005 with XML Parameters

Jon, simple and elegant. Exactly what I was looking for. Thanks!

Wednesday, April 28, 2010 9:21 PM by Roger

# re: Passing lists to SQL Server 2005 with XML Parameters

Great information!  Thanks!

Monday, August 9, 2010 11:00 AM by Nick Olsen

# re: Passing lists to SQL Server 2005 with XML Parameters

IT is great help to me.

Friday, September 17, 2010 8:37 AM by RAMA

# re: Passing lists to SQL Server 2005 with XML Parameters

Using Try / Catch block, if an error ocurred in the INSERT statement and you need to know in which ID was; what you will do?

Monday, December 13, 2010 10:29 AM by tecno

# re: Passing lists to SQL Server 2005 with XML Parameters

Got this to work just be following all steps outline. very well written. thanks.

Question: is this code susceptible to SQLInjection?

Thursday, January 20, 2011 2:50 PM by Vkor

# re: Passing lists to SQL Server 2005 with XML Parameters

Superb. Very well written. Tried it out with SQL Server 2008. Works perfect, but I get a message when I create the stored procedure at the line which contains SELECT ParamValues.ID.value('.','VARCHAR(20)'). The part is underlined and the message is "Cannot find either column ParamValues or the user defined function or the aggregate ParamValues.ID.value, or the name is ambiguous"  Just curious what this message is...

Friday, February 11, 2011 1:43 AM by Alhad

# re: Passing lists to SQL Server 2005 with XML Parameters

ParamValues.ID.value('.','VARCHAR(20)').  in this line whether the lenth of data type Varchar is metioned as 20 is it default lengh or we can increase or decreas it?

becoz when i tried to form xml with more then 30 char lenth its showing 'string or binary data error' while excuting, plz give me solution for this

Friday, June 17, 2011 6:55 AM by Suja

# re: Passing lists to SQL Server 2005 with XML Parameters

Thank u so much for that fantastic webpage article. but i have some issue in my code,

explanation is follows

There is a table with two columns message_id and receiver_id. For one message there are many receivers. Hence we need to insert multiple rows with multiple receiver_id but same message_id. I am having message_id in a variable and receiver_ids I am carrying thru an xml. Can anybody help me with the syntax how I can do this. I am using following code but its working only when I select one receiver id and doesn’t work when receiver ids are more. Please help.

Friday, July 1, 2011 8:45 AM by rajani1986

# re: Passing lists to SQL Server 2005 with XML Parameters

Jon, thanks, absolutely fantastic!

i'd been puzzled with that stuff for the whole day until i found your solution!

Wednesday, February 1, 2012 1:34 AM by kostya

# re: Passing lists to SQL Server 2005 with XML Parameters

I used to be recommended this web site by way of my

cousin. I am now not positive whether this put up is written through him as nobody else understand such precise about my difficulty.

You are amazing! Thanks!

Sunday, August 19, 2012 11:31 AM by Pringle

# re: Passing lists to SQL Server 2005 with XML Parameters

How do I select only id from below xml...

I tried

SELECT ParamValues.ID.value('.','INT')

FROM @xml_eg.nodes('Products/id') as ParamValues(ID)  , but ended up getting 3 rows with NULL values.

declare @xml_eg xml

select @xml_eg =

'<Products>

<id>3<name>Joe</name>

<age>22</age>

</id>

<id>6<name>mike</name>

<age>50</age>

</id>

<id>15<name/>

<age/>

</id>

</Products>'

Thanks in advance

Friday, September 7, 2012 5:40 AM by Nayana

# re: Passing lists to SQL Server 2005 with XML Parameters

Major thankies for the article post.Thanks Again. Want more.

Tuesday, November 6, 2012 9:11 AM by ordermycheck

# re: Passing lists to SQL Server 2005 with XML Parameters

Great, thanks for sharing this blog.Really looking forward to read more. Fantastic.

Wednesday, November 7, 2012 8:08 PM by fashion