Using XML.Modify how to insert a node UNDERNEATH an existing node

Question

Using SQL and the function xml.modify is there anyway to add a new node underneath a specific node?

Here is my XML

<TESTCASESUMMARY>
    <TESTCASES>
        <TESTS id="1">
            <TEST1>1</TEST1>
            <TEST2>10</TEST2>
            <TEST3>10</TEST3>
            <TEST4>0</TEST4>
            <TEST5>0.75</TEST5>
            <TEST6>10</TEST6>
            <TEST7/>
            <TEST8>0.05</TEST8>
            <TEST9>0.05</TEST9>
            <TEST10>        <TEST11>0.05</TEST11>B</TEST10>
            <TEST12/>                           
            <TEST13/>                   
    </TESTCASES>           
</TESTCASESUMMARY>

I currently have

SET @xml.modify('insert <TEST11>{sql:variable("@TEST11")}</TEST11> as first into 

(TESTCASESUMMARY/TESTCASES/TESTS[sql:variable("@counter")]/TEST10)[1]')

i need to get test 11 under test 10 and not on the same level

thank you

Answer 1

I hope I get this correctly...

You can use after to specify a node, where you want to place the new node directly behind. But this node must exist...

DECLARE @xml XML=
N'<TESTCASESUMMARY>
    <TESTCASES>
        <TESTS id="1">
            <TEST1>1</TEST1>
            <!-- more nodes -->
            <TEST10>B</TEST10>
            <TEST12/>                           
            <TEST13/> 
        </TESTS>                  
    </TESTCASES>           
</TESTCASESUMMARY>';

DECLARE @Value11 VARCHAR(100)='0.05';
DECLARE @counter INT=1;

SET @xml.modify(N'insert <TEST11>{sql:variable("@Value11")}</TEST11> 
                  after 
                  (/TESTCASESUMMARY/TESTCASES/TESTS[sql:variable("@counter")]/TEST10)[1]');

SELECT @xml;

The result

<TESTCASESUMMARY>
  <TESTCASES>
    <TESTS id="1">
      <TEST1>1</TEST1>
      <!-- more nodes -->
      <TEST10>B</TEST10>
      <TEST11>0.05</TEST11>
      <TEST12 />
      <TEST13 />
    </TESTS>
  </TESTCASES>
</TESTCASESUMMARY>