How to convert XML String to DataTable in Sql Server


DECLARE @CategoryLevel varchar(max)
SET @CategoryLevel = '<DocumentElement>
  <CategoryLevel>
    <Mapping_ID>0</Mapping_ID>
    <Level_ID>1</Level_ID>
    <Level_Description>LEVEL1</Level_Description>
    <Level_Score>10</Level_Score>
    <IsChecked>true</IsChecked>
  </CategoryLevel>
  <CategoryLevel>
    <Mapping_ID>0</Mapping_ID>
    <Level_ID>2</Level_ID>
    <Level_Description>LEVEL2</Level_Description>
    <Level_Score>30</Level_Score>
    <IsChecked>true</IsChecked>
  </CategoryLevel>
</DocumentElement>'


 DECLARE @handle INT 
DECLARE @PrepareXmlStatus INT 

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @CategoryLevel 
DECLARE @TblLevel as table(Mapping_ID BIGINT,
    Level_ID BIGINT,
    Level_Description NVARCHAR(256),
    Level_Score INT,
    IsChecked BIT)

INSERT INTO @TblLevel
SELECT  *
FROM    OPENXML(@handle, '/DocumentElement/CategoryLevel', 2) 
    WITH (
    Mapping_ID BIGINT,
    Level_ID BIGINT,
    Level_Description NVARCHAR(256),
    Level_Score INT,
    IsChecked BIT
    ) 


EXEC sp_xml_removedocument @handle

SELECT * FROM @TblLevel

Comments

Popular posts from this blog

Download Excel File using AJAX in JavaScript

How to Capture Image using web camera in Asp.net and C#.NET / VB.NET

How to Install GIT ?