لطفا منتظر باشید ...
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,
'<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.
