Monday, 19 November 2012

Read Xml Data in SQL Server Stored Procedure



CREATE PROCEDURE usp_Get_XMLdataRows
      @XmlData NVARCHAR(MAX)
AS
BEGIN
      DECLARE @IDoc INT
           
      EXEC sp_xml_preparedocument                     
             @IDoc OUTPUT,                     
             @XmlData
                      
      SELECT * INTO #PrimaryBeneficiaries
      FROM OPENXML(@Idoc, '/ArrayOfPrimaryBeneficiaries/PrimaryBeneficiaries', 2)
         WITH
         (
                  ID INT 'ID',           
                  BeneficiaryName NVARCHAR(MAX),
                  [Target] NVARCHAR(MAX),
                  AgeID INT,
                  SocialEconomicID INT,
                  LocationID INT
         )
     
      EXEC sp_xml_removedocument @IDoc
     
      SELECT * FROM #PrimaryBeneficiaries
END

Run Script:
 
EXEC usp_Get_XMLdataRows
'<ArrayOfPrimaryBeneficiaries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PrimaryBeneficiaries>
    <ID>166</ID>   
    <BeneficiaryName>Benficiary1</BeneficiaryName>
    <Target>123</Target>
    <AgeID>2</AgeID>
    <SocialEconomicID>2</SocialEconomicID>
    <LocationID>2</LocationID>
  </PrimaryBeneficiaries>
  <PrimaryBeneficiaries>
    <ID>167</ID>   
    <BeneficiaryName>Benficiary2</BeneficiaryName>
    <Target>234</Target>
    <AgeID>3</AgeID>
    <SocialEconomicID>3</SocialEconomicID>
    <LocationID>3</LocationID>
  </PrimaryBeneficiaries>
  <PrimaryBeneficiaries>
    <ID>168</ID>   
    <BeneficiaryName>Benficiary3</BeneficiaryName>
    <Target>345</Target>
    <AgeID>2</AgeID>
    <SocialEconomicID>2</SocialEconomicID>
    <LocationID>2</LocationID>
  </PrimaryBeneficiaries>
</ArrayOfPrimaryBeneficiaries>'


Output:


No comments:

Post a Comment