Topic
  • 5 replies
  • Latest Post - ‏2011-05-05T13:41:49Z by anost
anost
anost
13 Posts

Pinned topic generic value in xmlquery

‏2011-05-04T12:28:44Z |
Hi all,

maybe somebody out there, who can help me?
I want to update some values stored in a XML column in DB2. I tried to use the following after an example:

select xmlquery(
'copy $new := $INFO modify 

do replace value of $new/customerinfo/phone with 
"905-xxx-xxxx" 

return $new 
')  from xmlcustomer where cid = 1000;


Now my question is: It is possible to use a DB-derived value instead of 905-xxx-xxxx.
I think on something like:

SELECT x.phonenumber FROM SCHEMA.
"NUMBERS" o, xmltable(
'$c/Block0/*/phonenumber' passing o.data as 
"c" columns phonenumber varchar(16) path 
'.' ) as x where datasetid = XXX and x.phonenumber like 
'049%' fetch first 1 rows only ;


Thanks,
Andreas
Updated on 2011-05-05T13:41:49Z at 2011-05-05T13:41:49Z by anost
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: generic value in xmlquery

    ‏2011-05-04T20:32:40Z  
    Hi Andreas,

    the XML update can use pretty much any value as input, e.g. a value from a relational column or from some computed expression.

    Here is an example that uses the value from the relational column "phone" to update the phone element in the XML document. The PASSING clause in the XMLQUERY function can be used to pass in whatever you want to use in the update:

    
    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;
    


    Does this help?

    Matthias

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

    Re: generic value in xmlquery

    ‏2011-05-05T11:34:26Z  
    Hi Andreas,

    the XML update can use pretty much any value as input, e.g. a value from a relational column or from some computed expression.

    Here is an example that uses the value from the relational column "phone" to update the phone element in the XML document. The PASSING clause in the XMLQUERY function can be used to pass in whatever you want to use in the update:

    <pre class="jive-pre"> 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; </pre>

    Does this help?

    Matthias

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

    thank you for the advice. Now I understand "Update 3" of your tech-article better and I used it for modifying single values. Of course another question came up. Now I want to use "Update 19" -update multiple node in the same manner-.

    With a modifcation of your sample:
    
    <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> <scientificvalue>-72</scientificvalue> <scientificvalue>+58</scientificvalue> </customerinfo>
    


    If I want to update all scientificvalue with a function done on it:
    
    select XMLQUERY(
    ' copy $newinfo := $INFO modify 
    
    for $j in $newinfo/customerinfo/scientificvalue 
    
    return 
    
    do replace value of $j with $p 
    
    return $newinfo
    ' PASSING (SELECT abs(integer(x.sv)) FROM customer o, xmltable(
    '$c/customerinfo' passing o.info as 
    "c" columns scientificvalue varchar(16) path 
    '.' ) as x where id = 123) as 
    "p") from customer WHERE id = 123;
    


    For sure it does not work as I did not get an single value in my xmltable-selection. Is there an way to include the $j iterator also in the xmltable-selection?

    Best,
    Andreas
  • anost
    anost
    13 Posts

    Re: generic value in xmlquery

    ‏2011-05-05T12:24:17Z  
    • anost
    • ‏2011-05-05T11:34:26Z
    Hi Matthias,

    thank you for the advice. Now I understand "Update 3" of your tech-article better and I used it for modifying single values. Of course another question came up. Now I want to use "Update 19" -update multiple node in the same manner-.

    With a modifcation of your sample:
    <pre class="jive-pre"> <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> <scientificvalue>-72</scientificvalue> <scientificvalue>+58</scientificvalue> </customerinfo> </pre>

    If I want to update all scientificvalue with a function done on it:
    <pre class="jive-pre"> select XMLQUERY( ' copy $newinfo := $INFO modify for $j in $newinfo/customerinfo/scientificvalue return do replace value of $j with $p return $newinfo ' PASSING (SELECT abs(integer(x.sv)) FROM customer o, xmltable( '$c/customerinfo' passing o.info as "c" columns scientificvalue varchar(16) path '.' ) as x where id = 123) as "p") from customer WHERE id = 123; </pre>

    For sure it does not work as I did not get an single value in my xmltable-selection. Is there an way to include the $j iterator also in the xmltable-selection?

    Best,
    Andreas
    Ok..I'm getting better. Here is what I did:
    
    select XMLQUERY(
    ' copy $newinfo := $INFO modify 
    
    for $j in $newinfo/customerinfo/scientificvalue let $k := abs($j) where starts-with($j,
    '-') 
    
    return 
    
    do replace value of $j with $k 
    
    return $newinfo
    ' ) from customer WHERE id = 123;
    


    But at the moment I have some trouble with the starts-with function:
    SQL0104N An unexpected token "-" was found following "ere starts-with($j,'".
    Expected tokens may include: ")". SQLSTATE=42601

    Maybe I could get some help here?

    Best,
    Andreas
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: generic value in xmlquery

    ‏2011-05-05T12:36:45Z  
    • anost
    • ‏2011-05-05T12:24:17Z
    Ok..I'm getting better. Here is what I did:
    <pre class="jive-pre"> select XMLQUERY( ' copy $newinfo := $INFO modify for $j in $newinfo/customerinfo/scientificvalue let $k := abs($j) where starts-with($j, '-') return do replace value of $j with $k return $newinfo ' ) from customer WHERE id = 123; </pre>

    But at the moment I have some trouble with the starts-with function:
    SQL0104N An unexpected token "-" was found following "ere starts-with($j,'".
    Expected tokens may include: ")". SQLSTATE=42601

    Maybe I could get some help here?

    Best,
    Andreas
    Here is another option that you could consider:

    
    select XMLQUERY(
    ' copy $newinfo := $INFO modify 
    
    for $j in $newinfo/customerinfo/scientificvalue 
    
    return 
    
    do replace value of $j with fn:abs(xs:integer($j)) 
    
    return $newinfo
    ' ) from customer WHERE id = 123;
    

    The problem with the starts-with function is probably that you used single quotes for the 2nd parameter when it should be double quotes, like this:

    starts-with($j,"-")

    Cheers,

    Matthias


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

    Re: generic value in xmlquery

    ‏2011-05-05T13:41:49Z  
    Here is another option that you could consider:

    <pre class="jive-pre"> select XMLQUERY( ' copy $newinfo := $INFO modify for $j in $newinfo/customerinfo/scientificvalue return do replace value of $j with fn:abs(xs:integer($j)) return $newinfo ' ) from customer WHERE id = 123; </pre>
    The problem with the starts-with function is probably that you used single quotes for the 2nd parameter when it should be double quotes, like this:

    starts-with($j,"-")

    Cheers,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks for the code-shortening! Actually the problem in starts-with was the single vs. double quotes. I am sure to have tested both cases...
    Just one remark, if other people need it: By usage of more syntax of FLWOR you have to move down the first return.
    
    select XMLQUERY(
    ' copy $newinfo := $INFO modify 
    
    for $j in $newinfo/customerinfo/scientificvalue where starts-with($j,
    "-") and not(matches($j,
    ",",
    "ix")) 
    
    return 
    
    do replace value of $j with fn:abs(xs:integer($j)) 
    
    return $newinfo
    ' ) from customer WHERE id = 123;
    

    Ok...special thanks to Matthias for the excellent help!
    Best,
    Andreas