Passing lists to SQL Server 2005 with XML Parameters



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


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

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


Which gives us the following three rows:


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)

@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();

"<{0}>", xmlRootName);
for (int i = 0; i < values.Length; i++)
"<value>{0}</value>", values[i]);
"</{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.


  • 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("'\r\n\r\n\r\n\r\n'");
    If I pass a string like "Test" it works but not with XML.

    What could be possible?

  • @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.

  • 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?!

  • How can I pass multiple nodes into a table like

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

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


    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.

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

  • 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'
    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...

  • @Supriya - you need to escape the following characters:
    & (should be &amp;)
    (should be &gt;)

    You can do that with a simple string.Replace call:
    xmlString = xmlString.Replace("&", "&amp;").Replace("", "&gt;")

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

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

  • this is very good and informative


  • Hi,
    This is very good and helpfull.

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

  • @Pradeep You'll want to take a look at bulk copy for large XML sets. See Ayende's post:

  • 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



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

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

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

    /* Written by Ed Sanford of ODI Consulting (c) 2008 */
    @CSVStr varchar(Max)
    DECLARE @Result XML
    SELECT @Result = '' + Replace(@CSVStr,',','') + ''
    RETURN @Result

  • cool it works like charm

  • I have to pass the xml to the stored procedure from code.
    I have a string variable ids="55"

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

  • Hi,
    Oh, so many problems...

    Problem #1
    I have xml with multiple nodes something like this: 3Joe226mike5015'

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

    Any help is appreciated!


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

  • Great post!

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

    I have a solution with the following script

    @xml = 3Joe226mike5015

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

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



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

  • Hi channa,

    It worked like a charm.

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

    declare @ProductIDs xml
    set @ProductIDs = 'Joe22mike50'

    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)

  • 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 5ABC6PQR. Now I want to update the records for ID 5 and 6.

    Thanks in advance

  • HI,

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

    @xml = '3Joe226mike5015'

    Thanks in advance

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

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

    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

  • Hey Channa,

    Thanks for the info.

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

  • 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

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

  • How can i parse these date attributes

  • 3Joe226mike5015'

    How to insert?

  • Really it helped me a lot

  • Hi,

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

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

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

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

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

  • Great information! Thanks!

  • IT is great help to me.

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

  • Got this to work just be following all steps outline. very well written. thanks.
    Question: is this code susceptible to SQLInjection?

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

  • 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

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

  • Jon, thanks, absolutely fantastic!

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

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

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

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

Comments have been disabled for this content.