T-SQL XML Update with XML fragment from another linked table

Question

I am looking for a way to update (.modify('insert into..') to be specific) an XML column in a table with xml fragments from another table linked by a foreign key.

For example, my table structure looks like below (simplified):

Fields 1 to 5 in each table can be ignored; their only purpose is to explain the fields Xx.
Fields Xx in each table are defined as XML and is pre-populated with an XML fragment which contains fields from the table inside a tag, table name. XML fragments are depicted after this list.
Table B and Table C has a foreign key FK_A, which links them to Table A. Table A - B and Table A - C are one to many (one record in A can have multiple records in B and C).

Now, to the Xx field sample values prior to what I need to achieve:

<!-- Table A record 1 -->
<TableA PK_A="1">
    <A1>Avalue</A1>
    <A2>Avalue</A2>
    <A3>Avalue</A3>
    <A4>Avalue</A4>
    <A5>Avalue</A5>
</TableA>

<!-- Table B record 1 -->
<TableB PK_B="1" FK_A="1">
    <B1>Bvalue1</B1>
    <B2>Bvalue1</B2>
    <B3>Bvalue1</B3>
    <B4>Bvalue1</B4>
    <B5>Bvalue1</B5>
</TableB>
<!-- Table B record 2 -->
<TableB PK_B="2" FK_A="1">
    <B1>Bvalue2</B1>
    <B2>Bvalue2</B2>
    <B3>Bvalue2</B3>
    <B4>Bvalue2</B4>
    <B5>Bvalue2</B5>
</TableB>

<!-- Table C record 1 -->
<TableC PK_C="1" FK_A="1">
    <C1>Cvalue1</C1>
    <C2>Cvalue1</C2>
    <C3>Cvalue1</C3>
    <C4>Cvalue1</C4>
    <C5>Cvalue1</C5>
</TableC>
<!-- Table C record 2 -->
<TableC PK_C="2" FK_A="1">
    <C1>Cvalue2</C1>
    <C2>Cvalue2</C2>
    <C3>Cvalue2</C3>
    <C4>Cvalue2</C4>
    <C5>Cvalue2</C5>
</TableC>

The problem here is, how would I update Table A by inserting all XBs and XCs as first (or last) in to the corresponding XA? I prefer a single operation to update the whole table.

After the operation, XA should look like:

<!-- Table A record 1 -->
<TableA PK_A="1">
    <!-- Table B record 1 -->
    <TableB PK_B="1" FK_A="1">
        <B1>Bvalue1</B1>
        <B2>Bvalue1</B2>
        <B3>Bvalue1</B3>
        <B4>Bvalue1</B4>
        <B5>Bvalue1</B5>
    </TableB>
    <!-- Table B record 2 -->
    <TableB PK_B="2" FK_A="1">
        <B1>Bvalue2</B1>
        <B2>Bvalue2</B2>
        <B3>Bvalue2</B3>
        <B4>Bvalue2</B4>
        <B5>Bvalue2</B5>
    </TableB>
    <!-- Table C record 1 -->
    <TableC PK_C="1" FK_A="1">
        <C1>Cvalue1</C1>
        <C2>Cvalue1</C2>
        <C3>Cvalue1</C3>
        <C4>Cvalue1</C4>
        <C5>Cvalue1</C5>
    </TableC>
    <!-- Table C record 2 -->
    <TableC PK_C="2" FK_A="1">
        <C1>Cvalue2</C1>
        <C2>Cvalue2</C2>
        <C3>Cvalue2</C3>
        <C4>Cvalue2</C4>
        <C5>Cvalue2</C5>
    </TableC>
    <A1>Avalue</A1>
    <A2>Avalue</A2>
    <A3>Avalue</A3>
    <A4>Avalue</A4>
    <A5>Avalue</A5>
</TableA>

What have I tried so far?

My best lead so far has been a divide and rule approach (a two part solution, which doesn't work).

WITH    CTEB (PK_A, XA, XB)
AS      (SELECT A.PK_A,
                a.XA,
                b.XB
         FROM   TableA AS a
                INNER JOIN
                TableB AS b
                ON b.FK_A = a.PK_A)
UPDATE  CTEB
    SET XA.modify('insert sql:column("XB") as last into (/TableA)[1]');

WITH    CTEC (PK_A, XA, XC)
AS      (SELECT A.PK_A,
                a.XA,
                c.XC
         FROM   TableA AS a
                INNER JOIN
                TableC AS c
                ON c.FK_A = a.PK_A)
UPDATE  CTEC
    SET XA.modify('insert sql:column("XC") as last into (/TableA)[1]');

Edit:

Apologies for not giving any text values to copy paste. Here we go.

DECLARE @A TABLE (PK_A INT, XA XML);
DECLARE @B TABLE (PK_B  INT, XB  XML, FK_A INT);
DECLARE @C TABLE (PK_C  INT, XC  XML, FK_A INT);

INSERT  INTO @A
VALUES (1, '<TableA PK_A="1"><A1>Avalue</A1><A2>Avalue</A2><A3>Avalue</A3><A4>Avalue</A4><A5>Avalue</A5></TableA>')
INSERT  INTO @A
VALUES (2, '<TableA PK_A="2"><A1>Avalue</A1><A2>Avalue</A2><A3>Avalue</A3><A4>Avalue</A4><A5>Avalue</A5></TableA>')
INSERT  INTO @B
VALUES (1,'<TableB PK_B="1" FK_A="1"><B1>Bvalue1</B1><B2>Bvalue1</B2><B3>Bvalue1</B3><B4>Bvalue1</B4><B5>Bvalue1</B5></TableB>', 1)
INSERT  INTO @B
VALUES (2, '<TableB PK_B="2" FK_A="1"><B1>Bvalue2</B1><B2>Bvalue2</B2><B3>Bvalue2</B3><B4>Bvalue2</B4><B5>Bvalue2</B5></TableB>', 1)
INSERT  INTO @B
VALUES (3, '<TableB PK_B="3" FK_A="2"><B1>Bvalue3</B1><B2>Bvalue3</B2><B3>Bvalue3</B3><B4>Bvalue3</B4><B5>Bvalue3</B5></TableB>', 2)
INSERT  INTO @B
VALUES (4, '<TableB PK_B="4" FK_A="2"><B1>Bvalue4</B1><B2>Bvalue4</B2><B3>Bvalue4</B3><B4>Bvalue4</B4><B5>Bvalue4</B5></TableB>', 2)
INSERT  INTO @C
VALUES (1, '<TableC PK_C="1" FK_A="1"><C1>Cvalue1</C1><C2>Cvalue1</C2><C3>Cvalue1</C3><C4>Cvalue1</C4><C5>Cvalue1</C5></TableC>', 1)
INSERT  INTO @C
VALUES (2, '<TableC PK_C="2" FK_A="1"><C1>Cvalue2</C1><C2>Cvalue2</C2><C3>Cvalue2</C3><C4>Cvalue2</C4><C5>Cvalue2</C5></TableC>', 1)
INSERT  INTO @C
VALUES (3, '<TableC PK_C="3" FK_A="2"><C1>Cvalue3</C1><C2>Cvalue3</C2><C3>Cvalue3</C3><C4>Cvalue3</C4><C5>Cvalue3</C5></TableC>', 2)
INSERT  INTO @C
VALUES (4, '<TableC PK_C="4" FK_A="2"><C1>Cvalue4</C1><C2>Cvalue4</C2><C3>Cvalue4</C3><C4>Cvalue4</C4><C5>Cvalue4</C5></TableC>', 2);

WITH    CTEB (PK_A, XA, XB)
AS      (SELECT A.PK_A,
                a.XA,
                b.XB
         FROM   @A AS a, @B as b
         WHERE  b.FK_A = a.PK_A)
UPDATE  CTEB
    SET XA.modify('insert sql:column("XB") as first into (/TableA)[1]');

WITH    CTEC (PK_A, XA, XC)
AS      (SELECT A.PK_A,
                a.XA,
                c.XC
         FROM   @A AS a
                INNER JOIN
                @C AS c
                ON c.FK_A = a.PK_A)
UPDATE  CTEC
    SET XA.modify('insert sql:column("XC") as first into (/TableA)[1]');

SELECT * FROM @A;

This updates the XML only with the first XML fragment from Table B and Table C.

Update: For anyone who might come across this question, please take a look at @Shnugo 's answer along with the marked answer. Both approaches are perfect. I marked @gofr1 's solution as the answer simply because he was the first to reply. Another consideration for future quest hunters is whether you like CTE or sub-select (as Shnugo pointed out).

In the future, please add xml-fragments directly. Don't make a screenshot and post that. People want to copy/paste those xml-fragments to toy around with, they'd rather not type it out themselves. GL. — TT., Oct 27 '16 at 05:57
Sorry about that, added a sample table structure and data to work with. — Jay, Oct 27 '16 at 06:14
No apologies needed :). It's a hint for you; people will be more eager to help if they don't have to type out an example xml-fragment to start working on an answer. — TT., Oct 27 '16 at 06:16
Now, after all your improvements, this is a great question! +1 from my side — Shnugo, Oct 27 '16 at 06:47

gofr1 · Accepted Answer · 2016-10-27T07:41:22.280

I haven't got relationship between tables, so I a bit hardcoded it (take from XML). Here some solution that works with test samples (next time, please, paste actual data - not screenshots).

Creating tables and inserting data similar to yours:

USE tempdb

IF OBJECT_ID(N'#table_a') IS NOT NULL DROP TABLE #table_a
IF OBJECT_ID(N'#table_b') IS NOT NULL DROP TABLE #table_b
IF OBJECT_ID(N'#table_c') IS NOT NULL DROP TABLE #table_c

CREATE TABLE #table_a (
    A nvarchar(2),
    Xx xml
)

CREATE TABLE #table_b (
    B nvarchar(2),
    Xx xml
)

CREATE TABLE #table_c (
    C nvarchar(2),
    Xx xml
)

INSERT INTO #table_a VALUES
(N'A1', N'<tablea PK_A="1"><A1>Avalue1</A1><A2>Avalue2</A2><A3>Avalue3</A3><A4>Avalue4</A4><A5>Avalue5</A5></tablea>')

INSERT INTO #table_b VALUES
(N'B1', N'<tableb PK_B="1" FK_A="1"><B1>Bvalue11</B1><B2>Bvalue12</B2><B3>Bvalue13</B3><B4>Bvalue14</B4><B5>Bvalue15</B5></tableb>'),
(N'B2', N'<tableb PK_B="2" FK_A="1"><B1>Bvalue21</B1><B2>Bvalue22</B2><B3>Bvalue23</B3><B4>Bvalue24</B4><B5>Bvalue25</B5></tableb>')

INSERT INTO #table_c VALUES
(N'C1', N'<tablec PK_C="1" FK_A="1"><C1>Cvalue11</C1><C2>Cvalue12</C2><C3>Cvalue13</C3><C4>Cvalue14</C4><C5>Cvalue15</C5></tablec>'),
(N'C2', N'<tablec PK_C="2" FK_A="1"><C1>Cvalue21</C1><C2>Cvalue22</C2><C3>Cvalue23</C3><C4>Cvalue24</C4><C5>Cvalue25</C5></tablec>')

Updating:

UPDATE a
set Xx.modify('insert sql:column("b.Xx") as first into (/tablea)[1]')
FROM #table_a a
OUTER APPLY (SELECT (
    SELECT Xx 
    FROM #table_b
    WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = Xx.value('(/tableb/@FK_A)[1]','int')
    FOR XML PATH(''), TYPE).query('/Xx/tableb') as Xx
    ) b

UPDATE a
set Xx.modify('insert sql:column("c.Xx") as first into (/tablea)[1]')
FROM #table_a a
OUTER APPLY (SELECT (
    SELECT Xx 
    FROM #table_c
    WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = Xx.value('(/tablec/@FK_A)[1]','int')
    FOR XML PATH(''), TYPE).query('/Xx/tablec') as Xx
    ) c

SELECT *
FROM #table_a

And the output:

<tablea PK_A="1">
  <tablec PK_C="1" FK_A="1">
    <C1>Cvalue11</C1>
    <C2>Cvalue12</C2>
    <C3>Cvalue13</C3>
    <C4>Cvalue14</C4>
    <C5>Cvalue15</C5>
  </tablec>
  <tablec PK_C="2" FK_A="1">
    <C1>Cvalue21</C1>
    <C2>Cvalue22</C2>
    <C3>Cvalue23</C3>
    <C4>Cvalue24</C4>
    <C5>Cvalue25</C5>
  </tablec>
  <tableb PK_B="1" FK_A="1">
    <B1>Bvalue11</B1>
    <B2>Bvalue12</B2>
    <B3>Bvalue13</B3>
    <B4>Bvalue14</B4>
    <B5>Bvalue15</B5>
  </tableb>
  <tableb PK_B="2" FK_A="1">
    <B1>Bvalue21</B1>
    <B2>Bvalue22</B2>
    <B3>Bvalue23</B3>
    <B4>Bvalue24</B4>
    <B5>Bvalue25</B5>
  </tableb>
  <A1>Avalue1</A1>
  <A2>Avalue2</A2>
  <A3>Avalue3</A3>
  <A4>Avalue4</A4>
  <A5>Avalue5</A5>
</tablea>

EDIT

One part UPDATE:

UPDATE a
set Xx.modify('insert sql:column("b.Xx") as first into (/tablea)[1]')
FROM #table_a a
OUTER APPLY (SELECT (
    SELECT * 
    FROM (
            SELECT Xx
            FROM #table_b b
            WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = b.Xx.value('(/tableb/@FK_A)[1]','int') 
            UNION ALL
            SELECT Xx 
            FROM #table_c c
            WHERE a.Xx.value('(/tablea/@PK_A)[1]','int') = c.Xx.value('(/tablec/@FK_A)[1]','int')
        ) d
    FOR XML PATH(''), TYPE).query('/Xx/*') as Xx
    ) b

Plus vote because this solution works. However, this is again a two part solution. So will wait for other solutions to see how they go. Thanks a lot. At least I have a fall back. And its brilliant. — Jay, Oct 27 '16 at 07:28
@gofr1 If you'd add `AS [node()]` to your `SELECT Xx` you would not need the additional `.query('/Xx/*')`. The only difference between this and my answer is *CTE or sub-select*. Seems to be a matter of taste :-) Anyway +1 from my side as you had to set up your own test scenario while I could just copy the existing code from the question. — Shnugo, Oct 27 '16 at 07:51
@Shnugo Haven't heard about `SELECT [column] as [node()]` could you please, give some guidance/examples (links maybe) of using this stuff? — gofr1, Oct 27 '16 at 08:01
@gofr1, [Look here, especiall "Columns with the name of an XPath Node Test"](https://msdn.microsoft.com/en-us/library/ms189885.aspx). The `AS [node()]` is roughly the same as the `AS [*]`. It will insert the value without a column name. In connection with XML-instances I prefer the `AS [node()]` to set an emphasis on the fact, that there is XML inserted. — Shnugo, Oct 27 '16 at 08:16
Wish I could mark multiple answers as right. Because @Shnugo 's approach is different, yet perfect too. So to be fair, I will go with the one which was posted first :). — Jay, Oct 31 '16 at 00:13

score 3 · Answer 2 · answered Oct 27 '16 at 06:46

The XML-DML statement .modify() allows one change at a time. As you want to insert two fragments into the XML in tbl A and you prefer a single operation to update the whole table, I'd suggest to prepare a combined value first and insert this in one single call. The following code is fully ad-hoc...

Try it like this

WITH FragmentsToAdd AS
(
    SELECT tblA.PK_A
          ,(
            SELECT *
            FROM
            (
                SELECT tblB.XB AS [node()]
                FROM @B AS tblB
                WHERE tblB.FK_A=tblA.PK_A
                UNION ALL
                SELECT tblC.XC AS [node()]
                FROM @C AS tblC
                WHERE tblC.FK_A=tblA.PK_A
            ) AS x
            FOR XML PATH(''),TYPE
           ) Fragment
    FROM @A AS tblA 
    GROUP BY tblA.PK_A
)
UPDATE @A SET XA.modify('insert sql:column("Fragment") as first into /TableA[1]')
FROM @A AS a
INNER JOIN FragmentsToAdd AS fr ON a.PK_A=fr.PK_A;

SELECT * FROM @A

I love this solution because in the real problem, I have more than 20 tables linked to the main table and there are multiple main tables. This gives me the option to have one query (soon to be a stored proc) per main table. And in future I can keep adding tables with UNION ALL to keep expanding. Will wait for some more time before marking as answer. — Jay, Oct 27 '16 at 07:36

T-SQL XML Update with XML fragment from another linked table

2 Answers2