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

我有一个 xml 如下：

<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>

该文件可能包含数百万条记录。我的 MS SQL 数据库，运行于Azure SQL 数据库，有以下2个表来存储这些记录：

1. Record(RecordId[PK,身份,自增],姓名,工作室)

2. Artist(RecordId [外键指 Record.RecordId], ArtistName, Age)

是否可以批量插入记录Record表，获取RecordIds，然后将艺术家信息批量插入到Artist在单个表中使用xml节点遍历xml的方法？

我长期以来一直在寻找一种有效的方法来做到这一点，但徒劳无功。

我尝试过类似于所描述的方法here and here，但我无法找到解决方案。

任何指向解决方案方向的指针都会有很大帮助。

Update: @srutzky：感谢您的解决方案。这完全符合我的要求。但有一个问题。我必须使用节点方法来解决问题。我已经更改了查询的第一部分。但我陷入了下半场。这就是我所做的。

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);

INSERT INTO @Record (Name, Studio)
   SELECT  T.c.value(N'(Name/text())[1]', 'NVARCHAR(400)'),
           T.c.value(N'(Studio/text())[1]', 'NVARCHAR(400)')
 FROM @ImportData.nodes('/Records/Record') T(c);

SELECT * FROM @Record

你能帮我完成第二部分吗？我对这种 xml 处理方法很陌生。

UPDATE2：我明白了......我绞尽脑汁几个小时，尝试了一些方法，终于找到了解决方案。

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);

INSERT INTO @Record (Name, Studio)
   SELECT  T.c.value(N'(Name/text())[1]', 'NVARCHAR(400)'),
           T.c.value(N'(Studio/text())[1]', 'NVARCHAR(400)')
 FROM @ImportData.nodes('/Records/Record') T(c);

INSERT INTO @Artist (RecordId, ArtistName, Age)
    SELECT  (SELECT RecordId FROM @Record WHERE Name=T.c.value(N'(../../Name/text())[1]', 'NVARCHAR(400)')),
            T.c.value(N'(ArtistName/text())[1]', 'NVARCHAR(400)'),
           T.c.value(N'(Age/text())[1]', 'INT')
 FROM @ImportData.nodes('/Records/Record/Artists/Artist') T(c);

 SELECT * FROM @Record
 SELECT * FROM @Artist

@srutzky：非常感谢您为我指明了正确的方向。欢迎提出任何改进此解决方案的建议。

无论如何，这不能在一次传递中完成，因为您不能在同一个 DML 语句中插入两个表（好吧，在触发器和 OUTPUT 子句之外，这两者都没有帮助）。但它可以通过两次有效地完成。事实在<Name>内的元素<Record>唯一是关键，因为这使我们能够使用Record表作为第二遍的查找表（即当我们得到Artist rows).

首先，你需要（嗯，should） 创建一个UNIQUE INDEX on 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_preparedocument
• sp_xml_删除文档

EDIT:
随着新要求的使用.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 将 XML 提取从 INSERT / SELECT 查询中抽象出来。另一个是简化版本，类似于您的查询UPDATE 2的问题。