How to update specific XML attributes in SQL Server?

Question

We are storing an XML document of all the different costs in SQL Server 2008 r2. Now, certain prices need to be update based on a formula.

One approach to this would be to:

Shred the XML and access the values.
Do the calculation
Now rebuild the XML structure.

Is this the only way to do it? Or is there an easier way where we can access the values on the fly and update the entire XML in one go? For this kind of scenario, what is the best approach?

Here is the sample of the XML:

<ItemAttributeData>
 <Section ID="469919" TypeID="61000002">
  <Attributes>
    <Attribute ID="3870" UniqueID="1c71212b-8395-4b43-a44d-9a63374c8c99" VALUE="2" />
    <Attribute ID="3569" UniqueID="c0b754d3-e03e-4d4a-9bc1-dcb11c04535b" VALUE="2" />
    <Attribute ID="1592" UniqueID="2609b344-88b2-44d6-be20-07b634705c30" VALUE="2" />
   </Attributes>
  </Section>
 <Section ID="469920" TypeID="61000001">
   <Attributes>
     <Attribute ID="3702" UniqueID="7cabf9e8-4f60-4e1e-afe5-75b153e8c5eb" VALUE="1000001649" />
     <Attribute ID="3870" UniqueID="595f8e2c-bccb-4fcb-81d4-303fcb7d44c4" VALUE="2" />
     <Attribute ID="3868" UniqueID="2226f6c6-380a-4195-98f7-c6deec84e4e8" VALUE="2" />
     <Attribute ID="3754" UniqueID="bf936c3f-fa96-49b9-8a62-46fb7e0009d5" VALUE="1000001600" />
     <Attribute ID="2204" UniqueID="bdc78784-86ad-4567-bc06-40896d603eaf" VALUE="1" />
     <Attribute ID="3874" UniqueID="8728249f-5e60-4938-a60d-208426fe11c8" VALUE="1" />
    </Attributes>
   </Section>
  <Section ID="469921" TypeID="61000001" />
 </ItemAttributeData>

Is this chunk of XML stored in SQL Server in column of data type XML? — Stoleg
– Stoleg, Commented Jun 25, 2013 at 21:17
@Stoleg: Yes it is a column of type XML Stored in SQL Server — user1176058
– user1176058, Commented Jun 26, 2013 at 13:01

Sled · Accepted Answer · 2017-02-20 01:50:07Z

2

You can use non standard XML update command replace value of. Like

DECLARE @myDoc xml
SET @myDoc = '<Root>
<Location LocationID="10" 
            LaborHours=".1"
            MachineHours=".2" >Manu 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

SET @myDoc.modify('
  replace value of (/Root/Location[1]/@LaborHours)[1]
  with (
       if (count(/Root/Location[1]/step) > 3) then
         "3.0"
       else
          "1.0"
      )
')
SELECT @myDoc

edited Feb 20, 2017 at 1:50

Sled

19.2k28 gold badges125 silver badges174 bronze badges

answered Jun 26, 2013 at 8:20

Alexander Sigachov

1,55111 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update specific XML attributes in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related