sql-serverxmlsql-server-2008t-sqlxml-dml

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

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):

Table structure

  1. Fields 1 to 5 in each table can be ignored; their only purpose is to explain the fields Xx.
  2. 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.
  3. 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).

Solution

  • 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