Using XPath/XQuery to create child nodes in a SQL column

Question

I have a sql table with an xml column named CasingRules, which would contain data such as:

<root>
  <Item>
    <RegularExpression>^Mc[A-Z,a-z]*</RegularExpression>
    <Format>ULU</Format>
  </Item>
  <Item>
    <RegularExpression>^Mac[A-Z,a-z]*</RegularExpression>
    <Format>ULLU</Format>
  </Item>
</root>

I'm trying to use MS SQL's modify method to add a new node 'ApplyTo' into each item, to create something like:

<root>
  <Item>
    <RegularExpression>^Mc[A-Z,a-z]*</RegularExpression>
    <Format>ULU</Format>
    <ApplyTo>NameAndAddress</ApplyTo>
  </Item>
  <Item>
    <RegularExpression>^Mac[A-Z,a-z]*</RegularExpression>
    <Format>ULLU</Format>
    <ApplyTo>NameAndAddress</ApplyTo>
  </Item>
</root>

.. but I'm very much a newbie to XPath, and not even sure if its possible to update multiple nodes in one query? Is there an elegant way to achieve this?

I'm expecting the syntax is something like this, but its not working:

UPDATE TableName
SET CasingRules.modify('insert <ApplyTo>NameAndAddress</ApplyTo> as last into (/root//Item[1])')

Answer 1

This XQuery:

declare namespace local = "http://example.org";
declare function local:copy($element as element()) {
  element {node-name($element)}
    {$element/@*,
     for $child in $element/node()
        return if ($child instance of element())
          then local:test($child)
          else $child
    }
};
declare function local:test($element as element()) {
  local:copy($element),
  for $true in ($element[parent::Item][not(following-sibling::*)])
  return <ApplyTo>NameAndAddress</ApplyTo>
};
local:copy(/*)

Output:

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <Item>
    <RegularExpression>^Mc[A-Z,a-z]*</RegularExpression>
    <Format>ULU</Format>
    <ApplyTo>NameAndAddress</ApplyTo>
  </Item>
  <Item>
    <RegularExpression>^Mac[A-Z,a-z]*</RegularExpression>
    <Format>ULLU</Format>
    <ApplyTo>NameAndAddress</ApplyTo>
  </Item>
</root>

Answer 2

Not so sure XQuery support in MSSQL can handle this, but will be happy to hear otherwise (and switch the accepted answer) if anyone knows better.

So in the meantime, I updated in C# instead:

foreach (TypedRow row in typedDataSet)
{
  XmlDocument casingRulesXml = new XmlDocument();
  casingRulesXml.LoadXml(row.CasingRules);

  if (casingRulesXml.GetElementsByTagName("ApplyTo").Count == 0)
  {
    XmlNodeList itemNodes = casingRulesXml.GetElementsByTagName("Item");

    for (int i = 0; i < itemNodes.Count; i++)
    {
      XmlElement newElement = casingRulesXml.CreateElement("ApplyTo");
      newElement.InnerText = "NameAndAddress";
      itemNodes[i].AppendChild(newElement);
    }

    // In this case the xml has no heading declaration.
    XmlWriterSettings settings = new XmlWriterSettings();
    settings.OmitXmlDeclaration = true;
    settings.ConformanceLevel = ConformanceLevel.Fragment;

    StringBuilder stringBuilder = new StringBuilder();
    XmlWriter xmlWriter = XmlWriter.Create(stringBuilder, settings);
    casingRulesXml.WriteTo(xmlWriter);
    xmlWriter.Flush();

    row.CasingRules = stringBuilder.ToString();
  }
}