How to replace a node value with another node value in XML data in SQL Server

Question

I have an XML column in SQL Server which stores some data like:

<Title>
    <Section name="General">
         <InputText>
              <Value> Comments for the XML </Value>
         </InputText>
    </Section>
    <Section name="Comments">
         <InputText>
              <Value> Copy here the value of comments from General Section.</Value>
         </InputText>
    </Section>
</Title>

In this XML only one section tag was present. I created one more section called Comments and I want to copy the text value from first section called General to the newly created section's InputText child node.

I have tried one query:

UPDATE tempTable
SET ColumnXML.modify('replace value of (/Title[1]/Section[2]/InputText[1]/Value[1]/text())[1] 
   with ColumnXML.value(/Title[1]/Section[1]/InputText[1]/Value[1]/text())[1], "varchar(max)"')
WHERE id < 100; 

It runs successfully but I don't see any changes happening. The expected output is in all 100 XMLs the value of input text from one section to get copied to another section's input text.

What would be the correct query for this?

Answer 1

You're making several mistakes in using modify.

• You cannot (and need not) reference ColumnXML in the modify function, it will not be visible in its scope.
• I do not know where the varchar(max) comes from in your statement, I suspect you used it as an argument for the value function. You do not need the value function.

DECLARE @x XML=N'<Title>
    <Section name="General">
         <InputText>
              <Value> Comments for the XML </Value>
         </InputText>
    </Section>
    <Section name="Comments">
         <InputText>
              <Value> Copy here the value of comments from General Section.</Value>
         </InputText>
    </Section>
</Title>';

DECLARE @t TABLE(x XML);
INSERT INTO @t(x)VALUES(@x);

UPDATE @t
SET x.modify('replace value of (/Title[1]/Section[2]/InputText[1]/Value[1]/text())[1]
   with (/Title[1]/Section[1]/InputText[1]/Value[1]/text())[1]');

SELECT * FROM @t;