SQL Stored Procedure To Insert XML String

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