title: T-SQL XML Update with XML fragment from another linked table
body: <p>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.</p>
  
  <p>For example, my table structure looks like below (simplified):</p>
  
  <p><a href="https://i.stack.imgur.com/ge65P.png" rel="nofollow"><img src="https://i.stack.imgur.com/ge65P.png" alt="Table structure"></a></p>
  
  <ol>
  <li>Fields 1 to 5 in each table can be ignored; their only purpose is to
  explain the fields Xx.</li>
  <li>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.</li>
  <li>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).</li>
  </ol>
  
  <p>Now, to the Xx field sample values prior to what I need to achieve:</p>
  
  <pre><code>&lt;!-- Table A record 1 --&gt;
  &lt;TableA PK_A="1"&gt;
      &lt;A1&gt;Avalue&lt;/A1&gt;
      &lt;A2&gt;Avalue&lt;/A2&gt;
      &lt;A3&gt;Avalue&lt;/A3&gt;
      &lt;A4&gt;Avalue&lt;/A4&gt;
      &lt;A5&gt;Avalue&lt;/A5&gt;
  &lt;/TableA&gt;
  
  &lt;!-- Table B record 1 --&gt;
  &lt;TableB PK_B="1" FK_A="1"&gt;
      &lt;B1&gt;Bvalue1&lt;/B1&gt;
      &lt;B2&gt;Bvalue1&lt;/B2&gt;
      &lt;B3&gt;Bvalue1&lt;/B3&gt;
      &lt;B4&gt;Bvalue1&lt;/B4&gt;
      &lt;B5&gt;Bvalue1&lt;/B5&gt;
  &lt;/TableB&gt;
  &lt;!-- Table B record 2 --&gt;
  &lt;TableB PK_B="2" FK_A="1"&gt;
      &lt;B1&gt;Bvalue2&lt;/B1&gt;
      &lt;B2&gt;Bvalue2&lt;/B2&gt;
      &lt;B3&gt;Bvalue2&lt;/B3&gt;
      &lt;B4&gt;Bvalue2&lt;/B4&gt;
      &lt;B5&gt;Bvalue2&lt;/B5&gt;
  &lt;/TableB&gt;
  
  &lt;!-- Table C record 1 --&gt;
  &lt;TableC PK_C="1" FK_A="1"&gt;
      &lt;C1&gt;Cvalue1&lt;/C1&gt;
      &lt;C2&gt;Cvalue1&lt;/C2&gt;
      &lt;C3&gt;Cvalue1&lt;/C3&gt;
      &lt;C4&gt;Cvalue1&lt;/C4&gt;
      &lt;C5&gt;Cvalue1&lt;/C5&gt;
  &lt;/TableC&gt;
  &lt;!-- Table C record 2 --&gt;
  &lt;TableC PK_C="2" FK_A="1"&gt;
      &lt;C1&gt;Cvalue2&lt;/C1&gt;
      &lt;C2&gt;Cvalue2&lt;/C2&gt;
      &lt;C3&gt;Cvalue2&lt;/C3&gt;
      &lt;C4&gt;Cvalue2&lt;/C4&gt;
      &lt;C5&gt;Cvalue2&lt;/C5&gt;
  &lt;/TableC&gt;
  </code></pre>
  
  <p>The problem here is, <strong>how would I update Table A by inserting all XBs and XCs as first (or last) in to the corresponding XA?</strong> I prefer a single operation to update the whole table.</p>
  
  <p>After the operation, XA should look like:</p>
  
  <pre><code>&lt;!-- Table A record 1 --&gt;
  &lt;TableA PK_A="1"&gt;
      &lt;!-- Table B record 1 --&gt;
      &lt;TableB PK_B="1" FK_A="1"&gt;
          &lt;B1&gt;Bvalue1&lt;/B1&gt;
          &lt;B2&gt;Bvalue1&lt;/B2&gt;
          &lt;B3&gt;Bvalue1&lt;/B3&gt;
          &lt;B4&gt;Bvalue1&lt;/B4&gt;
          &lt;B5&gt;Bvalue1&lt;/B5&gt;
      &lt;/TableB&gt;
      &lt;!-- Table B record 2 --&gt;
      &lt;TableB PK_B="2" FK_A="1"&gt;
          &lt;B1&gt;Bvalue2&lt;/B1&gt;
          &lt;B2&gt;Bvalue2&lt;/B2&gt;
          &lt;B3&gt;Bvalue2&lt;/B3&gt;
          &lt;B4&gt;Bvalue2&lt;/B4&gt;
          &lt;B5&gt;Bvalue2&lt;/B5&gt;
      &lt;/TableB&gt;
      &lt;!-- Table C record 1 --&gt;
      &lt;TableC PK_C="1" FK_A="1"&gt;
          &lt;C1&gt;Cvalue1&lt;/C1&gt;
          &lt;C2&gt;Cvalue1&lt;/C2&gt;
          &lt;C3&gt;Cvalue1&lt;/C3&gt;
          &lt;C4&gt;Cvalue1&lt;/C4&gt;
          &lt;C5&gt;Cvalue1&lt;/C5&gt;
      &lt;/TableC&gt;
      &lt;!-- Table C record 2 --&gt;
      &lt;TableC PK_C="2" FK_A="1"&gt;
          &lt;C1&gt;Cvalue2&lt;/C1&gt;
          &lt;C2&gt;Cvalue2&lt;/C2&gt;
          &lt;C3&gt;Cvalue2&lt;/C3&gt;
          &lt;C4&gt;Cvalue2&lt;/C4&gt;
          &lt;C5&gt;Cvalue2&lt;/C5&gt;
      &lt;/TableC&gt;
      &lt;A1&gt;Avalue&lt;/A1&gt;
      &lt;A2&gt;Avalue&lt;/A2&gt;
      &lt;A3&gt;Avalue&lt;/A3&gt;
      &lt;A4&gt;Avalue&lt;/A4&gt;
      &lt;A5&gt;Avalue&lt;/A5&gt;
  &lt;/TableA&gt;
  </code></pre>
  
  <p><strong>What have I tried so far?</strong></p>
  
  <p>My best lead so far has been a divide and rule approach (a two part solution, which doesn't work).</p>
  
  <pre><code>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]');
  </code></pre>
  
  <p>Edit:</p>
  
  <p>Apologies for not giving any text values to copy paste. Here we go.</p>
  
  <pre><code>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, '&lt;TableA PK_A="1"&gt;&lt;A1&gt;Avalue&lt;/A1&gt;&lt;A2&gt;Avalue&lt;/A2&gt;&lt;A3&gt;Avalue&lt;/A3&gt;&lt;A4&gt;Avalue&lt;/A4&gt;&lt;A5&gt;Avalue&lt;/A5&gt;&lt;/TableA&gt;')
  INSERT  INTO @A
  VALUES (2, '&lt;TableA PK_A="2"&gt;&lt;A1&gt;Avalue&lt;/A1&gt;&lt;A2&gt;Avalue&lt;/A2&gt;&lt;A3&gt;Avalue&lt;/A3&gt;&lt;A4&gt;Avalue&lt;/A4&gt;&lt;A5&gt;Avalue&lt;/A5&gt;&lt;/TableA&gt;')
  INSERT  INTO @B
  VALUES (1,'&lt;TableB PK_B="1" FK_A="1"&gt;&lt;B1&gt;Bvalue1&lt;/B1&gt;&lt;B2&gt;Bvalue1&lt;/B2&gt;&lt;B3&gt;Bvalue1&lt;/B3&gt;&lt;B4&gt;Bvalue1&lt;/B4&gt;&lt;B5&gt;Bvalue1&lt;/B5&gt;&lt;/TableB&gt;', 1)
  INSERT  INTO @B
  VALUES (2, '&lt;TableB PK_B="2" FK_A="1"&gt;&lt;B1&gt;Bvalue2&lt;/B1&gt;&lt;B2&gt;Bvalue2&lt;/B2&gt;&lt;B3&gt;Bvalue2&lt;/B3&gt;&lt;B4&gt;Bvalue2&lt;/B4&gt;&lt;B5&gt;Bvalue2&lt;/B5&gt;&lt;/TableB&gt;', 1)
  INSERT  INTO @B
  VALUES (3, '&lt;TableB PK_B="3" FK_A="2"&gt;&lt;B1&gt;Bvalue3&lt;/B1&gt;&lt;B2&gt;Bvalue3&lt;/B2&gt;&lt;B3&gt;Bvalue3&lt;/B3&gt;&lt;B4&gt;Bvalue3&lt;/B4&gt;&lt;B5&gt;Bvalue3&lt;/B5&gt;&lt;/TableB&gt;', 2)
  INSERT  INTO @B
  VALUES (4, '&lt;TableB PK_B="4" FK_A="2"&gt;&lt;B1&gt;Bvalue4&lt;/B1&gt;&lt;B2&gt;Bvalue4&lt;/B2&gt;&lt;B3&gt;Bvalue4&lt;/B3&gt;&lt;B4&gt;Bvalue4&lt;/B4&gt;&lt;B5&gt;Bvalue4&lt;/B5&gt;&lt;/TableB&gt;', 2)
  INSERT  INTO @C
  VALUES (1, '&lt;TableC PK_C="1" FK_A="1"&gt;&lt;C1&gt;Cvalue1&lt;/C1&gt;&lt;C2&gt;Cvalue1&lt;/C2&gt;&lt;C3&gt;Cvalue1&lt;/C3&gt;&lt;C4&gt;Cvalue1&lt;/C4&gt;&lt;C5&gt;Cvalue1&lt;/C5&gt;&lt;/TableC&gt;', 1)
  INSERT  INTO @C
  VALUES (2, '&lt;TableC PK_C="2" FK_A="1"&gt;&lt;C1&gt;Cvalue2&lt;/C1&gt;&lt;C2&gt;Cvalue2&lt;/C2&gt;&lt;C3&gt;Cvalue2&lt;/C3&gt;&lt;C4&gt;Cvalue2&lt;/C4&gt;&lt;C5&gt;Cvalue2&lt;/C5&gt;&lt;/TableC&gt;', 1)
  INSERT  INTO @C
  VALUES (3, '&lt;TableC PK_C="3" FK_A="2"&gt;&lt;C1&gt;Cvalue3&lt;/C1&gt;&lt;C2&gt;Cvalue3&lt;/C2&gt;&lt;C3&gt;Cvalue3&lt;/C3&gt;&lt;C4&gt;Cvalue3&lt;/C4&gt;&lt;C5&gt;Cvalue3&lt;/C5&gt;&lt;/TableC&gt;', 2)
  INSERT  INTO @C
  VALUES (4, '&lt;TableC PK_C="4" FK_A="2"&gt;&lt;C1&gt;Cvalue4&lt;/C1&gt;&lt;C2&gt;Cvalue4&lt;/C2&gt;&lt;C3&gt;Cvalue4&lt;/C3&gt;&lt;C4&gt;Cvalue4&lt;/C4&gt;&lt;C5&gt;Cvalue4&lt;/C5&gt;&lt;/TableC&gt;', 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;
  </code></pre>
  
  <p>This updates the XML only with the first XML fragment from Table B and Table C.</p>
  
  <p><strong><em>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).</em></strong></p>