SQL Stored Procedure To Insert XML String

Manojjupally
Posted by Manojjupally under Sql Server category on | Points: 40 | Views : 1304
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

Comments or Responses

Login to post response