Reputation: 724
Easiest way to parse XML using pl/sql dom parser
I have no idea how to explain but I will try my best. I am using Oracle Apex and have XML in my table called "CLOBTABLE" with datatype Clob. I have tried XMLTYPE as a datatype but its not accepting any data in apex, so i am using CLOB Currently as a data type. now i want to use data which is in xml form i.e.
<Row>
<cellArray>
<Cell>
<columnId>1</columnId>
<valueArray>
<Value>
<value>IR000024575452</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>5</columnId>
<valueArray>
<Value>
<value>AZ12579856-001</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>2</columnId>
<valueArray>
<Value>
<value>IT06686</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>9</columnId>
<valueArray>
<Value>
<value>Hu Mics Metab K</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>8</columnId>
<valueArray>
<Value>
<value>2006-03-31</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>7</columnId>
<valueArray>
<Value>
<value>2006-07-27</value>
</Value>
</valueArray>
</Cell>
</cellArray>
</Row>
<Row>
<cellArray>
<Cell>
<columnId>1</columnId>
<valueArray>
<Value>
<value>IR000024575452</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>5</columnId>
<valueArray>
<Value>
<value>AZ12579856-001</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>2</columnId>
<valueArray>
<Value>
<value>IT06686</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>9</columnId>
<valueArray>
<Value>
<value>Hu Mics Metab K</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>8</columnId>
<valueArray>
<Value>
<value>2006-03-31</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>7</columnId>
<valueArray>
<Value>
<value>2006-07-27</value>
</Value>
</valueArray>
</Cell>
</cellArray>
</Row>
and want to parse this data and insert it in another table called "Hyp". each data corresponds to the column id of the database table and rest are the column values. i have tried Oracle XMLTYPE parser which is
DECLARE
indoc VARCHAR2(2000);
indomdoc DBMS_XMLDOM.DOMDocument;
innode DBMS_XMLDOM.DOMNode;
myparser DBMS_XMLPARSER.parser;
buf VARCHAR2(2000);
BEGIN
indoc := '<emp><name>De Selby</name></emp>';
myParser := DBMS_XMLPARSER.newParser;
DBMS_XMLPARSER.parseBuffer(myParser, indoc);
indomdoc := DBMS_XMLPARSER.getDocument(myParser);
innode := DBMS_XMLDOM.makeNode(indomdoc);
DBMS_XMLDOM.writeToBuffer(innode, buf);
DBMS_OUTPUT.put_line(buf);
DBMS_XMLDOM.freeDocument(indomdoc);
DBMS_XMLPARSER.freeParser(myParser);
END;
but now i want to instead of using static content i.e. De Selby give dynamic content i.e. from that "CLOBTABLE" xml is given, data is parsed and inserted into "Hyp" table. I don't know how to do it. Any help will be greatly appreciated.
Upvotes: 2
Views: 20112
Answers (1)
Reputation: 7028
apex.oracle.com has the last apex version and db version (which is 11gR2 atm). This allows usage of xmltables.
Please note that i had to wrap the data in tags to have valid xml (otherwise it would take as the main element, doesn't parse)
DECLARE
l_xmlclob CLOB :=
'<data>
<Row>
<cellArray>
<Cell>
<columnId>1</columnId>
<valueArray>
<Value>
<value>IR000024575452</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>5</columnId>
<valueArray>
<Value>
<value>AZ12579856-001</value>
</Value>
</valueArray>
</Cell>
</cellArray>
</Row>
<Row>
<cellArray>
<Cell>
<columnId>1</columnId>
<valueArray>
<Value>
<value>IR000024575452</value>
</Value>
</valueArray>
</Cell>
<Cell>
<columnId>5</columnId>
<valueArray>
<Value>
<value>AZ12579856-001</value>
</Value>
</valueArray>
</Cell>
</cellArray>
</Row>
</data>';
BEGIN
FOR r IN ( SELECT rownum rn, cells
FROM xmltable('/data/Row' passing XMLTYPE(l_xmlclob)
columns CELLS XMLTYPE PATH './cellArray')
)
LOOP
DBMS_OUTPUT.PUT_LINE('Row: '||r.rn);
FOR c IN ( SELECT colid, colval
FROM xmltable('/cellArray/Cell' passing r.cells
columns COLID NUMBER PATH './columnId',
COLVAL VARCHAR(20) PATH './valueArray/Value/value')
)
LOOP
DBMS_OUTPUT.PUT_LINE('colid, col value: '||c.colid||', '||c.colval);
END LOOP;
END LOOP;
END;
Output:
Row: 1
colid, col value: 1, IR000024575452
colid, col value: 5, AZ12579856-001
Row: 2
colid, col value: 1, IR000024575452
colid, col value: 5, AZ12579856-001
Say that if you want actual columns, you could try this:
FOR r IN ( SELECT rownum rn, cells
FROM xmltable('/data/Row' passing XMLTYPE(l_xmlclob)
columns CELLS XMLTYPE PATH './cellArray')
)
LOOP
DBMS_OUTPUT.PUT_LINE('Row: '||r.rn);
FOR c IN ( SELECT col1, col2, col3, col4, col5, col6
FROM xmltable('/cellArray/Cell' passing r.cells
columns COL1 VARCHAR(20) PATH './valueArray/Value/value[//columnId[contains(text(), "1")]]',
COL2 VARCHAR(20) PATH './valueArray/Value/value[//columnId[contains(text(), "2")]]',
COL3 VARCHAR(20) PATH './valueArray/Value/value[//columnId[contains(text(), "3")]]',
COL4 VARCHAR(20) PATH './valueArray/Value/value[//columnId[contains(text(), "4")]]',
COL5 VARCHAR(20) PATH './valueArray/Value/value[//columnId[contains(text(), "5")]]',
COL6 VARCHAR(20) PATH './valueArray/Value/value[//columnId[contains(text(), "6")]]'
)
)
LOOP
DBMS_OUTPUT.PUT_LINE('col values: col1: '||c.col1||', col2: '||c.col2||', col3: '||c.col3||', col4: '||c.col4||', col5: '||c.col5||', col6: '||c.col6);
END LOOP;
END LOOP;
Output:
Row: 1
col values: col1: IR000024575452, col2: , col3: , col4: , col5: , col6:
col values: col1: , col2: , col3: , col4: , col5: AZ12579856-001, col6:
Row: 2
col values: col1: IR000024575452, col2: , col3: , col4: , col5: , col6:
col values: col1: , col2: , col3: , col4: , col5: AZ12579856-001, col6:
Just a note though: your values are a weird structure... valuearray>value>value? Value is wrapped in value, and why would a single cell have multiple values (array)? If this is the case, ever, in your xml, you'll need to adapt.
Upvotes: 6