ASP.NET - how to use xml to pass multiple value in single parameter in stored procedure

Asked By bhanupratap singh on 17-May-13 08:16 AM
how to use xml to pass multiple value in single parameter in stored procedure
I need to pass multiple value like branches. user select many branch to get a report. So how to pass
these multiple value to stored procedure using xml and get result back in gridview
thanks
any one pls help me out
Robbe Morris replied to bhanupratap singh on 17-May-13 08:26 AM
As a general rule, working with XML in stored procedures is slow and the code is messy.  Are you sure table value parameters are not a better option?


http://www.nullskull.com/a/1589/sql-server-table-valued-parameters--types--multiple-row-inserts.aspx

If that really isn't an option, you might want to look into using the CLR in stored procedures:

http://www.nullskull.com/articles/sql_server_2005_clr_regex.asp
Sandeep Mittal replied to bhanupratap singh on 18-May-13 02:27 AM
Refer below example

CREATE PROCEDURE proc1 @xml XML
AS
BEGIN
  DECLARE @Id INT, @val VARCHAR(10)
  SELECT  @Id = Node.Data.value('(Id)[1]', 'INT'), @val = Node.Data.value('(val)[1]', 'VARCHAR(MAX)')
  FROM    @xml.nodes('/Root') Node(Data)
  SELECT  @Id, @val
END
  
DECLARE @xml XML
SET @xml = '<Root><Id>1</Id><val>abc</val></Root>'
EXEC proc1 @xml

Also refer this link

http://www.itdeveloperzone.com/2012/01/convert-xml-to-table-in-sql-server-2005.html