xml problem

jemz · #1 (**permalink**) 09-27-11, 12:58

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.

Quote:

select id from
customer
where xmlexist($cust_xml/customer/address[state="Australia"]')

n_i · #2 (**permalink**) 09-27-11, 13:42

Thusly?

Code:

$cust_xml/customer[address/state="Australia" and gender="female"]

jemz · #3 (**permalink**) 09-27-11, 14:07

Quote:

Originally Posted by n_i

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.

n_i · #4 (**permalink**) 09-27-11, 14:58

You know that XML is case-sensitive, don't you?

jemz · #5 (**permalink**) 09-27-11, 21:55

Quote:

Originally Posted by n_i

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

n_i · #6 (**permalink**) 09-27-11, 22:30

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.

jemz · #7 (**permalink**) 09-27-11, 23:32

[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

jemz · #8 (**permalink**) 09-28-11, 05:39

Quote:

Originally Posted by n_i

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech