oracle plsql:如何解析 XML 并插入到表中 [英] oracle plsql: how to parse XML and insert into table

问题描述

如何将嵌套的 xml 文件加载到数据库表中?

How to load a nested xml file into database table ?

<?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>       

在这个xml中，person是表名，name是归档名，Tom是它的归档值.地址是一个子表，州和城市是地址内的两列.我想将 person 行插入到 person 表中，如果失败，不要插入到 address 表中.这个 xml 可能非常大.这样做的最佳解决方案是什么?

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 文档加载到 XMLType 中，然后对其进行查询，例如:

You can load an XML document into an XMLType, then query it, e.g.:

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;

这篇关于oracle plsql:如何解析 XML 并插入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！