CODEWITHSUNDEEP
xmlt-sqlxpathsql-server-2012xml-dml
ajeh
ajeh

Reputation: 2784

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?

Upvotes: 3

Views: 1452

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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>

Upvotes: 2

Related Questions