IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2012-03-01T23:33:09Z by MatthiasNicola
anost
anost
13 Posts

Pinned topic update xml over multiple tables

‏2012-02-29T11:04:47Z |
Hi,

a had a related topic before: generic value in xmlquery. Now I want to update a xml value by using a value from a relational column. If I use the former sample it works:

create table customer(id integer, phone varchar(15), info XML); insert into customer values (123, 
'408-463-4963', 
'<customerinfo Cid="1002"> <name>Jim Noodle</name> <addr country=
"Canada"> <street>25 EastCreek</street> <city>Markham</city> <prov-state>Ontario</prov-state> <pcode-zip>N9C 3T6</pcode-zip> </addr> <phone type=
"work">905-555-7258</phone> </customerinfo>
'); select XMLQUERY(
' copy $newinfo := $INFO modify 

do replace value of $newinfo/customerinfo/phone with $p 

return $newinfo
' PASSING customer.phone as 
"p") from customer WHERE id = 123;


Also adapted with a second table it works:

select XMLQUERY(
' copy $newinfo := $INFO modify 

do replace value of $newinfo/customerinfo/phone with $p 

return $newinfo
' PASSING anotherTable.phone as 
"p") from customer, anotherTable WHERE customer.id = anotherTable.id;


But if I want to make an update I can just hand over 1 table?!

update customer SET INFO = XMLQUERY(
'...


Therefore I started to somehow integrate the second table. My last try is here:

update customer SET INFO = xmlquery(
'copy $newinfo := $INFO modify 

for $j in $newinfo/customerinfo/phone 

return 

do replace value of $j with db2-fn:sqlquery(
"SELECT phone from anotherTable where id=parameter(1)",$id) 

return $new
' passing customer.id as 
"id") where id = 101;


Now I got SQL16040N. The argument of function function name does not identify a single column with SQL data type XML. Could somebody give me a hint how to correct this error. Or know somebody a better solution for my problem?

Best,
Andreas
Updated on 2012-03-01T23:33:09Z at 2012-03-01T23:33:09Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: update xml over multiple tables

    ‏2012-02-29T19:58:50Z  
    Hi Andreas,

    I don't have time right now to try it out, but here is my best guess.

    In the embedded SQL query, wrap the XMLTEXT function around the returned "phone" column, so that it returns a column of XML text nodes (type XML) rather than a varchar column:

    db2-fn:sqlquery("SELECT XMLTEXT(phone) from anotherTable where id=parameter(1)",$id)

    Does this resolve the problem?

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • anost
    anost
    13 Posts

    Re: update xml over multiple tables

    ‏2012-03-01T08:48:32Z  
    Hi Andreas,

    I don't have time right now to try it out, but here is my best guess.

    In the embedded SQL query, wrap the XMLTEXT function around the returned "phone" column, so that it returns a column of XML text nodes (type XML) rather than a varchar column:

    db2-fn:sqlquery("SELECT XMLTEXT(phone) from anotherTable where id=parameter(1)",$id)

    Does this resolve the problem?

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias,

    thanks for your answer, it solved the problem. Actually I already tried the XMLTEXT function but I wrapped it around the wrong term (the whole sql query).

    Best,
    Andreas
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: update xml over multiple tables

    ‏2012-03-01T23:33:09Z  
    • anost
    • ‏2012-03-01T08:48:32Z
    Hi Matthias,

    thanks for your answer, it solved the problem. Actually I already tried the XMLTEXT function but I wrapped it around the wrong term (the whole sql query).

    Best,
    Andreas
    Glad it works for you. Thanks for confirming that this was the correct solution.

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/