Stored Procedure with XML as input

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

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.