xmlt-sqlxpathsql-server-2012xml-dml

How can I use XQuery to update an attribute value in an XML variable?


Trying to update one attribute in the XML contained in a variable:

set @x.modify('declare namespace abc="http://abcsystems.com/";
replace value of
(/abc:App/abc:Client/abc:Addresses/abc:PostalAddress)[@AddressLine1] with "555 Service Rd."')

Tried that with and without a subscript on @AddressLine1[1].

This throws the error:

Msg 2337, Level 16, State 1, Line 8
XQuery [modify()]: The target of 'replace' must be at most one node, found 'element(abc{http://abcsystems.com/}:PostalAddress,xdt:untyped) *'

There is only one PostalAddress element in the entire XML. What is the error telling me?


Solution

  • Without the actual XML this is flying blind, but you might be looking for this:

    set @x.modify('declare namespace abc="http://abcsystems.com/";
    replace value of
    (/abc:App/abc:Client/abc:Addresses/abc:PostalAddress/@AddressLine1)[1] with "555 Service Rd."')
    

    The (xpath here)[1] is typically used to enforce one single node

    UPDATE: Working example

    declare @x xml=
    N'<abc:App xmlns:abc="http://abcsystems.com/">
      <abc:Client>
        <abc:Addresses>
          <abc:PostalAddress AddressLine1="test" />
        </abc:Addresses>
      </abc:Client>
    </abc:App>';
    
    set @x.modify('declare namespace abc="http://abcsystems.com/";
    replace value of
    (/abc:App/abc:Client/abc:Addresses/abc:PostalAddress/@AddressLine1)[1] 
    with "555 Service Rd."');
    
    select @x;
    

    The result

    <abc:App xmlns:abc="http://abcsystems.com/">
      <abc:Client>
        <abc:Addresses>
          <abc:PostalAddress AddressLine1="555 Service Rd." />
        </abc:Addresses>
      </abc:Client>
    </abc:App>