Hello and Welcome guys, here I am going to discuss with you about how we can pass XML as node value to SQL Stored Procedure to pass multiple value at one time no using any loop and all.
See how it works..let me know your opinion and comments. I like to hear and improve my writing skills.
--CREATE Stored Procedure as xml parameter
CREATE PROCEDURE uspParseXMLTOSP(@myXML xml)
CREATE PROCEDURE uspParseXMLTOSP(@myXML xml)
AS
BEGIN
--Create @tempTable with Column ID, NAME and PRICE –table variable
DECLARE @tempTable
TABLE (ID int, NAME VARCHAR(50), PRICE FLOAT)
INSERT INTO @tempTable (ID, NAME, PRICE)
SELECT
prodXML.value('(id)[1]', 'int') as 'ID',
prodXML.value('(name)[1]', 'Varchar(50)') as 'Name',
prodXML.value('(price)[1]', 'varchar(50)') as 'PRICE'
FROM
@myXML.nodes('/Products/Details') as Products(prodXML)
--Get Data from Table
SELECT * FROM @tempTable
END
--Run Stored Procedure as xml parameter
--
--Products
--Details
--id 1 id
--name ABC name
--price 125.00 price
--Details
--Products
EXEC
uspParseXMLTOSP @myXML='your xml'
Comments