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

/
2 Comments

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




About Post Author

Microsoft Certified SQL Server Developer

You may also like

2 comments:

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.

Followers

Popular Posts