oracle 使用 pl/sql dom 解析器解析 XML 的最简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11782423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easiest way to parse XML using pl/sql dom parser
提问by IConfused
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.
我不知道如何解释，但我会尽力而为。我正在使用 Oracle Apex 并且在我的名为“CLOBTABLE”的表中有 XML，数据类型为 Clob。我曾尝试将 XMLTYPE 作为数据类型，但它不接受 apex 中的任何数据，因此我使用 CLOB 当前作为数据类型。现在我想使用xml形式的数据，即
<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
并希望解析此数据并将其插入另一个名为“Hyp”的表中。每个数据对应于数据库表的列 id，其余是列值。我试过 Oracle XMLTYPE 解析器，它是
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.
但现在我想而不是使用静态内容，即 De Selby 给出动态内容，即从给出的“CLOBTABLE”xml 中，数据被解析并插入到“Hyp”表中。我不知道该怎么做。任何帮助将不胜感激。
回答by Tom
apex.oracle.com has the last apex version and db version (which is 11gR2 atm). This allows usage of xmltables.
apex.oracle.com 有最新的 apex 版本和 db 版本（即 11gR2 atm）。这允许使用 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)
请注意，我必须将数据包装在标签中才能获得有效的 xml（否则它将作为主要元素，不解析）
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.
不过请注意：您的值是一个奇怪的结构... valuearray>value>value？值包含在值中，为什么单个单元格会有多个值（数组）？如果是这种情况，在您的 xml 中，您将需要进行调整。
