How to extract from multiple levels in one query
362032Dec 1 2004 — edited Dec 3 2004 Hi,
I'm new to xml and extracting data and I can't seem to figure out how to do the following.
I have a rather large (17MB) xml file loaded in a table column. The column is xmltype stored as object relational. Now I have the following element hierarchy '/BroadcastInvoice/Payee/Station/Payer/...' There are multiple payees with multiple Stations which have multiple payers, etc, etc. But let's stop at Payer to try and keep this simple. Assume that Payee, Station and Payer have a Name attribute.
I want to issue a sql statement against this column to extract the Payee, Station and Payer Name attributes as columns. I've tried various combinations of extract and xmlsequence, but can't seem to extract the multiple levels. So the query will look something like:
select ... from xml_invoice_test, table(xmlsequence()) where xml_id = 1;
and return:
Payee Station Payer
===== ======= =====
A 1 a
A 1 b
A 2 c
B 1 a
B 1 c
B 1 d
C 1 a
C 2 a
Is this possible with a single sql query, or do I need to write a pl/sql routine that will loop through the various levels to extract the information I need? Eventually, I will be extracting other information at lower levels, but if I can figure out how to do it at this level, then the rest should be obvious. The intent of all of this is to load a relational table with information in this XML file.
This is on Oracle 10g, so if there are some features that will help with this, I will be very greatful.
Thanks,
Alan