| XML Query Help [message #282058] |
Tue, 20 November 2007 13:20  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
