Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Inserting Data with OPENXML()

Given that it's a rowset function, it's natural that you'd want to insert the results of a SELECT against OPENXML() into another table. There are a couple of ways of approaching this. First, you could execute a separate pass against the XML document for each piece of it that you wanted to extract. You would execute an INSERT…SELECT FROM OPENXML() for each database table that you wanted to insert rows into, grabbing a different section of the XML document with each pass. Here's an example of this approach:

 USE tempdb GO CREATE TABLE Artists (ArtistId varchar(5), Name varchar(30)) GO CREATE TABLE Songs (ArtistId varchar(5), SongId int, Name varchar(50)) GO DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, ...