Read Data From XML String in SQL XML String
<MergedData>
<Module>
<ModuleName>McAfee.AR.Dal.ARProcess.dll</ModuleName>
<LinesCovered>399</LinesCovered>
<LinesPartiallyCovered>0</LinesPartiallyCovered>
<LinesNotCovered>76</LinesNotCovered>
<BlocksCovered>455</BlocksCovered>
<BlocksNotCovered>75</BlocksNotCovered>
</Module>
</MergedData>
Stored Procedure To Read XML String and Insert To Table
Variable (@mergeddata is going to containg xml string IF(@mergeddata IS NOT NULL)
BEGIN
DECLARE @DocHandle INT
EXEC SP_XML_PREPAREDOCUMENT @DocHandle OUTPUT, @mergeddata
DECLARE @blocksCovered INT
DECLARE @blocksNotCovered int
DECLARE @coveredLines int
DECLARE @notCoveredLines int
DECLARE @partiallyCoveredLines INT
DECLARE @module_name VARCHAR(200)
DECLARE MERGECURSOR CURSOR FOR
SELECT BlocksCovered
,BlocksNotCovered
,LinesCovered
,LinesNotCovered
,LinesPartiallyCovered
,ModuleName
FROM OPENXML(@DocHandle,'MergedData/Module',2)
WITH(BlocksCovered INT
,BlocksNotCovered int
,LinesCovered int
,LinesNotCovered int
,LinesPartiallyCovered int
,ModuleName VARCHAR(200)
)
if(@@ERROR <>0)
BEGIN
ROLLBACK TRAN
CLOSE MERGECURSOR
DEALLOCATE MERGECURSOR
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
END
OPEN MERGECURSOR
FETCH NEXT FROM MERGECURSOR INTO
@blocksCovered
,@blocksNotCovered
,@coveredLines
,@notCoveredLines
,@partiallyCoveredLines
,@module_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [test].[TestCodeCoverageMergedResult]
([test_run_merge_id]
,[blocks_covered]
,[blocks_not_covered]
,[covered_lines]
,[not_covered_lines]
,[partially_covered_lines],module_name)
VALUES
(@return
,@blocksCovered
,@blocksNotCovered
,@coveredLines
,@notCoveredLines
,@partiallyCoveredLines,@module_name)
IF(@@ERROR<> 0)
BEGIN
ROLLBACK TRAN
CLOSE MERGECURSOR
DEALLOCATE MERGECURSOR
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
RETURN
END
FETCH NEXT FROM MERGECURSOR INTO
@blocksCovered
,@blocksNotCovered
,@coveredLines
,@notCoveredLines
,@partiallyCoveredLines
,@module_name
END
CLOSE MERGECURSOR
DEALLOCATE MERGECURSOR
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
END