2

I have seen several answers here on stackoverflow but I can not find what I'm looking for. I have checked this question and this one and some others pages but can't manage to get to a solution.

This is my test data. Actually this code is inside an XML column in a table.

<product id=152>
    <name>tractor</name>
    <category weight=5600 invoice="A600"/>
</product>
<product id=731>
    <name>excavadora</name>
    <category weight=12340 invoice="B300"/>
</product>

What I need is to update the previous XML doing two actions:

  • add new attribute on the category node
  • be able to update that new attribute value, tried modifying weight but can't get to do it

I think the solution should be using .modify and .value keywords in a query but don't know how.

Im using SQL Server 2008 express. I have seen that a query can help to get an answer but I don't really have anything working.

Community
  • 1
  • 1
  • i have found this question http://stackoverflow.com/questions/12545964/updating-xml-attributes-with-new-values-in-a-sql-server-2008-table will test, maybe is solves my problem –  Sep 23 '12 at 19:58

1 Answers1

0

Here's an example specific to your situation. It's replacing the weight of ID 152 with the value 54321.

DECLARE
    @x xml,
    @ProductID int,
    @CategoryWeight int

SELECT @x = '
    <Product ID="152"> 
        <Category Weight="5600" Invoice="A600" /> 
    </Product>
    <Product ID="731">
        <Category Weight="12340" Invoice="B300" />        
    </Product>'

SELECT
   @ProductID = 152,
   @CategoryWeight = 54321

SET @x.modify('
    replace value of (
        /Product[@ID=sql:variable("@ProductID")]/Category/@Weight
    )[1]
    with sql:variable("@CategoryWeight")
')

SELECT @x

Note that you can replace the sql:variable("") pieces with actual values if you want to. Get familiar with this example and the insert should be a piece of cake. :) Good luck!

jdl
  • 1,104
  • 8
  • 12