add attribute and update value XML column sqlserver2008

Question

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.

i have found this question stackoverflow.com/questions/12545964/… will test, maybe is solves my problem — user1692756
– user1692756, Commented Sep 23, 2012 at 19:58

jdl · Accepted Answer · 2013-02-11 03:49:54Z

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!

edited Feb 11, 2013 at 3:49

answered Feb 11, 2013 at 2:15

jdl

1,1048 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

add attribute and update value XML column sqlserver2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related