TSQL 从 XML 字符串插入记录 [英] TSQL Inserting records from XML string
问题描述
我有一个 SQL 查询，它从我传递给它的 XML 字符串中将记录插入到表中.该字符串可以包含 1 个或多个节点，因此每个节点都是一条新记录.
I have a SQL query that is inserting records into a table from an XML string that I pass to it. The string could contain 1 node or multiple so each one is a new record.
这是我的 XML 字符串:
Here is my XML string:
<root>
<data>
<segment>
<trainingEventID>9</trainingEventID>
<localeID>641</localeID>
<numOfTeammates>12</numOfTeammates>
<nonProdHrs>21</nonProdHrs>
<segmentDate>10/10/2014</segmentDate>
<trainers>
<trainer>
<empID>HUS123</empID>
</trainer>
<trainer>
<empID>Dan123</empID>
</trainer>
</trainers>
</segment>
</data>
<data>
<segment>
<trainingEventID>9</trainingEventID>
<localeID>641</localeID>
<numOfTeammates>12</numOfTeammates>
<nonProdHrs>21</nonProdHrs>
<segmentDate>10/25/2014</segmentDate>
<trainers>
<trainer>
<empID>HUS123</empID>
</trainer>
<trainer>
<empID>Dan123</empID>
</trainer>
</trainers>
</segment>
</data>
</root>
每个 segment 都是添加到表中的一条新记录.
Every segment is a new record that is added into the table.
现在，我有一个名为 trainers 的单独表.对于每个培训师，我还需要在该表中插入一条记录，但它需要具有该段的最后插入的记录 ID.
Now, I have a separate table called trainers. For each trainer, I need to also insert a record into that table but it needs to have the last inserted record id of the segment.
这是我的查询:
INSERT INTO myTable(trainingEventID, localeID, segmentDate, numofTeammates, nonProdHrs)
SELECT ParamValues.x1.value('trainingEventID[1]', 'INT'),
ParamValues.x1.value('localeID[1]', 'INT'),
ParamValues.x1.value('segmentDate[1]', 'DATE'),
ParamValues.x1.value('numOfTeammates[1]', 'INT'),
ParamValues.x1.value('nonProdHrs[1]', 'FLOAT')
FROM @xml.nodes('/root/data/segment') AS ParamValues(x1);
如何使用从段插入创建的记录 ID 将训练器插入到另一个表中?
How can I go about inserting the trainers into another table with the record ID that was created from the segment insert?
推荐答案
鉴于问题中对这一陈述的澄清:
Given the clarification of this statement in the question:
对于每个训练器，我还需要在该表中插入一条记录，但它需要具有该段的最后插入的记录 ID.
For each trainer, I need to also insert a record into that table but it needs to have the last inserted record id of the segment.
存在(如对问题的评论所见):
being (as found in the comments on the question):
总共有 4 条记录插入到 trainer 表中，其中 2 条的段 ID 为 1，另外 2 条的段 ID 为 2.
There would be a total of 4 records inserted into the trainer table, 2 have the segment id of 1 and the other 2 with the segment id of 2.
以下内容会将这些数据插入到具有自动递增 ID 的相关表中.在示例数据中，我稍微改变了 EmpID 值，以更清楚地表明它确实按预期工作.
The following will insert this data into related tables that have auto-incrementing IDs. In the sample data, I varied the EmpID values slightly to make it clearer that it is indeed working as expected.
DECLARE @DocumentID INT, @ImportData XML;
SET @ImportData = N'
<root>
<data>
<segment>
<trainingEventID>9</trainingEventID>
<localeID>641</localeID>
<numOfTeammates>12</numOfTeammates>
<nonProdHrs>21</nonProdHrs>
<segmentDate>10/10/2014</segmentDate>
<trainers>
<trainer>
<empID>HUS123</empID>
</trainer>
<trainer>
<empID>Dan123</empID>
</trainer>
</trainers>
</segment>
</data>
<data>
<segment>
<trainingEventID>9</trainingEventID>
<localeID>641</localeID>
<numOfTeammates>12</numOfTeammates>
<nonProdHrs>21</nonProdHrs>
<segmentDate>10/25/2014</segmentDate>
<trainers>
<trainer>
<empID>HUS1234</empID>
</trainer>
<trainer>
<empID>Dan1234</empID>
</trainer>
</trainers>
</segment>
</data>
</root>';
DECLARE @Segment TABLE (SegmentId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
TrainingEventID INT NOT NULL, -- Unique
LocaleID INT NOT NULL, -- Unique
NumOfTeammates INT,
NonProdHrs INT,
SegmentDate DATE); -- Unique
-- Ideally create UNIQUE INDEX with the 3 fields noted above
DECLARE @Trainer TABLE (TrainerId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
SegmentID INT NOT NULL, -- FK to Segment.SegmentID
EmpID VARCHAR(50) NOT NULL);
EXEC sp_xml_preparedocument @DocumentID OUTPUT, @ImportData;
-- First pass: extract "Segment" rows
INSERT INTO @Segment
(TrainingEventID, LocaleID, NumOfTeammates, NonProdHrs, SegmentDate)
SELECT TrainingEventID, LocaleID, NumOfTeammates, NonProdHrs, SegmentDate
FROM OPENXML (@DocumentID, N'/root/data/segment', 2)
WITH (TrainingEventID INT './trainingEventID/text()',
LocaleID INT './localeID/text()',
NumOfTeammates INT './numOfTeammates/text()',
NonProdHrs INT './nonProdHrs/text()',
SegmentDate DATE './segmentDate/text()');
-- Second pass: extract "Trainer" rows
INSERT INTO @Trainer (SegmentID, EmpID)
SELECT seg.SegmentID, trnr.EmpID
FROM OPENXML (@DocumentID, N'/root/data/segment/trainers/trainer', 2)
WITH (TrainingEventID INT '../../trainingEventID/text()',
LocaleID INT '../../localeID/text()',
SegmentDate DATE '../../segmentDate/text()',
EmpID VARCHAR(50) './empID/text()') trnr
INNER JOIN @Segment seg
ON seg.[TrainingEventID] = trnr.[TrainingEventID]
AND seg.[LocaleID] = trnr.[LocaleID]
AND seg.[SegmentDate] = trnr.[SegmentDate];
EXEC sp_xml_removedocument @DocumentID;
-------------------
SELECT * FROM @Segment ORDER BY [SegmentID];
SELECT * FROM @Trainer ORDER BY [SegmentID];
输出:
SegmentId TrainingEventID LocaleID NumOfTeammates NonProdHrs SegmentDate
1 9 641 12 21 2014-10-10
2 9 641 12 21 2014-10-25
TrainerId SegmentID EmpID
1 1 HUS123
2 1 Dan123
3 2 HUS1234
4 2 Dan1234
参考文献:
这篇关于TSQL 从 XML 字符串插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
