Stored Procedure with XML as input

Arunsp16
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 19164 red flag
Rating: 5 out of 5  
 1 vote(s)

How to give Xml as input to Stored Procedure.

Introduction


In the following Stored Procedure article, i have written how to use XML as input to SP

Save XML data in a Table:

 

First step:

Create a table
Create table empxml(LastName nvarchar(255), FirstName nvarchar(255), EmployeeID int)
Second step:

create a stored procedure

Create Procedure spEmpXml @exml xml

as

begin

Insert into empxml(LastName,FirstName,EmployeeID)

Select p.value('@LastName','nvarchar(255)')as LastName,

       p.value('@FirstName','nvarchar(255)')as FirstName,

       p.value('@EmployeeID','int')as EmployeeID from @exml.nodes('/employeeData/employee') as abc(p)

end

In the above insert query, the "empxml" table number of columns and  "exml "Xml field should match.
"nodes" represent the xml fields. "p.Value" used to take xml data's.

Third step:

Execute the stored procedure

Exec spEmpXml @exml = '<employeeData>

                  <employee LastName="Smith" FirstName="Randolph" EmployeeID="25000"/>

                  <employee LastName="Jack" FirstName="Wayn" EmployeeID="45000"/>

               </employeeData>'

Result will be:

LastName  FirstName   EmployeeID
Smith         Randolph      25000
Jack           Wayn          45000

Save XML data in temporary Table:

Create a stored procedure

Create Proc teeXml @prdxml xml

as

begin

Select x.value('.','VARCHAR(5)') as id from @prdxml.nodes('/Products/id') as params(x)

end

Execute SP and give XML input

Exec teeXml @prdxml ='<Products><id>3</id><id>6</id><id>15</id></Products>'

Result will be:

ID
3
6
15

Conclusion


I hope this article will help for beginners to understand how to use XML as input.

Thank you and have a great day.

Regards,
ArunKumar SP
Page copy protected against web site content infringement by Copyscape

About the Author

Arunsp16
Full Name: Arun Kumar
Member Level: Starter
Member Status: Member
Member Since: 6/6/2011 7:14:04 AM
Country: India
http://arunspdreamz.blogspot.com/
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)