Home » Developer & Programmer » JDeveloper, Java & XML » XML Query Help (Oracle 10.1.0.5.0)
XML Query Help [message #282058] Tue, 20 November 2007 13:20 Go to next message
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
Junior Member
I am trying to figure out how to query documents containing multiple nodes.

The sample document below containg a "store" row at the top level and two "departemnt" rows in the DEPT node.

CREATE TABLE XML_TEST (XML_DOC CLOB);

INSERT INTO XML_TEST VALUES('
<XPORTAL xmlns="com.fimt.xportal">
<SESSION_ID>AAAA</SESSION_ID>
<ROW>
<STORE_ID>S1</STORE_ID>
<STORE_NAME>STORE 1</STORE_NAME>
<DEPT>
<DEPT_ROW>
<DEPT_ID>D1</DEPT_ID>
<DEPT_NAME>DEPT 1</DEPT_NAME>
</DEPT_ROW>
<DEPT_ROW>
<DEPT_ID>D2</DEPT_ID>
<DEPT_NAME>DEPT 2</DEPT_NAME>
</DEPT_ROW>
</DEPT>
</ROW>
</XPORTAL>');

COMMIT;

I need to return the following data set:

SESSION_ID	STORE_ID	DEPT_NAME
AAAA		S1		DEPT 1
AAAA		S1		DEPT 2

The query below returns a single row with correct SESSION_ID and STORE_ID.
SELECT DEPT.*,
       EXTRACTVALUE (XMLTYPE (XML_DOC),
                     '/XPORTAL/SESSION_ID',
                     'xmlns="com.fimt.xportal"'
                    ) SESSION_ID,
       EXTRACTVALUE (XMLTYPE (XML_DOC),
                     '/XPORTAL/ROW/STORE_ID',
                     'xmlns="com.fimt.xportal"'
                    ) STORE_ID,
       EXTRACT (VALUE(DEPT),
                     '/DEPT/DEPT_ROW/DEPT_NAME',
                     'xmlns="com.fimt.xportal"'
                    ) DEPT_NAME
FROM XML_TEST,
     TABLE(XMLSEQUENCE(EXTRACT (XMLTYPE (XML_DOC),
                                '/XPORTAL/ROW/DEPT',
                                'xmlns="com.fimt.xportal"'
                       ))) DEPT

For DEPT_NAME it returns XML doc that contains both departments:
<DEPT_NAME xmlns="com.fimt.xportal">DEPT 1</DEPT_NAME>
<DEPT_NAME xmlns="com.fimt.xportal">DEPT 2</DEPT_NAME>

How do I get the result set that I need?

Thanks
Art
Re: XML Query Help [message #284609 is a reply to message #282058] Fri, 30 November 2007 04:56 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi there.

You need to use the following syntax to acheive what you require :

SELECT
  EXTRACTVALUE(VALUE(t), '/XPORTAL/SESSION_ID', 'xmlns="com.fimt.xportal"') session_id,
  EXTRACTVALUE(VALUE(s), '/ROW/STORE_ID', 'xmlns="com.fimt.xportal"') store_id,
  EXTRACTVALUE(VALUE(u), '/DEPT_ROW/DEPT_ID', 'xmlns="com.fimt.xportal"') dept_id,
  EXTRACTVALUE(VALUE(u), '/DEPT_ROW/DEPT_NAME', 'xmlns="com.fimt.xportal"') dept_name
FROM
  xml_test x,
  TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(x.xml_doc), '/XPORTAL', 'xmlns="com.fimt.xportal"'))) t,
  TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), '/XPORTAL/ROW', 'xmlns="com.fimt.xportal"'))) s,
  TABLE(XMLSEQUENCE(EXTRACT(VALUE(s), '/ROW/DEPT/DEPT_ROW', 'xmlns="com.fimt.xportal"'))) u
/

SESSION_ID STORE_ID   DEPT_ID    DEPT_NAME
---------- ---------- ---------- ----------
AAAA       S1         D1         DEPT 1
AAAA       S1         D2         DEPT 2

2 rows selected.


Regards
Previous Topic: how to connect oracle 9i Database using java coding
Next Topic: oracle.jms.AQjmsException: JMS-120: Dequeue failed
Goto Forum:
  


Current Time: Tue Dec 03 18:06:38 CST 2024