OraFAQ Forum: JDeveloper, Java & XML » Query XML Data (2 Merged)

Home » Developer & Programmer » JDeveloper, Java & XML » Query XML Data (2 Merged) ( 9.2.0.5.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query XML Data (2 Merged) [message #497945]

Mon, 07 March 2011 20:53

srraajesh
Messages: 63
Registered: May 2005

Member

Hi All,

I was going through a blogpost that was explaining XMLSequence usage with some examples.

CREATE TABLE PROD_XML_TAB 
   (	TEST_FIELD XMLTYPE 
   )

INSERT INTO PROD_XML_TAB(TEST_FIELD)
VALUES(
XMLType('<Product ProductCode="100">
 <BrandName>IPhones</BrandName>
 <ModelDetails ModelID="100-1" ModelName="iPhone 3G">
  <Item ItemNo="100-1-A"><ItemName>IPhone 3G 8GB Black</ItemName><Price>900</Price></Item>
 </ModelDetails>
 <ModelDetails ModelID="100-2" ModelName="iPhone 3GS">
  <Item ItemNo="100-2-A"><ItemName>IPhone 3GS 16GB Black</ItemName><Price>900</Price></Item>
  <Item ItemNo="100-2-B"><ItemName>IPhone 3G S 16GB White</ItemName><Price>950</Price></Item>
  <Item ItemNo="100-2-C"><ItemName>IPhone 3G S 32GB Black</ItemName><Price>1000</Price></Item>
  <Item ItemNo="100-2-D"><ItemName>IPhone 3G S 32GB White</ItemName><Price>1050</Price></Item>
  </ModelDetails> 
  <ModelDetails ModelID="100-3" ModelName="IPhone 4">
  <Item ItemNo="100-3-A"><ItemName>IPhone 4 16GB White</ItemName><Price>1200</Price></Item>
  <Item ItemNo="100-3-B"><ItemName>IPhone 4 16GB Black</ItemName><Price>1200</Price></Item>
  <Item ItemNo="100-3-C"><ItemName>IPhone 4 32GB White</ItemName><Price>1400</Price></Item>
 </ModelDetails>
</Product>'))

It is expected to come with the output as shown in the blog post (product code Vs model ID Vs list of item names ) and I came up with the following SQL..

WITH TB_TBL AS       
(select extractvalue(test_field, '/Product/@ProductCode') as product_code,
       extractvalue(value(a), '/ModelDetails/@ModelID')  as model_id,
       extract(value(a), '/ModelDetails/Item') as item_Det
  from PROD_XML_TAB,
       table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a)
select product_code,model_id,extractvalue(value(a1),'/Item/ItemName')
from tb_tbl,table(xmlsequence(extract(item_Det, '/Item'))) a1

Is there a better way to achieve the same?

Report message to a moderator

Re: Query XML Data (2 Merged) [message #497986 is a reply to message #497945]

Tue, 08 March 2011 01:00

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Better in which meaning?
You can write it as follow (is this better?):

SQL> select extractvalue(test_field, '/Product/@ProductCode') as product_code,
  2         extractvalue(value(a), '/ModelDetails/@ModelID')  as model_id,
  3         extractvalue(value(a1),'/Item/ItemName') item_name
  4  from PROD_XML_TAB,
  5       table(xmlsequence(extract(test_field, '/Product/ModelDetails'))) a,
  6       table(xmlsequence(extract(value(a), '/ModelDetails/Item'))) a1
  7  /
PRODUCT_CODE MODEL_ID ITEM_NAME
------------ -------- -------------------------
100          100-1    IPhone 3G 8GB Black
100          100-2    IPhone 3GS 16GB Black
100          100-2    IPhone 3G S 16GB White
100          100-2    IPhone 3G S 32GB Black
100          100-2    IPhone 3G S 32GB White
100          100-3    IPhone 4 16GB White
100          100-3    IPhone 4 16GB Black
100          100-3    IPhone 4 32GB White

Regards
Michel

Report message to a moderator

Previous Topic:	how can me write into xml file ? (merged)
Next Topic:	Unable to access URL using HttpURLConnection

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 02:29:28 CDT 2024