Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

XML Bulk Dynamic Inserting Records

By gandhi.mk, 2008/11/22

Solution for dynamic bulk inserting records into appropriate tables from the XML files. The XML file structure should be in appropriate table structure.
In the first argument pass the XML file tags and second argument pass the table name.

Create  Procedure [dbo].[SP_UpdateTable_XML]
 ( @XMLText nvarchar(MAX), @TableName varchar(50) )
As         
Begin                
declare @sExe varchar(max)                
set @sExe =    'declare @idoc int; '+
 'EXEC sp_xml_preparedocument @idoc OUTPUT,''' 
 + @XMLText + ''';'+                                
 ' SELECT * FROM OPENXML (@idoc,''NewDataSet/' + @TableName + 
 '
 '',2) WITH  ' + @TableName + ';'+                                
 ' EXEC sp_xml_removedocument @idoc '                        

 exec (@sExe)       
 End

Total article views: 1746 | Views in the last 30 days: 1