SQL Server XQuery Part 5


In SQL Server, do you need to update data in your XML? You can do that with replace value of … with. We will be working with a variable of XML type.

In this discussion, we will look at an example from Microsoft, at their website.

The syntax is replace value of Expression1 with Expression2. So to use it, if we have an XML document stored in the variable @Doc, we have SET @myDoc.modify(‘replace value of … with … ‘);

Replacing values in an XML instance

A document instance is first assigned to a variable of xml type (@Doc). Then, replace value of XML DML statements update values in the document.

DECLARE @myDoc xml;  
SET @myDoc = '
<Root>  
    <Location LocationID="10" LaborHours="1.1" MachineHours=".2" >
                 Manufacturing steps are described here.  
        <step>Manufacturing step 1 at this work center</step>  
        <step>Manufacturing step 2 at this work center</step>  
    </Location>  
</Root>';  
SELECT @myDoc;  
-- update text in the first manufacturing step  
SET @myDoc.modify('  
  replace value of (/Root/Location/step[1]/text())[1]  
  with     "new text describing the manu step"  
');  
SELECT @myDoc;  
-- update attribute value  
SET @myDoc.modify('  
  replace value of (/Root/Location/@LaborHours)[1]  
  with     "100.0"  
');  
SELECT @myDoc;

Here are the results of the query.

<Root>
  <Location LocationID="10" LaborHours="1.1" MachineHours=".2">
		Manufacturing steps are described here.  
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>


<Root>
  <Location LocationID="10" LaborHours="1.1" MachineHours=".2">
		Manufacturing steps are described here.  
<step>Manufacturing step 1 at this work center</step>
<step>new text describing the manu step</step>
</Location>
</Root>
<Root>
  <Location LocationID="10" LaborHours="100.0" MachineHours=".2">
		Manufacturing steps are described here.  
<step>Manufacturing step 1 at this work center</step>
<step>new text describing the manu step</step>
</Location>
</Root>
Series Navigation<< SQL Server XQuery Part 4SQL Server XQuery Part 6 >>

Leave a comment

Your email address will not be published. Required fields are marked *