Marloptoptemo
New Member
I have a table (Oracle 11.2.0.2) with an xmltype column and other non-xmltype columns. I want to do a select based on the value of some of the non-xmltype columns plus the contents of the xmltype column.I've tried doing this using xmlquery in my select, but I'm getting some funny results. Here's a reduced version my data: an order table with the username and the order details.\[code\]-- create order tablecreate table orders(username varchar2(20), order_data xmltype);-- an order entered by jiminsert into orders values ('jim', xmltype('<ord
rder xmlnsrd="http://www.blah.com/order/1.0"> <ord:zip>123</ord:zip> <ord:date_time>2012-09-24T00:27:00</ord:date_time> <ord:item> <ord
roduct>A</ordroduct> <ord:quantity>12</ord:quantity> </ord:item> <ord:item> <ordroduct>B</ordroduct> <ord:quantity>34</ord:quantity> </ord:item></ordrder>'));-- an order entered by bobinsert into orders values ('bob', xmltype('<ordrder xmlnsrd="http://www.blah.com/order/1.0"> <ord:zip>123</ord:zip> <ord:date_time>2012-09-24T00:27:00</ord:date_time> <ord:item> <ordroduct>A</ordroduct> <ord:quantity>56</ord:quantity> </ord:item> <ord:item> <ordroduct>C</ordroduct> <ord:quantity>78</ord:quantity> </ord:item></ordrder>'));\[/code\]As an example query, I tried to find all orders entered by Bob which have more than 1 item by doing this:\[code\]SELECT username, itemcountFROM (SELECT username, XMLCAST(XMLQUERY('count(/*rder/*:item)' PASSING order_data RETURNING CONTENT) AS NUMBER) itemcount FROM orders )WHERE username = 'bob'AND itemcount > 1\[/code\]but it doesn't return any results, however if I comment out the last line (AND itemcount > 1) I get:\[code\]username | linecount--------------------bob | 2\[/code\]...I'm not sure why that is, I thought maybe it wasn't treating it as the correct type but I'm casting it as a number with the xmlcast.What's more puzzling is if I set the last line to be:\[code\]AND itemcount = 2\[/code\]...it disappears again, but if I change it to:\[code\]AND itemcount != -7\[/code\]...it reappears in the results, but with a value of zero:\[code\]username | linecount--------------------bob | 0\[/code\]So, I'm puzzled - I must be doing something wrong but I can't see what it is.
rder xmlnsrd="http://www.blah.com/order/1.0"> <ord:zip>123</ord:zip> <ord:date_time>2012-09-24T00:27:00</ord:date_time> <ord:item> <ordroduct>A</ordroduct> <ord:quantity>12</ord:quantity> </ord:item> <ord:item> <ordroduct>B</ordroduct> <ord:quantity>34</ord:quantity> </ord:item></ordrder>'));-- an order entered by bobinsert into orders values ('bob', xmltype('<ordrder xmlnsrd="http://www.blah.com/order/1.0"> <ord:zip>123</ord:zip> <ord:date_time>2012-09-24T00:27:00</ord:date_time> <ord:item> <ordroduct>A</ordroduct> <ord:quantity>56</ord:quantity> </ord:item> <ord:item> <ordroduct>C</ordroduct> <ord:quantity>78</ord:quantity> </ord:item></ordrder>'));\[/code\]As an example query, I tried to find all orders entered by Bob which have more than 1 item by doing this:\[code\]SELECT username, itemcountFROM (SELECT username, XMLCAST(XMLQUERY('count(/*rder/*:item)' PASSING order_data RETURNING CONTENT) AS NUMBER) itemcount FROM orders )WHERE username = 'bob'AND itemcount > 1\[/code\]but it doesn't return any results, however if I comment out the last line (AND itemcount > 1) I get:\[code\]username | linecount--------------------bob | 2\[/code\]...I'm not sure why that is, I thought maybe it wasn't treating it as the correct type but I'm casting it as a number with the xmlcast.What's more puzzling is if I set the last line to be:\[code\]AND itemcount = 2\[/code\]...it disappears again, but if I change it to:\[code\]AND itemcount != -7\[/code\]...it reappears in the results, but with a value of zero:\[code\]username | linecount--------------------bob | 0\[/code\]So, I'm puzzled - I must be doing something wrong but I can't see what it is.