使用 SQL 将多个分组的 xml 文档插入到单个 xml 文档中

Question

我有两个表

tmpEntityAddress

EntityId   Address
________   _______
5          <Address />
5          <Address />
7          <Address />

tmpEntityAddresses(英语：tmpEntityAddresses

)

EntityId   XML
________   _______
5          <Addresses />
5          <Addresses />

我想将各种地址文档分组并插入到第二个表中的单个地址文档中。具有粗略的基本架构，如下所示：

<Addresses>
<Address>
<Street />
<PostCode />
</Address>
<Address>
<Street />
<PostCode />
</Address>
</Addresses>

我无法完全理解如何使用 XML DML 语言功能在 SQL 中执行此操作，例如 XML 插入等 https://learn.microsoft.com/en-us/sql/t-sql/xml/xml-data-modification-language-xml-dml

我在想我可以做到以下几点：

update tmpEntityAddresses
set
XML.modify('insert sql:column("Address") into (/Addresses)[1]'
from
tmpEntityAddresses
join #tmpEntityAddresses on tmpEntityAddresses.EntityId = tmpEntityAddress.EntityId

但它似乎只从 #tmpEntityAddress 中添加了一行，这不是我想要的，因为我需要整个集合。

这在SQL中可能吗？如果是这样，如何实现这一目标？

Answer 1

你是对的，你一次只能将.modify()用于一个操作......

以下代码具有一些假设：

1. 在您的示例中，Addresses中的第二行应具有EntityId=7
2. 两个表的 XML 中都有预先存在的内容

您可以使用可更新的 CTE。此 CTE 将选取Addresses列，并从Address表中为给定 ID 添加所有 XML 的计算列。

现在我们可以使用 modify，一次将所有组合的地址条目插入到一个操作中：

DECLARE @address TABLE (ID INT,[Address] XML);
INSERT INTO @address VALUES
(5, N'<Address id="5a" ><Street>Some Street</Street></Address>')
,(5, N'<Address id="5b" ><Street>Some Other</Street></Address>')
,(7, N'<Address id="7" ><Street>One More</Street></Address>');
DECLARE @addresses TABLE (ID INT,AddrXML XML);
INSERT INTO @addresses VALUES
(5, N'<Addresses><PreexistingContent>Blah</PreexistingContent></Addresses>')
,(7, N'<Addresses><PreexistingContent>Booh</PreexistingContent></Addresses>');
WITH CombinedAddress AS
(
SELECT adrs.ID
,(
SELECT adr.[Address]
FROM @address AS adr
WHERE adr.ID=adrs.ID
FOR XML PATH(''),TYPE
) AS Combined
,adrs.AddrXML
FROM @addresses AS adrs
)
UPDATE CombinedAddress
SET AddrXML.modify(N'insert sql:column("Combined") as last into (/Addresses)[1]');
SELECT * FROM @addresses 

ID=5 的结果

<Addresses>
<PreexistingContent>Blah</PreexistingContent>
<Address>
<Address id="5a">
<Street>Some Street</Street>
</Address>
</Address>
<Address>
<Address id="5b">
<Street>Some Other</Street>
</Address>
</Address>
</Addresses>

更新地址未嵌套

试试这个：

WITH CombinedAddress AS
(
SELECT adrs.ID
,(
SELECT adr.[Address] AS [*]
FROM @address AS adr
WHERE adr.ID=adrs.ID
FOR XML PATH(''),TYPE
) AS Combined
,adrs.AddrXML
FROM @addresses AS adrs
)
UPDATE CombinedAddress
SET AddrXML.modify(N'insert sql:column("Combined") as last into (/Addresses)[1]');

结果

<Addresses>
<PreexistingContent>Blah</PreexistingContent>
<Address id="5a">
<Street>Some Street</Street>
</Address>
<Address id="5b">
<Street>Some Other</Street>
</Address>
</Addresses>

Answer 2

您绝对正确，因为您不应该像字符串一样连接 XML。这就是正确完成的方式 - 通过相关的子查询：

declare @t table (
Id int not null,
Address xml not null
);
insert into @t (Id, Address)
values
(5, N'<Address Val="1" />'),
(5, N'<Address Val="2" />'),
(7, N'<Address Val="7" />');

select sq.Id, (
select t.Address
from @t t
where t.Id = sq.Id
for xml path(''), type, root('Addresses')
)
from (select distinct i.Id from @t i) sq
order by sq.Id;

您可以根据需要将生成的输出用作insert或update中的源。