oracle plsql：如何解析 XML 并插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12982687/
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
oracle plsql: how to parse XML and insert into table
提问by Frank
How to load a nested xml file into database table ?
如何将嵌套的 xml 文件加载到数据库表中？
<?xml version="1.0" ?>
<person>
<row>
<name>Tom</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
<row>
<name>Jim</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
</person>
In this xml, person is the table name , name is the filed name, Tom is its filed value. Address is a subtable and state and city is two column inside Address. I want to insert the person row into person table, if it failed , do not insert into address table. This xml could be very big. What's the best solution to do this ?
在这个 xml 中，person 是表名，name 是归档名称，Tom 是它的归档值。地址是一个子表，州和城市是地址内的两列。我想将 person 行插入到 person 表中，如果失败，不要插入到 address 表中。这个 xml 可能非常大。这样做的最佳解决方案是什么？
回答by Jeffrey Kemp
You can load an XML document into an XMLType, then query it, e.g.:
您可以将 XML 文档加载到 XMLType 中，然后对其进行查询，例如：
DECLARE
x XMLType := XMLType(
'<?xml version="1.0" ?>
<person>
<row>
<name>Tom</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
<row>
<name>Jim</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
</person>');
BEGIN
FOR r IN (
SELECT ExtractValue(Value(p),'/row/name/text()') as name
,ExtractValue(Value(p),'/row/Address/State/text()') as state
,ExtractValue(Value(p),'/row/Address/City/text()') as city
FROM TABLE(XMLSequence(Extract(x,'/person/row'))) p
) LOOP
-- do whatever you want with r.name, r.state, r.city
END LOOP;
END;
回答by daggett
select *
FROM XMLTABLE('/person/row'
PASSING
xmltype('
<person>
<row>
<name>Tom</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
<row>
<name>Jim</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
</person>
')
COLUMNS
--describe columns and path to them:
name varchar2(20) PATH './name',
state varchar2(20) PATH './Address/State',
city varchar2(20) PATH './Address/City'
) xmlt
;
回答by Hemant Kumar Bankey
CREATE OR REPLACE PROCEDURE ADDEMP
(xml IN CLOB)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPID,EMPNAME,EMPDETAIL,CREATEDBY,CREATED)
SELECT
ExtractValue(column_value,'/ROOT/EMPID') AS EMPID
,ExtractValue(column_value,'/ROOT/EMPNAME') AS EMPNAME
,ExtractValue(column_value,'/ROOT/EMPDETAIL') AS EMPDETAIL
,ExtractValue(column_value,'/ROOT/CREATEDBY') AS CREATEDBY
,ExtractValue(column_value,'/ROOT/CREATEDDATE') AS CREATEDDATE
FROM TABLE(XMLSequence( XMLType(xml))) XMLDUMMAY;
COMMIT;
END;
