代码之家 › 专栏 › 技术社区 › PaulG

使用xpath/xquery在SQL列中创建子节点

xquery xpath sql-server-2005

-1

PaulG · 技术社区 · 14 年前

我有一个SQL表,其中有一个名为casingrules的XML列,其中包含以下数据:

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

我正在尝试使用MS SQL的modify方法将新节点“applyto”添加到每个项中,以创建如下内容:

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

…但我对xpath非常熟悉,甚至不确定是否可以在一个查询中更新多个节点?有没有一个优雅的方法来实现这一点?

我希望语法是这样的,但不起作用:

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

2 回复 | 直到 14 年前

user357812 14 年前

此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(/*)

输出:

<?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>

PaulG 14 年前

不太确定MSSQL中的XQuery支持是否可以处理这个问题,但是如果有人知道得更好的话,我们会很高兴听到(并切换接受的答案)。

因此,在此期间,我更新了c而不是:

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();
  }
}