基于复合XQuery的SQL Server XML更新序列化数组

Question

我有一个带有XmlProperties列的db表，其中的数据是序列化的。net数组的形式。下面是一个例子:

<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
  <KeyValueOfstringanyType>
    <Key>CompanyName</Key>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">Company A</Value>
  </KeyValueOfstringanyType>
  <KeyValueOfstringanyType>
    <Key>CompanyUrl</Key>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string" />
  </KeyValueOfstringanyType>
</ArrayOfKeyValueOfstringanyType>

我想更新所有的值元素，其中Key = "CompanyName"和Value = "Company A"和set Value = "Company A, LLC."在这一列中还有其他键/值对，所以这就是为什么我需要更新仅适用于该键/值组合。

我可以使用这个查询查询并获得结果:

WITH KeyValues
AS (SELECT
  Id,
  key_value.value('./*:Key[1]', 'NVARCHAR(MAX)') AS [key],
  key_value.value('./*:Value[1]', 'NVARCHAR(MAX)') AS [value]
FROM dbo.MyTable
CROSS APPLY XmlProperties.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType[*:Key="CompanyName" and *:Value="Company A"]') AS N (key_value))
SELECT
  *
FROM KeyValues

然而，我似乎不知道如何将相同的过滤逻辑应用于。modify语句，将"Company a"替换为"Company a, LLC"。

我试过这样做，但无济于事:

UPDATE dbo.MyTable
SET XmlProperties.modify('replace value of (/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType/*:Value[../*:Key="CompanyName" and ../*:Value="Company A"]) with "Company A, LLC"')

谁有优雅的解决方案?我觉得我已经很接近了，但还没有突破。

Answer 1

您可以这样尝试:

declare @xml XML = '<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
  <KeyValueOfstringanyType>
    <Key>CompanyName</Key>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">Company A</Value>
  </KeyValueOfstringanyType>
  <KeyValueOfstringanyType>
    <Key>CompanyUrl</Key>
    <Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string" />
  </KeyValueOfstringanyType>
</ArrayOfKeyValueOfstringanyType>'
SET @xml.modify('
  replace value of 
    (/*:ArrayOfKeyValueOfstringanyType
     /*:KeyValueOfstringanyType[*:Key="CompanyName" and *:Value="Company A"]
     /*:Value
     /text()
    )[1] 
  with "Company A, LLC"
')

SQL小提琴

注意，replace value of要求您将xpath结果限制为一个简单类型。可以看到，在上面的示例中，末尾的/text()表示xpath返回简单类型string，然后()[1]限制字符串只能返回一个实例。