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

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

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

Я думал, что могу сделать следующее:

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

Но, похоже, добавляется только одна строка из #tmpEntityAddress, а это не то, что мне нужно, поскольку мне нужна вся коллекция.

Возможно ли это с помощью SQL? Если да, то как этого можно достичь?


xml sql sql-server tsql xml-dml
person Technicolour    schedule 18.04.2017    source источник
comment
Какой еще контент уже существует в Addresses?   -  person Shnugo    schedule 18.04.2017
comment
@Shnugo Куча данных, связанных с адресом, таких как элементы ‹Street /› ‹StreetNumber /›. Я отредактировал его, так как не думал, что он имеет отношение к рассматриваемой проблеме.   -  person Technicolour    schedule 19.04.2017

Ответы (2)


arrow_upward
1
arrow_downward

Вы правы, что вы можете использовать .modify() только для одного действия за раз...

В следующем коде есть некоторые предположения:

  1. В вашем примере вторая строка в Addresses должна иметь EntityId=7
  2. В обоих XML-таблицах есть уже существующий контент.

Вы можете использовать обновляемый CTE. Этот CTE выберет столбцы Addresses и добавит вычисляемый столбец всех XML-файлов для данного идентификатора из таблицы 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>
person Shnugo    schedule 18.04.2017
comment
Это близко, однако мне нужно, чтобы адрес не был вложенным. ‹Адреса›‹Адрес id=5›‹Улица /›‹/Адрес›‹Адрес id=7›‹Улица /›‹/Адрес›‹/Адреса› - person Technicolour; 19.04.2017

arrow_upward
0
arrow_downward

Вы абсолютно правы в том, что не следует объединять 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, в зависимости от ваших потребностей.

person Roger Wolf    schedule 18.04.2017
comment
Я думаю, что ОП хочет вставить несколько Address XML в уже существующий Addresses XML в другой таблице. Ваш подход подходит только для одной таблицы, но, по крайней мере, насколько я понимаю, это не решает проблему... - person Shnugo; 18.04.2017