I have an xml column with the following data:
<Policy>
<Name>
<Id>adf</Id>
<First>Alan</First>
<Last>Turing</Last>
</Name>
<Name>
<Id>asdf</Id>
<Business>Vandelay Industries</Business>
</Name>
<Name>
<Id>asdf</Id>
<First>Dennis</First>
<Last>Ritchie</Last>
</Name>
</Policy>
How would I write an XML-DML query that will replace the value of the Id element for each Name across all rows so it contains the value of First, Last, and Business. For example:
<Policy>
<Name>
<Id>AlanTuring</Id>
<First>Alan</First>
<Last>Turing</Last>
</Name>
<Name>
<Id>Vandelay Industries</Id>
<Business>Vandelay Industries</Business>
</Name>
<Name>
<Id>DennisRitchie</Id>
<First>Dennis</First>
<Last>Ritchie</Last>
</Name>
</Policy>
Here's my failed attempt:
update policy set data.modify('
replace value of (//Name/Id/text())[1]
with concat(
(//Name/First/text())[1],
(//Name/Last/text())[1],
(//Name/Business/text())[1])')
Which produces the following:
<Policy>
<Name>
<Id>AlanTuringVandelay Industries</Id>
<First>Alan</First>
<Last>Turing</Last>
</Name>
<Name>
<Id>asdf</Id>
<Business>Vandelay Industries</Business>
</Name>
<Name>
<Id>asdf</Id>
<First>Dennis</First>
<Last>Ritchie</Last>
</Name>
</Policy>
This works.