Inserting Data with OPENXML()

for RuBoard

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, '<songs>        <artist id="JHART" name="Johnny Hartman">        <song id="1" name="It Was Almost Like a Song"/>        <song id="2" name="I See Your Face Before Me"/>        <song id="3" name="For All We Know"/>        <song id="4" name="Easy Living"/>        </artist>        <artist id="HCONN" name="Harry Connick, Jr.">        <song id="1" name="Sonny Cried"/>        <song id="2" name="A Nightingale Sang in Berkeley Square"/>        <song id="3" name="Heavenly"/>        <song id="4" name="You Didn''t Know Me When"/>        </artist> </songs>' INSERT Artists (ArtistId, Name) SELECT id,name FROM OPENXML(@hdoc, '/songs/artist', 1) WITH (id varchar(5) '@id',       name varchar(30) '@name') INSERT Songs (ArtistId, SongId, Name) SELECT artistid, id,name FROM OPENXML(@hdoc, '/songs/artist/song', 1) WITH (artistid varchar(5) '../@id',       id int '@id',       name varchar(50) '@name') EXEC sp_xml_removedocument @hDoc GO SELECT * FROM Artists SELECT * FROM Songs GO DROP TABLE Artists, Songs 

(Results)

 ArtistId Name -------- ------------------------------ JHART    Johnny Hartman HCONN    Harry Connick, Jr. ArtistId SongId      Name -------- ----------- -------------------------------------------------- JHART    1           It Was Almost Like a Song JHART    2           I See Your Face Before Me JHART    3           For All We Know JHART    4           Easy Living HCONN    1           Sonny Cried HCONN    2           A Nightingale Sang in Berkeley Square HCONN    3           Heavenly HCONN    4           You Didn't Know Me When 

As you can see, we make a separate call to OPENXML() for each table. The tables are normalized; the XML document is not, so we shred it into multiple tables. Here's another way to accomplish the same thing that doesn't require multiple calls to OPENXML():

 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 CREATE VIEW ArtistSongs AS SELECT       a.ArtistId,       a.Name AS ArtistName,       s.SongId,       s.Name as SongName FROM Artists a JOIN Songs s ON (a.ArtistId=s.ArtistId) GO CREATE TRIGGER ArtistSongsInsert ON ArtistSongs INSTEAD OF INSERT AS INSERT Artists SELECT DISTINCT ArtistId, ArtistName FROM inserted INSERT Songs SELECT ArtistId, SongId, SongName FROM inserted GO DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, '<songs>        <artist id="JHART" name="Johnny Hartman">        <song id="1" name="It Was Almost Like a Song"/>        <song id="2" name="I See Your Face Before Me"/>        <song id="3" name="For All We Know"/>        <song id="4" name="Easy Living"/>        </artist>        <artist id="HCONN" name="Harry Connick, Jr.">        <song id="1" name="Sonny Cried"/>        <song id="2" name="A Nightingale Sang in Berkeley Square"/>        <song id="3" name="Heavenly"/>        <song id="4" name="You Didn''t Know Me When"/>        </artist> </songs>' INSERT ArtistSongs (ArtistId, ArtistName, SongId, SongName) SELECT artistid, artistname, songid, songname FROM OPENXML(@hdoc, '/songs/artist/song', 1) WITH (artistid varchar(5) '../@id',       artistname varchar(30) '../@name',       songid int '@id',       songname varchar(50) '@name') EXEC sp_xml_removedocument @hDoc GO SELECT * FROM Artists SELECT * FROM Songs GO DROP VIEW ArtistSongs GO DROP TABLE Artists, Songs 

(Results)

 ArtistId Name -------- ------------------------------ HCONN    Harry Connick, Jr. JHART    Johnny Hartman ArtistId SongId      Name -------- ----------- -------------------------------------------------- JHART    1           It Was Almost Like a Song JHART    2           I See Your Face Before Me JHART    3           For All We Know JHART    4           Easy Living HCONN    1           Sonny Cried HCONN    2           A Nightingale Sang in Berkeley Square HCONN    3           Heavenly HCONN    4           You Didn't Know Me When 

This technique uses a view and an INSTEAD OF trigger to alleviate the need for two passes with OPENXML(). We use a view to simulate the denormalized layout of the XML document, then set up an INSTEAD OF trigger to insert the data in the XML document "into"this view. The trigger performs the actual work of shredding , only it does it much more efficiently than calling OPENXML() twice. It makes two passes over the logical inserted table and splits the columns contained therein (which mirror those of the view) into two separate tables.

for RuBoard