How to give Xml as input to Stored Procedure.
In the following Stored Procedure article, i have written how to use XML as input to SP
Save XML data in a Table:
Create a table
Create table empxml(LastName nvarchar(255), FirstName nvarchar(255), EmployeeID int)
create a stored procedure
Create Procedure spEmpXml @exml xml
Insert into empxml(LastName,FirstName,EmployeeID)
Select p.value('@LastName','nvarchar(255)')as LastName,
p.value('@EmployeeID','int')as EmployeeID from @exml.nodes('/employeeData/employee') as abc(p)
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.
Execute the stored procedure
Exec spEmpXml @exml = '<employeeData>
<employee LastName="Smith" FirstName="Randolph" EmployeeID="25000"/>
<employee LastName="Jack" FirstName="Wayn" EmployeeID="45000"/>
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
beginSelect x.value('.','VARCHAR(5)') as id from @prdxml.nodes('/Products/id') as params(x)
Execute SP and give XML input
Exec teeXml @prdxml ='<Products><id>3</id><id>6</id><id>15</id></Products>'
Result will be:
I hope this article will help for beginners to understand how to use XML as input.
Thank you and have a great day.