Home » Developer & Programmer » JDeveloper, Java & XML » Querying XML
Querying XML [message #244127] Mon, 11 June 2007 12:15
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
CREATE TABLE TEST_XML
(
  DOC_ID   NUMBER(10),
  DOC_XML  SYS.XMLTYPE
);

Insert into TEST_XML
   (DOC_ID, DOC_XML)
 Values
   (1, XMLTYPE('<doc>
  <id>1</id>
  <store>
    <id>STR1</id>
    <dept>
      <id>DEPT1_STR1</id>
      <file_name>dept1 FOR store1</file_name>
    </dept>
    <dept>
      <id>DEPT2_STR1</id>
      <file_name>dept2 FOR store1</file_name>
    </dept>
    <id>STR2</id>
    <dept>
      <id>DEPT1_STR2</id>
      <file_name>dept1 FOR store2</file_name>
    </dept>
    <dept>
      <id>DEPT2_STR2</id>
      <file_name>dept2 FOR store2</file_name>
    </dept>
  </store>
</doc>
'));
Insert into TEST_XML
   (DOC_ID, DOC_XML)
 Values
   (2, XMLTYPE('<doc>
  <id>2</id>
  <store>
    <id>STR2</id>
    <dept>
      <id>DEPT1_STR2</id>
      <file_name>dept1 FOR store2</file_name>
    </dept>
    <dept>
      <id>DEPT2_STR2</id>
      <file_name>FIR21 FOR store2</file_name>
    </dept>
  </store>
</doc>
'));
COMMIT;

I am trying to write a query to return the following result set:

DOC_ID	STORE_ID	DEPT_ID

1	STR1		DEPT1_STR1
1	STR1		DEPT2_STR1
1	STR2		DEPT1_STR2
1	STR2		DEPT2_STR2
2	STR2		DEPT1_STR2
2	STR2		DEPT2_STR2


I am able to write the query to return all DOC/STORE combinations and another one for all DOC/DEPT combinations:

select extractValue(a.doc_xml,'/doc/id') as doc_id, 
--extractValue(value(dept),'/doc/store/id') as store_id, 
extractValue(value(dept),'/dept/id') as dept_id 
from test_xml a, 
table(XMLSequence(extract(a.doc_xml,'/doc/store/dept'))) dept

and another one for all DOC/DEPT combinations:
select extractValue(a.doc_xml,'/doc/id') as doc_id, 
extractValue(value(str),'/id') as store_id
from test_xml a,
table(XMLSequence(extract(a.doc_xml,'/doc/store/id'))) str

but I cannot figure out how to combine the two.
Oracle v10.1.0.5.0

Thanks
-Art
Previous Topic: Help with XML output 10g- Closing issue
Next Topic: Problem with adf table
Goto Forum:
  


Current Time: Thu Mar 28 11:40:31 CDT 2024