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@productIdsxmlSET@productIds=' 3 6 15 '
　　SELECTParamValues.ID.value('.','VARCHAR(20)')FROM@productIds.nodes('/Products/id') asParamValues(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.
　　CREATEPROCEDURESelectByIdList(@productIdsxml) AS
　　DECLARE@ProductsTABLE(ID int) INSERTINTO@Products(ID) SELECTParamValues.ID.value('.','VARCHAR(20)')FROM@productIds.nodes('/Products/id') asParamValues(ID) SELECT*FROMProductsINNERJOIN
　　@ProductspONProducts.ProductID =p.ID
　　Now we can call it as follows:
　　
　　EXECSelectByIdList @productIds=' 3 6 15 '
　　
　　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 Boyse 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, Stevewas handling the application code, and I talked him into doing this via quick and dirty string concatenation. His method worked great:
　　
　　publicstaticstringBuildXmlString(stringxmlRootName, string[] values)
　　{
　　StringBuilder xmlString =newStringBuilder();
　　xmlString.AppendFormat("<{0}>", xmlRootName);for(inti =0;i
　　{
　　xmlString.AppendFormat("
?,>　　}
　　xmlString.AppendFormat(" ", xmlRootName);returnxmlString.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.
　　PublishedFriday, February 16, 2007 1:01 AM by Jon Galloway
　　Filed under: SQL, SQL Server
　　 Comments
　　#This Old Code - Passing lists to SQL Server 2005 with XML ParametersFriday, February 16, 2007 10:47 AM by This Old Code - Passing lists to SQL Server 2005 with XML Parameters
　　#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("' /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?Friday, March 02, 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 02, 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 08, 2007 5:29 AM by GH
　　#re: Passing lists to SQL Server 2005 with XML Parameters
　　Hi,
　　I used another technique to pass multiple values: Using a comma delimited string with id's as input parameter for a SQL query (blog.krisvandermast.com/UsingACommaDelimitedStringWithIdsAsInputParameterForASQLQuery.aspx).
　　Grz, Kris.Friday, May 18, 2007 7:50 AM by Kris van der Mast
　　#Passing lists to SQL Server 2005 using XML ??? innerlogic
　　Pingback from Passing lists to SQL Server 2005 using XML ??? innerlogicThursday, June 07, 2007 11:41 AM by Passing lists to SQL Server 2005 using XML ??? innerlogic
　　#re: Passing lists to SQL Server 2005 with XML Parameters
　　How can I pass multiple nodes into a table like
　　 3 Joe 22 6 mike 50 15 'Tuesday, June 19, 2007 1:29 PM by mike
　　#NHibernate's Xml In
　　NHibernate's Xml InWednesday, June 20, 2007 11:33 PM by Ayende @ Rahien
　　#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
　　#Have you ever wanted to pass an array of values into a sproc? XML is your friend
　　Have you ever wanted to pass an array of values into a sproc? XML is your friendTuesday, July 03, 2007 9:08 PM by Kevin Isom
　　#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
　　There is a simple method.
　　Just pass in a comma delimited string of id's: @ids = '3,5,7,8'
　　Change it to have commas at the front and back: SET @ids = ',' + @ids + ','
　　It will now look like this: ',3,5,7,8,'
　　To get your results, use something like: SELECT * FROM Products WHERE @ids LIKE '%,' + ID + ',%'Thursday, August 30, 2007 3:21 PM by Paul
　　#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'
　　 '
　　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 &)
　　<(should be <)
>　　>(should be >)
　　You can do that with a simple string.Replace call:
　　xmlString = xmlString.Replace("&", "&").Replace("<", "<").Replace(">", ">")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
　　I have a large block of xml I want to pass in as the @productsId from my .Net code. I can get the code to work fine when I use the sample xml posted here:
　　xmlString = ' 3 6 15 '
　　However, my xml string (which gets pulled from a table in the db, which is one large xml document) is much larger and contains values with special characters (", ', &, ...).
　　Is there an encoding method, I need to use in order to encode my xml string and pass it to the stored proc.?Sunday, October 14, 2007 6:24 PM by lance
　　 Leave a Comment
　　Title(required)
　　Name(required)
　　Your URL(optional)
　　Comments(required)
　　
　　Remember Me?
　　

本文转自
http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx