Showing posts with label How to update XML data in SQL Server using stored procedure?. Show all posts
Showing posts with label How to update XML data in SQL Server using stored procedure?. Show all posts

How to update XML data in SQL Server 2008 using stored procedure?

Step 1:

Create a stored procedure that would take the input as XML data  & then break down the data to columns depending upon the XML tags formed:

CREATE PROC spExtractDataFromXML
(
 @inputXML XML=''
)
AS
BEGIN
SET NOCOUNT ON;
  SELECT
    x.i.value('./EmpId[1]','int') AS 'EmpId',
    x.i.value('./EmpName[1]','varchar(100)') AS 'EmpName',
    x.i.value('./Address[1]','varchar(100)') AS 'Address'
  FROM
  @inputXML.nodes('/Info/Data') AS x(i)
END
GO


Step 2:

Now call the stored procedure we created & pass the XML string as input to the SP:

Exec spExtractDataFromXML 
<Info>
 <Data>
    <EmpId>100</EmpId>
    <EmpName>ABC</EmpName>
    <Address>My Address</Address>
 </Data>
<Data>
    <EmpId>101</EmpId>
    <EmpName>ABC1</EmpName>
    <Address>My Address1</Address>
 </Data>
<Data>
    <EmpId>102</EmpId>
    <EmpName>ABC2</EmpName>
    <Address>My Address2</Address>
 </Data>
</Info>


The output of the query would look like below, that is our desired output:

saveXMLData


Followers

Popular Posts