I've written simple Oracle queries to extract XMLTYPE data before, but this XML is different - I need to pull information from attributes, child elements, and their respective attributes. I also would like to write an INSERT statement as well (preferably one that is able to find the highest option value and add 1). Consider the following XML:
<metadata>
<fields>
<field name="cusInt01" label="Reference point">
<option value="1">CB</option>
<option value="2">CF</option>
<option value="3">DF</option>
<option value="4">EKB</option>
<option value="5">ES</option>
<option value="6">GL</option>
<option value="7">GR</option>
<option value="8">KB</option>
<option value="9">KBE</option>
<option value="10">MSL</option>
<option value="11">PT</option>
<option value="12">RB</option>
<option value="13">RF</option>
<option value="14">RT</option>
<option value="15">UN</option>
<option value="16">UNK</option>
</field>
</fields>
</metadata>
I can write, for instance, a query to extract all of the field names:
select
field_names.*
FROM
metadata m,
XMLTABLE('/metadata/fields/field'
PASSING xmltype(m.xml_string)
COLUMNS field_name VARCHAR(32) PATH '@name') field_names;
How do I write a query that can extract all the different information in a tabular form? How do I, for instance, display it as:
field_name | field_label | option_value | option_label
cusInt01 Reference point 1 CB
cusInt01 Reference point 2 CF
cusInt01 Reference point 2 DF
... etc. Thoughts? I've been trying to cobble a query together but so far spinning my wheels.
In your example, the data is at multiple levels. Each field
can have many option
.
So, you must break up field
as well as option
elements with XMLTable.
First, you should break field
elements, where option
elements are mapped as XMLType. Then pass it to second XMLTable to further break it down.
Data Setup:
CREATE TABLE metadata (xml_string VARCHAR2 (2000));
INSERT INTO metadata
VALUES ('<metadata>
<fields>
<field name="cusInt01" label="Reference point">
<option value="1">CB</option>
<option value="2">CF</option>
<option value="3">DF</option>
<option value="4">EKB</option>
<option value="5">ES</option>
<option value="6">GL</option>
<option value="7">GR</option>
<option value="8">KB</option>
<option value="9">KBE</option>
<option value="10">MSL</option>
<option value="11">PT</option>
<option value="12">RB</option>
<option value="13">RF</option>
<option value="14">RT</option>
<option value="15">UN</option>
<option value="16">UNK</option>
</field>
</fields>
</metadata>');
COMMIT;
Query:
SELECT field.field_name,
field.field_label,
options.option_value,
options.option_label
FROM metadata m,
XMLTABLE (
'metadata/fields/field'
PASSING xmltype (m.xml_string)
COLUMNS field_name VARCHAR2 (32) PATH '@name',
field_label VARCHAR2 (32) PATH '@label',
field_options XMLTYPE PATH 'option') field,
XMLTABLE (
'option'
PASSING field.field_options
COLUMNS option_value NUMBER PATH '@value',
option_label VARCHAR2 (10) PATH '/') options;
Result:
FIELD_NAME FIELD_LABEL OPTION_VALUE OPTION_LABEL
-------------------------------------------------------------
cusInt01 Reference point 1 CB
cusInt01 Reference point 2 CF
cusInt01 Reference point 3 DF
cusInt01 Reference point 4 EKB
cusInt01 Reference point 5 ES
cusInt01 Reference point 6 GL
cusInt01 Reference point 7 GR
cusInt01 Reference point 8 KB
cusInt01 Reference point 9 KBE
cusInt01 Reference point 10 MSL
cusInt01 Reference point 11 PT
cusInt01 Reference point 12 RB
cusInt01 Reference point 13 RF
cusInt01 Reference point 14 RT
cusInt01 Reference point 15 UN
cusInt01 Reference point 16 UNK
Similar example at Oracle XML DB Developer's Guide.