SQL Server XML Update Serialized Array based on compound XQuery

Question

I have a db table with an XmlProperties column with data in the form of a Serialized .NET array. Here's an example:

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

I would like to update all Value elements where Key = "CompanyName" and Value = "Company A" and set Value = "Company A, LLC." There are other Key/Value pairs in this column so that's why I need the update to only apply to that Key/Value combination.

I am able to query and get the results using this query:

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

However, I can't seem to figure out how to apply that same filtering logic to a .modify statement to replace "Company A" with "Company A, LLC."

I have tried something like this to no avail:

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

Anybody got an elegant solution? I feel like I'm close, but just haven't cracked it yet.

Answer 1

You can try this way :

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 Fiddle

Notice that replace value of requires you to limit the xpath result to one simple type. See that in the above example, trailing /text() means the xpath returns simple type string, and then ()[1] limits strings being returned to only one instance.