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
Post a Comment