Results 1 to 8 of 8

Thread: xml problem

  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: xml problem

    hi, can i ask a hand on this...

    how can i query in the xml document,
    customers having state="Australia"
    with the gender="female"

    using the function xmlexists

    <Customer>
    <name>shane</name>
    <address>
    <state>Australia</state>
    <zipcode>0012</zipcode>
    </address>
    <gender>female</gender>
    </Customer>


    here is my code.
    select id from
    customer
    where xmlexist($cust_xml/customer/address[state="Australia"]')

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Thusly?
    Code:
    $cust_xml/customer[address/state="Australia" and gender="female"]

  3. #3
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by n_i View Post
    Thusly?
    Code:
    $cust_xml/customer[address/state="Australia" and gender="female"]

    Hi, i tried your code but there is no display...but when i look at in my table there is one record that having state="Australia" gender="female'


    Thank you in advance.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You know that XML is case-sensitive, don't you?

  5. #5
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by n_i View Post
    You know that XML is case-sensitive, don't you?

    hi, yes i know it's case sensitive...i made another query that will only filter having state="Australia" and there are records display.



    Kindly Regrads,

    Jemz

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    >db2 -tvf c:\temp\t.sql
    with x(c) as (values xmlparse(document 
    '<Customer> 
     <name>shane</name> 
     <address> 
      <state>Australia</state> 
      <zipcode>0012</zipcode> 
     </address> 
     <gender>female</gender> 
    </Customer>'
    )) select x.* from sysibm.sysdummy1 d, x 
    where xmlexists (
    '$i/Customer[address/state="Australia" and gender="female"]'
    passing x.c as "i")
    
    C
    
    ---------------------------------------------------------------------------------------
    <Customer><name>shane</name><address><state>Australia</state><zipcode>0012</zipcode></address><gender>female</gender></C
    ustomer>
    
    
    
    
      1 record(s) selected.

  7. #7
    Join Date
    Aug 2011
    Posts
    84
    [QUOTE=n_i;6519962]
    Code:
    >db2 -tvf c:\temp\t.sql
    with x(c) as (values xmlparse(document 
    '<Customer> 
     <name>shane</name> 
     <address> 
      <state>Australia</state> 
      <zipcode>0012</zipcode> 
     </address> 
     <gender>female</gender> 
    </Customer>'
    )) select x.* from sysibm.sysdummy1 d, x 
    where xmlexists (
    '$i/Customer[address/state="Australia" and gender="female"]'
    passing x.c as "i")
    
    C
    
    ---------------------------------------------------------------------------------------
    <Customer><name>shane</name><address><state>Australia</state><zipcode>0012</zipcode></address><gender>female</gender></C
    ustomer>
    
    
    
    
      1 record(s) selected.
    [/QUOTE



    wow nice are you doing this in db2ce?is this the way to insert the xml in db2ce...i use datastudio to insert using stored procedure...

    by the way thank you for this and i will come back if this will work.more power to you


    Best Regards,

    Jemz

  8. #8
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by n_i View Post
    Thusly?
    Code:
    $cust_xml/customer[address/state="Australia" and gender="female"]

    Hi,

    it's working now...thank you for solving my problem..by the way where i can get this some tutorial or reference in filtering like my problem or other problem related to xml...thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •