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

查看:196
本文介绍了oracle plsql:如何解析XML并插入到表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何将嵌套的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是其文件值. 地址是一个子表,州和城市是地址内的两列.我想将人员行插入到人员表中,如果失败,请不要将其插入地址表中.这个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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆