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?

https://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:

https://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