sqlsql-serverxmlxpathxml-dml

SQL Server XML-DML how to replace element value with value of relative xpath


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>

Solution

  • This works.

    declare @i int
    declare @maxNames int
    set @i = 1
    set @maxNames = (select max(data.value('count(//Name)', 'int')) from Policy)
    while @i <= @maxNames begin
        update policy set data.modify('
          replace value of (//Name[sql:variable("@i")]/Id/text())[1] 
          with concat(
            (//Name[sql:variable("@i")]/First/text())[1], 
            (//Name[sql:variable("@i")]/Last/text())[1], 
            (//Name[sql:variable("@i")]/Business/text())[1])
        ') 
        set @i = @i + 1
    end