Выбор и вставка в сложные данные Oracle XMLTYPE
Я уже писал простые запросы Oracle для извлечения данных XMLTYPE, но этот XML отличается - мне нужно извлечь информацию из атрибутов, дочерних элементов и их соответствующих атрибутов. Я также хотел бы написать оператор INSERT (желательно тот, который может найти наибольшее значение параметра и добавить 1). Рассмотрим следующий 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>
Я могу написать, например, запрос для извлечения всех имен полей:
select
field_names.*
FROM
metadata m,
XMLTABLE('/metadata/fields/field'
PASSING xmltype(m.xml_string)
COLUMNS field_name VARCHAR(32) PATH '@name') field_names;
Как мне написать запрос, который может извлечь всю различную информацию в виде таблицы? Как мне, например, отобразить это как:
field_name | field_label | option_value | option_label
cusInt01 Reference point 1 CB
cusInt01 Reference point 2 CF
cusInt01 Reference point 2 DF
... и т.д. Мысли? Я пытался объединить запрос, но пока крутил свои колеса.
2 ответа
Решение
В вашем примере данные находятся на нескольких уровнях. каждый field может иметь много option, Итак, вы должны расстаться field так же как option элементы с XMLTable. Во-первых, вы должны сломать field элементы, где option элементы отображаются как XMLType. Затем передайте его второму XMLTable для дальнейшей разбивки.
Настройка данных:
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;
Запрос:
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;
Результат:
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
Подобный пример в Oracle XML DB Developer's Guide.
В дополнение к ответу Eat A Peach я разработал несколько запросов INSTEAD OF для вставки и обновления данных. Они все еще находятся в разработке, так что немного грубовато, но я решил опубликовать их здесь на случай, если кто-то может найти их пригодными.
CREATE OR REPLACE VIEW FIELD_OPTIONS AS
select
s.id as space_id,
s.prefix_code,
fields.name AS FIELD_NAME,
fields.label AS FIELD_LABEL,
options.value as OPTION_VALUE,
options.label AS OPTIONS_LABEL,
options.mapping AS OPTIONS_MAPPING
FROM
metadata m,
spaces s,
XMLTABLE('/metadata/fields/field'
PASSING xmltype(m.xml_string)
COLUMNS name VARCHAR(32) PATH '@name',
label VARCHAR(64) PATH '@label',
options XMLTYPE PATH 'option') fields,
XMLTABLE('/option'
PASSING fields.options
COLUMNS value NUMBER PATH '@value',
label VARCHAR(64) PATH '.',
mapping VARCHAR(64) PATH '@mapping') options
where
s.metadata_id = m.id;
CREATE OR REPLACE TRIGGER INSERT_FIELD_OPTIONS
INSTEAD OF INSERT ON FIELD_OPTIONS
FOR EACH ROW
DECLARE
field_name VARCHAR(32);
space_prefix_code VARCHAR(32);
new_option_num NUMBER;
new_option_label VARCHAR(64);
new_option_mapping VARCHAR(64);
BEGIN
space_prefix_code:=:NEW.prefix_code;
field_name:=:NEW.field_name;
new_option_label:=:NEW.options_label;
new_option_mapping:=:NEW.options_mapping;
select --TODO: find out if options.value = 0
max(options.value)+1 INTO new_option_num
FROM
metadata m,
spaces s,
XMLTABLE('/metadata/fields/field'
PASSING xmltype(m.xml_string)
COLUMNS name VARCHAR(32) PATH '@name',
label VARCHAR(64) PATH '@label',
options XMLTYPE PATH 'option') fields,
XMLTABLE('/option'
PASSING fields.options
COLUMNS value NUMBER PATH '@value',
label VARCHAR(64) PATH '.') options
where
s.metadata_id = m.id AND
s.prefix_code = space_prefix_code AND
fields.name=field_name;
update metadata set
xml_string=(INSERTCHILDXML(XMLTYPE(xml_string),
'/metadata/fields/field[@name=''' || field_name || ''']',
'option',
XMLTYPE('<option mapping="' || new_option_mapping || '" value="' || TO_CHAR(new_option_num) || '">' || new_option_label || '</option>'))).getclobval() --must convert to clob as it is stored in the database that way
WHERE
id =(select metadata_id from spaces where prefix_code = space_prefix_code);
DBMS_OUTPUT.put_line(CHR(9) || TO_CHAR(SQL%ROWCOUNT)||' rows updated');
END;
/
CREATE OR REPLACE TRIGGER UPDATE_FIELD_OPTIONS
INSTEAD OF UPDATE ON FIELD_OPTIONS
FOR EACH ROW
DECLARE
field_name VARCHAR(32);
space_prefix_code VARCHAR(32);
new_options_label VARCHAR(64);
new_option_mapping VARCHAR(64);
new_option_value VARCHAR(64);
BEGIN
space_prefix_code:=:NEW.prefix_code;
field_name:=:NEW.field_name;
new_options_label:=:NEW.options_label;
--new_option_mapping:=:NEW.options_mapping; --TODO: add this feature later
new_option_value:=:NEW.option_value;
update metadata set
xml_string=(UPDATEXML(XMLTYPE(xml_string),
'/metadata/fields/field[@name=''' || field_name || ''']/option[@value=''' || new_option_value || ''']/text()',
new_options_label)).getclobval() --must convert to clob as it's stored in the database that way
WHERE
id =(select metadata_id from spaces where prefix_code = space_prefix_code);
DBMS_OUTPUT.put_line(CHR(9) || TO_CHAR(SQL%ROWCOUNT)||' rows updated');
END;
/