How can I dynamically change the XML structure of a string in SQL

Question

I need a SQL script that will to pull an XML string from the DB [varchar(max)], inspect it, and update it if it fits a specific situation.

Imagine that my xml is in the following format:

<root>
  <level1>
    <level2>
      <level3 />
      <level3 />
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for XYZ">
              <options>
                <option this="that" />
                <option me="you" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="this one is not of interest">
              <options>
                <option this="that" />
                <option me="you" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for ABC">
              <options>
                <option this="that" />
                <option me="you" />
                <option here="now" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
</root>

So, what I want to do is to update all elements whose name is "level6" and which have an attribute called "here" whose value begins with "now is the time". So, that should match just two elements above.

But, that's not the only selection criteria. The list of options must not contain <option here="now" />. So, that should leave us with just one element to update.

<level6 here="now is the time for XYZ">
    <options>
        <option this="that" />
        <option me="you" />
    </options>
 </level6>

To that element, I then want to add the missing <option here="now" />, so that becomes:

<level6 here="now is the time for XYZ">
    <options>
        <option this="that" />
        <option me="you" />
        <option here="now" />
    </options>
 </level6>

So, the end result should be:

 <root>
  <level1>
    <level2>
      <level3 />
      <level3 />
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for XYZ">
              <options>
                <option this="that" />
                <option me="you" />
                <option here="now" />      // <- this one new
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="this one is not of interest">
              <options>
                <option this="that" />
                <option me="you" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for ABC">
              <options>
                <option this="that" />
                <option me="you" />
                <option here="now" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
</root>

Assume that I can read the data out of the DB into a string, and that I know how to update the DB, so it's really how to manipulate the xml string in SQL (SQL Server).

Answer 1

You can use XML DML (data modification) with the .modify function to change the XML.

SET @xml.modify('
  insert <option here="now" />
  as last into
  ( /root/level1/level2/level3/level4/level5/level6
     [substring(@here, 1, 15) = "now is the time"]
     /options [not(/option[@here = "now"])]
   )[1]');

This works as follows:

• insert <option here="now" /> this is the value we are inserting
• as last into it goes after other child nodes of the selected one
• /root/level1/level2/level3/level4/level5/level6 this gets us that level6 node
• [substring(@here, 1, 15) = "now is the time"] predicates the node to have a here attribute starting with that value. You must modify the length parameter to match the value you are comparing. There is no LIKE in XQuery
• /options [not(/option[@here = "now"])] we look for an options node which has no option child which in turn has a here="now" attribute
• [1] the first such node

If you need to modify multiple nodes within a single XML document, you need to run this in loop

DECLARE @i int = 20; --max nodes

WHILE @xml.exist('
  /root/level1/level2/level3/level4/level5/level6
     [substring(@here, 1, 15) = "now is the time"]
     /options [not(option[@here = "now"])]
   ') = 1
BEGIN

    SET @xml.modify('
      insert <option here="now" /> as last into
      ( /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       )[1]');
     
    SET @i -= 1;
    IF @i = 0
        BREAK;
END;

You can also do this for a whole table

DECLARE @i int = 20; --max nodes

WHILE EXISTS (SELECT 1
    FROM YourTable
    WHERE XmlColumn.exist('
      /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       ') = 1)
BEGIN

    UPDATE t
    SET XmlColumn.modify('
      insert <option here="now" /> as last into
      ( /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       )[1]')
    FROM YourTable t
    WHERE XmlColumn.exist('
      /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       ') = 1;
     
    SET @i -= 1;
    IF @i = 0
        BREAK;
END;

For very large datasets it may be faster to rebuild the whole XML using XQuery, with the extra node added using Constructed XML.

db<>fiddle