使用外键批量插入嵌套的xml作为第一个表的标识列

无论如何，这不可能一次完成，因为您不能在同一DML语句中插入两个表中（当然，在Triggers和OUTPUT子句之外，这两个都不起作用）。但是可以通过两次有效地完成。关键是该<Name>元素内的事实<Record>是唯一的，因为这使我们可以将该Record表用作第二遍的查找表（即，当我们获取Artist行时）。

首先，您需要（当然，应该）创建UNIQUE INDEXon Record (Name ASC)。在下面的示例中，我使用UNIQUE CONSTRAINT，但这仅是由于我使用表变量而不是临时表来使示例代码更易于重新运行（顶部不需要显式的IF EXISTS DROP）。该索引将有助于第二遍的性能。

该示例使用OPENXML，.nodes()因为与同一函数相比，它很有可能会更高效，因为同一文档需要遍历两次。该OPENXML函数的最后一个参数，用于2指定文档是“基于元素”的，因为默认解析正在寻找“基于属性”。

DECLARE @DocumentID INT, @ImportData XML;

SET @ImportData = N'
<Records>
  <Record>
    <Name>Best of Pop</Name>
    <Studio>ABC studio</Studio>
    <Artists>
      <Artist>
        <ArtistName>John</ArtistName>
        <Age>36</Age>            
      </Artist> 
      <Artist>
        <ArtistName>Jessica</ArtistName>
        <Age>20</Age>            
      </Artist>
    </Artists>
  </Record>
  <Record>
    <Name>Nursery rhymes</Name>
    <Studio>XYZ studio</Studio>
    <Artists>
      <Artist>
        <ArtistName>Judy</ArtistName>
        <Age>10</Age>            
      </Artist> 
      <Artist>
        <ArtistName>Rachel</ArtistName>
        <Age>15</Age>            
      </Artist>
    </Artists>
  </Record>
</Records>';


DECLARE @Record TABLE (RecordId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
                       Name NVARCHAR(400) UNIQUE,
                       Studio NVARCHAR(400));
DECLARE @Artist TABLE (ArtistId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
                       RecordId INT NOT NULL,
                       ArtistName NVARCHAR(400), Age INT);

EXEC sp_xml_preparedocument @DocumentID OUTPUT, @ImportData;

-- First pass: extract "Record" rows
INSERT INTO @Record (Name, Studio)
   SELECT Name, Studio
   FROM   OPENXML (@DocumentID, N'/Records/Record', 2) 
             WITH (Name    NVARCHAR(400)  './Name/text()', 
                   Studio  NVARCHAR(400)  './Studio/text()');


-- Second pass: extract "Artist" rows
INSERT INTO @Artist (RecordId, ArtistName, Age)
   SELECT rec.RecordId, art.ArtistName, art.Age
   FROM   OPENXML (@DocumentID, N'/Records/Record/Artists/Artist', 2) 
             WITH (Name        NVARCHAR(400)  '../../Name/text()',
                   ArtistName  NVARCHAR(400)  './ArtistName/text()', 
                   Age         INT  './Age/text()') art
   INNER JOIN @Record rec
           ON rec.[Name] = art.[Name];


EXEC sp_xml_removedocument @DocumentID;
-------------------

SELECT * FROM @Record ORDER BY [RecordID];
SELECT * FROM @Artist ORDER BY [RecordID];

参考：

• OPENXML
• sp_xml_prepare文档
• sp_xml_removedocument

编辑：
使用.nodes()功能代替的新要求OPENXML，以下将起作用：

DECLARE @ImportData XML;

SET @ImportData = N'
<Records>
  <Record>
    <Name>Best of Pop</Name>
    <Studio>ABC studio</Studio>
    <Artists>
      <Artist>
        <ArtistName>John</ArtistName>
        <Age>36</Age>            
      </Artist> 
      <Artist>
        <ArtistName>Jessica</ArtistName>
        <Age>20</Age>            
      </Artist>
    </Artists>
  </Record>
  <Record>
    <Name>Nursery rhymes</Name>
    <Studio>XYZ studio</Studio>
    <Artists>
      <Artist>
        <ArtistName>Judy</ArtistName>
        <Age>10</Age>            
      </Artist> 
      <Artist>
        <ArtistName>Rachel</ArtistName>
        <Age>15</Age>            
      </Artist>
    </Artists>
  </Record>
</Records>';

IF (OBJECT_ID('tempdb..#Record') IS NOT NULL)
BEGIN
   DROP TABLE #Record;
END;
IF (OBJECT_ID('tempdb..#Artist') IS NOT NULL)
BEGIN
   DROP TABLE #Artist;
END;

CREATE TABLE #Record (RecordId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
                      Name NVARCHAR(400) UNIQUE,
                      Studio NVARCHAR(400));
CREATE TABLE #Artist (ArtistId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
                      RecordId INT NOT NULL,
                      ArtistName NVARCHAR(400),
                      Age INT);


-- First pass: extract "Record" rows
INSERT INTO #Record (Name, Studio)
   SELECT col.value(N'(./Name/text())[1]', N'NVARCHAR(400)') AS [Name],
          col.value(N'(./Studio/text())[1]', N'NVARCHAR(400)') AS [Studio]
   FROM   @ImportData.nodes(N'/Records/Record') tab(col);


-- Second pass: extract "Artist" rows
;WITH artists AS
(
   SELECT col.value(N'(../../Name/text())[1]', N'NVARCHAR(400)') AS [RecordName],
          col.value(N'(./ArtistName/text())[1]', N'NVARCHAR(400)') AS [ArtistName],
          col.value(N'(./Age/text())[1]', N'INT') AS [Age]
   FROM   @ImportData.nodes(N'/Records/Record/Artists/Artist') tab(col)
)
INSERT INTO #Artist (RecordId, ArtistName, Age)
   SELECT rec.RecordId, art.ArtistName, art.Age
   FROM artists art
   INNER JOIN #Record rec
           ON rec.[Name] = art.RecordName;

-- OR --
-- INSERT INTO #Artist (RecordId, ArtistName, Age)
   SELECT rec.RecordId,
          col.value(N'(./ArtistName/text())[1]', N'NVARCHAR(400)') AS [ArtistName],
          col.value(N'(./Age/text())[1]', N'INT') AS [Age]
   FROM   @ImportData.nodes(N'/Records/Record/Artists/Artist') tab(col)
   INNER JOIN #Record rec
           ON rec.Name = col.value(N'(../../Name/text())[1]', N'NVARCHAR(400)');

-------------------

SELECT * FROM #Record ORDER BY [RecordID];
SELECT * FROM #Artist ORDER BY [RecordID];

#Artist上面显示了两个插入选项。第一个使用CTE从INSERT / SELECT查询中提取XML提取。另一个是简化版本，类似于问题UPDATE 2中的查询。