Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2011-05-05T13:41:49Z by anost
anost
anost
13 Posts
ACCEPTED ANSWER

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
    321 Posts
    ACCEPTED ANSWER

    Re: generic value in xmlquery

    ‏2011-05-04T20:32:40Z  in response to anost
    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
      ACCEPTED ANSWER

      Re: generic value in xmlquery

      ‏2011-05-05T11:34:26Z  in response to MatthiasNicola
      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
        ACCEPTED ANSWER

        Re: generic value in xmlquery

        ‏2011-05-05T12:24:17Z  in response to anost
        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
          321 Posts
          ACCEPTED ANSWER

          Re: generic value in xmlquery

          ‏2011-05-05T12:36:45Z  in response to anost
          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
            ACCEPTED ANSWER

            Re: generic value in xmlquery

            ‏2011-05-05T13:41:49Z  in response to MatthiasNicola
            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