Массовая вставка вложенного 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, работающая наБаза данных SQL Azure, имеет следующие 2 таблицы для хранения этих записей:
Record
(RecordId [PK, идентификатор, автоинкремент], имя, студия)
Artist
(RecordId [внешний ключ ссылается на Record.RecordId], ArtistName, Age)
Можно ли массово вставить записи вRecord
таблицу, получить RecordIds, а затем массово вставить информацию об исполнителе вArtist
таблица в одном обходе XML с использованием подхода узлов XML?
Я долго искал эффективный способ сделать это, но тщетно.
Я пробовал подходы, аналогичные описаннымВот а такжеВот, но я не могу найти решение.
Любые указатели в направлении решения будут очень полезны.
Обновить: @srutzky: Спасибо за решение. Это работает именно так, как я хотел. Но есть одна загвоздка. Я должен использовать подход узлов для решения. Я изменил первую часть запроса. Но я застрял во 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);
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: Спасибо большое за то, что указал мне правильное направление. Любые предложения по улучшению этого решения приветствуются.