How to replace a tag with another in sql server XML file

Question

I am working on a table in sql server which stores xml file in a column. In that xml file I am doing some changes. The XML file looks like:

<Report version=1>
 <Title>
   <Student>
         <InputNumber type="int" min="0" max="100" name="age" description="Age 
          of student">
            <Value>20</Value>
         </InputNumber>
         <InputNumber type="int" min="0" max="100" name="height" 
         description="height of student">
             <Value>170</Value>
         </InputNumber>
   </Student>
 </Title>
</Report>

I understand the usage of modify function for updating attributes or text present between tags as:

UPDATE student
SET dataxml.modify('replace value of (/Report/@version)[1] with "2"')
WHERE id=10
or
UPDATE student
SET dataxml.modify('replace value of (/Report/Title/Student/InputNumber[1]/Value[1]/text())[1] with "21"')
WHERE id=10

But now I want to replace entire tag with another tag i.e.

         <InputNumber type="int" min="0" max="100" name="height" 
         description="height of student">
             <Value>170</Value>
         </InputNumber>

with

         <InputText name="height" 
         description="height of student">
             <Value>170 cm</Value>
         </InputText>

I found something on internet like this and tried.

Update Student
   set dataxml = replace(cast(dataxml as nvarchar(max)),'/Report/Title/Student/InputNumber[2]>','InputText>')
WHERE id=10

It says updated successfully. But I don't see the change in XML. How can I do that?

Answer 1

First of all: Your XML is not valid. The attribute version=1 must be version="1". Second: The verb tag is just one markup like <Student> or </Student>, but the whole node with attributes and nested sub-nodes is called node or - as a special type of node - element.

Now to your issue:

We need a declared table to simulate your issue:

DECLARE @student TABLE(ID INT IDENTITY, dataxml XML);
INSERT INTO @student VALUES
(N'<Report version="1">
 <Title>
   <Student>
         <InputNumber type="int" min="0" max="100" name="age" description="Age of student">
            <Value>20</Value>
         </InputNumber>
         <InputNumber type="int" min="0" max="100" name="height" description="height of student">
             <Value>170</Value>
         </InputNumber>
   </Student>
 </Title>
</Report>');

--This is the new element we want to insert (better: want to use to replace another)

DECLARE @newElement XML=
N'<InputText name="height" description="height of student">
    <Value>170 cm</Value>
  </InputText>';

--approach one calls `.modify()` twice:

UPDATE @student SET dataxml.modify('insert sql:variable("@newElement") after (/Report/Title/Student/InputNumber[@name="height"])[1]');
UPDATE @student SET dataxml.modify('delete (/Report/Title/Student/InputNumber[@name="height"])[1]');

SELECT * FROM @student; 

--approach two uses FLWOR-XQuery

UPDATE @student SET dataxml=dataxml.query('<Report version="{/Report/@version}">
                                           {<Title> 
                                            <Student>
                                            {
                                            for $elmt in /Report/Title/Student/*
                                            return
                                            if(local-name($elmt)="InputNumber" and $elmt[@name="height"]) then
                                               <InputText name="height" description="height of student">
                                                   <Value>{$elmt/Value/text()} cm</Value>
                                               </InputText>
                                            else
                                                $elmt
                                            }
                                            </Student>
                                           </Title>}
                                           </Report>');

Both ideas in short:

1) We insert the new element right after the one which should be replaced and remove it in a separate step.

2) We re-create the XML via XQuery by running through the inner list of nodes within <Student> and insert the new content instead of the existing node.