如何将嵌套的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>
在这个xml中,person是表名,name是归档名,Tom是其提交的值.
地址是一个子表,国家和城市是地址中的两列.我想把person行插入个人表,如果失败,不要插入地址表.这个xml可能很大最好的解决方案是什么？
您可以将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,'/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;
