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