Prototyping: Using XML Type for Device Access DB Import

# Using XML Type
# http://docs.oracle.com/cd/B10501_01/appdev.920/a96620/xdb04cre.htm
#
# Load XMLTYPE From File
# http://www.oracle-base.com/articles/9i/LoadXMLTYPEFromFile.php
drop table equipment_models;
CREATE TABLE equipment_models ( equipment_model_name VARCHAR2(16), equipment_model_version NUMBER, equipment_model_xml XMLTYPE, equipment_model_inserted TIMESTAMP);

ALTER TABLE equipment_models ADD (
CONSTRAINT equipment_models_pk PRIMARY KEY (equipment_model_name, equipment_model_version)
);

create or replace
PROCEDURE insert_equipment_model_xml (p_equipment_model_name IN VARCHAR2, p_equipment_model_version IN NUMBER, p_equipment_model_xml_data IN CLOB) AS
v_insertion_timestamp LSA.EQUIPMENT_MODELS.EQUIPMENT_MODEL_INSERTED%TYPE;
BEGIN
-- get current system timestamp
v_insertion_timestamp := SYSTIMESTAMP;

-- delete row if it already exists
DELETE FROM LSA.EQUIPMENT_MODELS em WHERE em.equipment_model_name = p_equipment_model_name AND em.equipment_model_version = p_equipment_model_version;

-- insert the new row
INSERT INTO LSA.EQUIPMENT_MODELS (
equipment_model_name,
equipment_model_version,
equipment_model_xml,
equipment_model_inserted
)
VALUES (
p_equipment_model_name,
p_equipment_model_version,
XMLTYPE.createXML(p_equipment_model_xml_data),
v_insertion_timestamp
);
END;
/

begin
insert_equipment_model_xml('MX',21,'<?xml version="1.0" encoding="UTF-8"?><eqmod xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www-acc.gsi.de/XMLSchema/xsd/v03/eqmod.xsd"><header><eqmodversion>MX_21</eqmodversion></header></eqmod>');
end;

SELECT extract(p.column_value, '//text()') as PROPERTIES FROM EQUIPMENT_MODELS em, TABLE(XMLSEQUENCE(extract(em.EQUIPMENT_MODEL_XML, '//eqmod/properties/property/name'))) p
where em.EQUIPMENT_MODEL_NAME = 'MX' and em.EQUIPMENT_MODEL_VERSION = 21;

SELECT extract(node_table.column_value, '//text()') as PROPERTIES from TABLE(
SELECT XMLSEQUENCE(extract(em.EQUIPMENT_MODEL_XML, '//eqmod/properties/property/name'))
from EQUIPMENT_MODELS em
where em.EQUIPMENT_MODEL_NAME = 'MX' and em.EQUIPMENT_MODEL_VERSION = 21) node_table
;

# http://thinktibits.blogspot.de/2011/03/oracle-xmltable-usage-part-2.html
# http://www.w3schools.com/xpath/xpath_functions.asp

SELECT (em.EQUIPMENT_MODEL_NAME || '_' || em.EQUIPMENT_MODEL_VERSION) as EQUIPMENT_MODEL_VERSION, xmlt.* from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" VARCHAR(16) PATH 'name/text()',
"PROPERTY_IS_READ" CHAR(1) PATH 'if ("read" = action/@type) then "Y" else "N"',
"PROPERTY_IS_WRITE" CHAR(1) PATH 'if ("write" = action/@type) then "Y" else "N"',
"PROPERTY_IS_CALL" CHAR(1) PATH 'if ("call" = action/@type) then "Y" else "N"'
) xmlt order by em.EQUIPMENT_MODEL_NAME, em.EQUIPMENT_MODEL_VERSION;


SELECT xmlt.PROPERTY_NAME, DECODE(xmlt.PROPERTY_READ,'true','Y','false','N') AS IS_READ, DECODE(xmlt.PROPERTY_WRITE,'true','Y','false','N') AS IS_WRITE, DECODE(xmlt.PROPERTY_CALL,'true','Y','false','N') as IS_CALL from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" CHAR(16) PATH 'name/text()',
"PROPERTY_READ" VARCHAR2(6) PATH '"read" = action/@type',
"PROPERTY_WRITE" VARCHAR2(6) PATH '"write" = action/@type',
"PROPERTY_CALL" VARCHAR2(6) PATH '"call" = action/@type'
) AS xmlt where em.EQUIPMENT_MODEL_NAME = 'MX' and em.EQUIPMENT_MODEL_VERSION = 21;

SELECT xmlt.* from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" CHAR(16) PATH 'name/text()',
"PROPERTY_IS_READ" CHAR(1) PATH 'if ("read" = action/@type) then "Y" else "N"',
"PROPERTY_IS_WRITE" CHAR(1) PATH 'if ("write" = action/@type) then "Y" else "N"',
"PROPERTY_IS_CALL" CHAR(1) PATH 'if ("call" = action/@type) then "Y" else "N"'
) AS xmlt where em.EQUIPMENT_MODEL_NAME = 'MX' and em.EQUIPMENT_MODEL_VERSION = 21;

SELECT em.EQUIPMENT_MODEL_NAME, em.EQUIPMENT_MODEL_VERSION, xmlt.* from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" CHAR(16) PATH 'name/text()',
"PROPERTY_IS_READ" CHAR(1) PATH 'if ("read" = action/@type) then "Y" else "N"',
"PROPERTY_IS_WRITE" CHAR(1) PATH 'if ("write" = action/@type) then "Y" else "N"',
"PROPERTY_IS_CALL" CHAR(1) PATH 'if ("call" = action/@type) then "Y" else "N"'
) AS xmlt where property_is_call = 'Y';

SELECT (em.EQUIPMENT_MODEL_NAME || '_' || em.EQUIPMENT_MODEL_VERSION) as EQMODVERSION, xmlt.* from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" CHAR(16) PATH 'name/text()',
"PROPERTY_IS_READ" CHAR(1) PATH 'if ("read" = action/@type) then "Y" else "N"',
"PROPERTY_IS_WRITE" CHAR(1) PATH 'if ("write" = action/@type) then "Y" else "N"',
"PROPERTY_IS_CALL" CHAR(1) PATH 'if ("call" = action/@type) then "Y" else "N"'
) AS xmlt order by em.EQUIPMENT_MODEL_NAME, em.EQUIPMENT_MODEL_VERSION;

propdesc ersatz:

SELECT (em.EQUIPMENT_MODEL_NAME || '_' || em.EQUIPMENT_MODEL_VERSION) as EQUIPMENT_MODEL_VERSION, xmlt.* from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" VARCHAR(16) PATH 'name/text()',
"CATEGORY" VARCHAR(16) PATH '@category',
"IS_READ" CHAR(1) PATH 'if ("read" = action/@type) then "Y" else "N"',
"IS_WRITE" CHAR(1) PATH 'if ("write" = action/@type) then "Y" else "N"',
"IS_CALL" CHAR(1) PATH 'if ("call" = action/@type) then "Y" else "N"',
"MEDLOCK_READ" VARCHAR(16) PATH 'action[@type="read"]/@medlock',
"MEDLOCK_WRITE" VARCHAR(16) PATH 'action[@type="write"]/@medlock',
"ACCESS_READ" VARCHAR(16) PATH 'action[@type="read"]/@access',
"ACCESS_WRITE" VARCHAR(16) PATH 'action[@type="write"]/@access',
"PARA_COUNT_READ" NUMBER PATH 'count(action[@type="read"]/para/*) + count(para[../action[@type="read"]]/*)',
"PARA_COUNT_WRITE" NUMBER PATH 'count(action[@type="write"]/para/*) + count(para[../action[@type="write"]]/*)',
"DATA_COUNT_READ" NUMBER PATH 'count(action[@type="read"]/data/*) + count(data[../action[@type="read"]]/*)',
"DATA_COUNT_WRITE" NUMBER PATH 'count(action[@type="write"]/data/*) + count(data[../action[@type="write"]]/*)'
) AS xmlt;

SELECT xmlt.*, (em.EQUIPMENT_MODEL_NAME || '_' || em.EQUIPMENT_MODEL_VERSION) as XML_ORIGIN from EQUIPMENT_MODELS em,
XMLTABLE('//eqmod/properties/property' PASSING em.EQUIPMENT_MODEL_XML
COLUMNS
"PROPERTY_NAME" VARCHAR(16) PATH 'name/text()',
"CATEGORY" VARCHAR(16) PATH '@category',
"IS_READ" CHAR(1) PATH 'if ("read" = action/@type) then "Y" else "N"',
"IS_WRITE" CHAR(1) PATH 'if ("write" = action/@type) then "Y" else "N"',
"IS_CALL" CHAR(1) PATH 'if ("call" = action/@type) then "Y" else "N"',
"MEDLOCK_READ" VARCHAR(16) PATH 'action[@type="read"]/@medlock',
"MEDLOCK_WRITE" VARCHAR(16) PATH 'action[@type="write"]/@medlock',
"ACCESS_READ" VARCHAR(16) PATH 'action[@type="read"]/@access',
"ACCESS_WRITE" VARCHAR(16) PATH 'action[@type="write"]/@access',
"PARA_READ_NODES" NUMBER PATH 'count(action[@type="read"]/para/*) + count(para[../action[@type="read"]]/*)',
"PARA_WRITE_NODES" NUMBER PATH 'count(action[@type="write"]/para/*) + count(para[../action[@type="write"]]/*)',
"DATA_READ_NODES" NUMBER PATH 'count(action[@type="read"]/data/*) + count(data[../action[@type="read"]]/*)',
"DATA_WRITE_NODES" NUMBER PATH 'count(action[@type="write"]/data/*) + count(data[../action[@type="write"]]/*)'
) AS xmlt
where (em.equipment_model_name = 'DGX' and em.equipment_model_version = '05')
or (em.equipment_model_name = 'DEFAULT-USRS' and em.equipment_model_version = '00')
order by xmlt.CATEGORY, xmlt.PROPERTY_NAME;

-- RaphaelMueller - 03 Apr 2012