Home » Developer & Programmer » JDeveloper, Java & XML » Query XML Data (2 Merged) ( 9.2.0.5.0)
Query XML Data (2 Merged) [message #497945] Mon, 07 March 2011 20:53 Go to next message
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?



Re: Query XML Data (2 Merged) [message #497986 is a reply to message #497945] Tue, 08 March 2011 01:00 Go to previous message
Michel Cadot
Messages: 67920
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
Previous Topic: how can me write into xml file ? (merged)
Next Topic: Unable to access URL using HttpURLConnection
Goto Forum:
  


Current Time: Tue Jul 27 23:03:41 CDT 2021