SQL-MERGE INTO-从带有子级的XML

Question

我正试图编写一个SQL脚本来从XML导入数据。在数据库中，我准备了两张表：*具有以下字段的代码组：代码/描述

• 具有以下字段的CodeValue：Code/Description/CodeGroupId(FK-int(=>1 CodeGroup有许多CodeValue

下面是XML:的一部分

<MASTERDATA plant="SXB">
<CODEGROUPS>
<CODEGROUPC><CODEGROUP>ANODE</CODEGROUP><CODEGROUPX>Condition of anodes</CODEGROUPX>
<CODEVALUES>
<CODEVALUEC><CODEVALUE>02AD</CODEVALUE><CODEVALUEX>2-Minor Depletion / Damage</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>03AD</CODEVALUE><CODEVALUEX>3-Major Depletion / Plan to refurbish</CODEVALUEX></CODEVALUEC>
</CODEVALUES>
</CODEGROUPC>
<CODEGROUPC><CODEGROUP>AUTO</CODEGROUP><CODEGROUPX>Measurement method</CODEGROUPX>
<CODEVALUES>
<CODEVALUEC><CODEVALUE>00ND</CODEVALUE><CODEVALUEX>0-Inspection Not Done/not inspectable</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>01AU</CODEVALUE><CODEVALUEX>Automatic</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>01MA</CODEVALUE><CODEVALUEX>Manual</CODEVALUEX></CODEVALUEC>
</CODEVALUES>
</CODEGROUPC>
</CODEGROUPS>
</MASTERDATA>

我不知道是否可以在单个MERGE INTO语句中写入在CodeGroup和CodeValue:中插入值的能力

MERGE INTO [dbo].[CodeGroup] AS TARGET
USING
(
SELECT DISTINCT
d.x.value('../../@plant[1]', 'nvarchar(15)') as PlantId,
d.x.value('INGRP[1]', 'nchar(3)') as Code,
d.x.value('INGRPX[1]', 'nvarchar(20)') as Name
FROM @data.nodes('/MASTERDATA/CODEGROUPS/CODEGROUPC')as d(x)
)
AS SOURCE ON (SOURCE.Code= TARGET.Code
AND SOURCE.PlantId = TARGET.PlantId)
WHEN MATCHED
THEN UPDATE SET SOURCE.Name= TARGET.Name
WHEN NOT MATCHED BY TARGET
THEN INSERT (PlantId, Code, Name)
VALUES (SOURCE.PlantId, SOURCE.Code, SOURCE.Name)
WHEN NOT MATCHED BY SOURCE AND (TARGET.PlantId in (SELECT PlantId FROM #TempTable))
THEN DELETE;

这就是我准备的，但我没有找到如何和"孩子"(CodeGroup的CodeValue(感谢您的帮助

Answer 1

您针对的是两个不同的表。这不能在一句话中完成。

试试这个代码：

DECLARE @xml XML=
'<MASTERDATA plant="SXB">
<CODEGROUPS>
<CODEGROUPC><CODEGROUP>ANODE</CODEGROUP><CODEGROUPX>Condition of anodes</CODEGROUPX>
<CODEVALUES>
<CODEVALUEC><CODEVALUE>02AD</CODEVALUE><CODEVALUEX>2-Minor Depletion / Damage</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>03AD</CODEVALUE><CODEVALUEX>3-Major Depletion / Plan to refurbish</CODEVALUEX></CODEVALUEC>
</CODEVALUES>
</CODEGROUPC>
<CODEGROUPC><CODEGROUP>AUTO</CODEGROUP><CODEGROUPX>Measurement method</CODEGROUPX>
<CODEVALUES>
<CODEVALUEC><CODEVALUE>00ND</CODEVALUE><CODEVALUEX>0-Inspection Not Done/not inspectable</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>01AU</CODEVALUE><CODEVALUEX>Automatic</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>01MA</CODEVALUE><CODEVALUEX>Manual</CODEVALUEX></CODEVALUEC>
</CODEVALUES>
</CODEGROUPC>
</CODEGROUPS>
</MASTERDATA>';
SELECT @xml.value('(/MASTERDATA/@plant)[1]','varchar(100)') AS Masterdata_plant
,cGr.value('(CODEGROUP/text())[1]','varchar(100)') AS CodeGroup
,cGr.value('(CODEGROUPX/text())[1]','varchar(100)') AS CodeGroupX
,cVl.value('(CODEVALUE/text())[1]','varchar(100)') AS CodeValue
,cVl.value('(CODEVALUEX/text())[1]','varchar(100)') AS CodeValueX
INTO #tmpXmlData
FROM @xml.nodes('/MASTERDATA/CODEGROUPS/CODEGROUPC') A(cGr)
OUTER APPLY cGr.nodes('CODEVALUES/CODEVALUEC') B(cVl);
SELECT * FROM #tmpXmlData;

使用在#tmpXmlData中找到的结果分别对两个表运行MERGE语句。

Answer 2

Shnugo完成了挑战，他说在一个MERGE语句中不可能做到这一点。我认为他是对的。然而，它让我思考，因为我已经多次使用OUTPUT子句来解决这类问题，我认为这是一个MASTER-DETAIL合并，当主表的键不是业务键并且是IDENTITY()或NEWID()时。

做你想做的事情最有效的方法是什么？Shnugo给了你一个部分解决方案，所以我增强了它(感谢Shnugo(，给了你想要的东西，但它使用了一个INSERT到临时表，然后使用了两个MERGE语句。一个用于主表，一个用于明细表。

第一个MERGE使用了一个技巧，允许您检索新行的新IDENTITY字段，这样在创建明细表时就不必通过业务键连接回主表。我希望这能有所帮助。

这是我的代码：

假设：

我在好几个领域都写了NOT NULLs。我对外键引用进行了级联删除。

create table dbo.CodeGroup (
CodeGroupId int identity(1,1) primary key,
Masterdata_plant varchar(100) not null,
Code varchar(100) not null,
Description varchar(100) not null
)
go
CREATE NONCLUSTERED INDEX [IX_CodeGroup] ON dbo.CodeGroup
(
[Masterdata_plant] ASC,
[Code] ASC
)
go
create table dbo.CodeValue 
(
Code varchar(100) not null,
CodeGroupId int not null,
Description varchar(100) not null,
CONSTRAINT PK_CodeValue PRIMARY KEY CLUSTERED 
(
Code ASC,
CodeGroupId ASC
)
)
go
ALTER TABLE dbo.CodeValue  WITH CHECK ADD  CONSTRAINT FK_CodeValue_CodeGroup FOREIGN KEY(CodeGroupId)
REFERENCES dbo.CodeGroup (CodeGroupId)
ON DELETE CASCADE
go
ALTER TABLE dbo.CodeValue CHECK CONSTRAINT FK_CodeValue_CodeGroup
go
delete from dbo.CodeGroup
go
delete from dbo.CodeValue
go
DECLARE @data XML=
'<MASTERDATA plant="SXB">
<CODEGROUPS>
<CODEGROUPC><CODEGROUP>ANODE</CODEGROUP><CODEGROUPX>Condition of anodes</CODEGROUPX>
<CODEVALUES>
<CODEVALUEC><CODEVALUE>02AD</CODEVALUE><CODEVALUEX>2-Minor Depletion / Damage</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>03AD</CODEVALUE><CODEVALUEX>3-Major Depletion / Plan to refurbish</CODEVALUEX></CODEVALUEC>
</CODEVALUES>
</CODEGROUPC>
<CODEGROUPC><CODEGROUP>AUTO</CODEGROUP><CODEGROUPX>Measurement method</CODEGROUPX>
<CODEVALUES>
<CODEVALUEC><CODEVALUE>00ND</CODEVALUE><CODEVALUEX>0-Inspection Not Done/not inspectable</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>01AU</CODEVALUE><CODEVALUEX>Automatic</CODEVALUEX></CODEVALUEC>
<CODEVALUEC><CODEVALUE>01MA</CODEVALUE><CODEVALUEX>Manual</CODEVALUEX></CODEVALUEC>
</CODEVALUES>
</CODEGROUPC>
</CODEGROUPS>
</MASTERDATA>'
if object_id('tempdb..#tmpXmlData') is not null Begin
drop table #tmpXmlData
End
create table #tmpXmlData (
ExistingCodeGroupId int,
Plant varchar(100),
CodeGroup varchar(100),
Description varchar(100),
CodeValue varchar(100),
CodeValueDescription varchar(100)
)
insert into #tmpXmlData
SELECT gc.CodeGroupId
,@data.value('(/MASTERDATA/@plant)[1]','varchar(100)') AS Plant
,cGr.value('(CODEGROUP/text())[1]','varchar(100)') AS CodeGroup
,cGr.value('(CODEGROUPX/text())[1]','varchar(100)') AS Description
,cVl.value('(CODEVALUE/text())[1]','varchar(100)') AS CodeValue
,cVl.value('(CODEVALUEX/text())[1]','varchar(100)') AS CodeValueDescription
FROM @data.nodes('/MASTERDATA/CODEGROUPS/CODEGROUPC') A(cGr)
OUTER APPLY cGr.nodes('CODEVALUES/CODEVALUEC') B(cVl)
left join CodeGroup gc
on gc.Masterdata_plant = @data.value('(/MASTERDATA/@plant)[1]','varchar(100)')
and gc.Code = cGr.value('(CODEGROUP/text())[1]','varchar(100)')
SELECT * FROM #tmpXmlData;
if object_id('tempdb..#IDs') is not null Begin
drop table #IDs
end
create table #IDs(
CodeGroupID int,
Plant varchar(100),
Code varchar(100),
Description varchar(100)
)
go
CREATE NONCLUSTERED INDEX [IX_CodeGroup] ON #IDs
(
[Plant] ASC,
[Code] ASC
)
;MERGE INTO dbo.CodeGroup AS TARGET
USING
(
select ExistingCodeGroupId, Plant, CodeGroup,Description
from #tmpXmlData
group by ExistingCodeGroupId, Plant, CodeGroup,Description
)
AS SOURCE ON (SOURCE.ExistingCodeGroupId= TARGET.CodeGroupId)
--Plant, and CodeGroup must have matched already in left join above.
WHEN MATCHED
and TARGET.Description <> SOURCE.Description
THEN UPDATE SET TARGET.Description = SOURCE.Description
WHEN NOT MATCHED BY TARGET
THEN INSERT (Masterdata_plant, Code, Description)
VALUES (SOURCE.Plant, SOURCE.CodeGroup, SOURCE.Description)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
output inserted.*
into #IDs; --This gets us the new CodeGroupId's

select * from #IDs
;MERGE INTO dbo.CodeValue AS TARGET
USING
(
select CodeGroupID=coalesce(d.ExistingCodeGroupId,i.CodeGroupID), CodeValue, CodeValueDescription
from #tmpXmlData d
left join #IDs i
on i.Plant = d.Plant
and i.Code = d.CodeGroup
group by coalesce(d.ExistingCodeGroupId,i.CodeGroupID), CodeValue, CodeValueDescription
)
AS SOURCE ON (SOURCE.CodeGroupID= TARGET.CodeGroupID
and SOURCE.CodeValue = Target.Code)
WHEN MATCHED
and TARGET.Description <> SOURCE.CodeValueDescription
THEN UPDATE SET TARGET.Description = SOURCE.CodeValueDescription
WHEN NOT MATCHED BY TARGET
THEN INSERT (CodeGroupID, Code, Description)
VALUES (SOURCE.CodeGroupID, SOURCE.CodeValue, SOURCE.CodeValueDescription)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
output inserted.*;
select * from dbo.CodeGroup
select * from dbo.CodeValue