2

i use DB2 express-c edition v9.1 database management system.

name of the table: student
name of the column: course

xml in the column:

<ids>
<course>
<id>

**101**  

</id> 
<lecture-id>

**0**  
</lecture-id>

</course>
<course>
<id>

**102**  

</id>
<lecture-id>

**2**  

</lecture-id>
</course>
</ids>

I need to update the value of <lecture-id> node of <course> node with <id> node as 101, to 1.
how should I query the database to get the desired result.
any help would be highly appreciated.

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Ajish
  • 21
  • 2

1 Answers1

0

I think it can be like this:

    UPDATE student
       SET course = XMLQUERY('transform 
                            copy      $new := $i
                            modify    do replace value of $new/ids/course/lecture-id
                                                       with 1
                            return    $new' PASSING course AS "i")

 WHERE id = 101;
Jalew
  • 1
  • Just thinking that how **WHERE id = 101** will look inside the xml structure. It seems like that this **id** is actually the id of the row. Am I right? I also have same kind of issue and wanna try this solution you provided above. – Superman Feb 24 '14 at 13:45