Вставка нескольких сгруппированных документов XML в один документ XML с помощью SQL

Question

У меня две таблицы

tmpEntityAddress

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

tmpEntityAddresses

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

Я хочу сгруппировать и вставить документы с различными адресами в один документ с адресами во второй таблице. С грубой и базовой схемой, похожей на:

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

Я не могу полностью понять, как бы я использовал возможности языка XML DML для этого в SQL, такие как вставка XML и т. Д. https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-data-modification-language-xml-dml, модификация языка-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 и добавить вычисляемый столбец всех XML для данного идентификатора из Address Таблица.

Теперь мы можем использовать modify, чтобы вставить все объединенные Address-записи в одно действие:

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в зависимости от ваших потребностей.