Insert multiple grouped xml documents into a single xml document with SQL

Question

I have two tables

tmpEntityAddress

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

tmpEntityAddresses

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

I want to group and insert the various addresses documents into the single addresses document in the second table. With a rough and basic schema looking like:

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

I can't quite wrap my head around how I would use the XML DML language features to do this in SQL such as XML insert etc https://learn.microsoft.com/en-us/sql/t-sql/xml/xml-data-modification-language-xml-dml

I was thinking that I could do the following:

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

But it seems to only add a single row from #tmpEntityAddress which isn't what I want as I need the whole collection.

Is this possible with SQL? If so how can this be achieved?

@Shnugo A bunch of address related data such as elements. I redacted it as I didn't think it was relevant to the problem at hand. — Technicolour, Apr 18 '17 at 21:21

Shnugo · Accepted Answer · 2017-04-18T21:21:16.283

You are right, that you can use .modify() for one action at a time only...

The following code has some assumptions:

In your example the second line in Addresses should have EntityId=7
There is preexisting content in both table's XML

You can use an updateable CTE. This CTE will pick the columns of Addresses and add a calculated column of all XMLs for the given ID from the Address table.

Now we can use modify, to insert all combined Address-entries in one single action at once:

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

The result for 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>

UPDATE Address not nested

Try this:

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]');

The result

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

This is close, however, I need it Address to not be nested.
– Technicolour Apr 18 '17 at 21:17 — Technicolour, Apr 18 '17 at 21:17

score 0 · Answer 2 · answered Apr 18 '17 at 05:48

You are absolutely right in that you shouldn't concatenate XML like strings. This is how it can be done properly - via a correlated subquery:

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;

You can use the resulting output as a source in either an insert or update, depending on your needs.

I think, that the OP wants to insert several `Address` XMLs into a pre-existing `Addresses` XML within another table. Your approach is fine with one table only, but - at least as I understand this - it is not solving the problem... — Shnugo, Apr 18 '17 at 07:55

Insert multiple grouped xml documents into a single xml document with SQL

2 Answers2

UPDATE Address not nested