大容量插入具有外键作为第一个表的标识列的嵌套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个表来存储这些记录：

Record（RecordId[PK，标识，自动递增]，名称，工作室）
Artist（RecordId[外键表示Record.RecordId]，ArtistName，Age）

是否可以使用xml节点方法在一次xml遍历中将记录大容量插入Record表，获取RecordId，然后将艺术家信息大容量插入到Artist表？

很长一段时间以来，我一直在寻找一种有效的方法来做到这一点，但没有成功。

我尝试过类似于这里和这里描述的方法，但我无法找到解决方案。

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

更新：@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处理方法的新手。

更新2：我明白了……我绞尽脑汁了几个小时，尝试了一些事情，终于找到了解决方案。

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

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

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

首先，您需要（好吧，应该）在Record (Name ASC)上创建一个UNIQUE INDEX。在下面的示例中，我使用了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_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中的查询。

相关内容

最新更新

热门标签：