使用oracle查询提取xml数据 [英] Extract xml data using oracle query
问题描述
Oracle 11g版
Oracle version 11g
HI， 当尝试读取LOOP中的XML时，查询给出了错误，但是，如果我将其更改为不存在的路径，则查询运行正常.但是，@Name未提取是预期的.我应该在这里更正什么，以使其提供期望的输出，如下所示:
HI , When trying to read the XML in a LOOP the query is giving an error but if, I change this to a non existing path then the query is running fine . However the @Name is not fetching is the expected . What should I correct here to make it give the desired output given below:
上述不存在的路径，从名称for $i in AuxiliaryObject/Row return <C>{$i}<R>{AuxiliaryObject/@NAM}
Non exsisting path described above, removed the E from the name : for $i in AuxiliaryObject/Row return <C>{$i}<R>{AuxiliaryObject/@NAM}
代码:
SELECT *
FROM XMLTABLE (
'<C> {for $i in AuxiliaryObject/Row return <C>{$i}<R>{AuxiliaryObject/@NAM}</R></C>}</C>/C'
PASSING xmltype(
'<AuxiliaryType>
<AuxiliaryObject id="1" NAME="Provider_P107">
<Row>
<Index_id>1</Index_id>
<Provider_ID_description>GNRCN</Provider_ID_description>
<Provider_ID>GNRCN</Provider_ID>
</Row>
<Row>
<Index_id>2</Index_id>
<Provider_ID_description>EGUT12</Provider_ID_description>
<Provider_ID>EGUT12 </Provider_ID>
</Row>
</AuxiliaryObject>
<AuxiliaryObject id="2" NAME="Provider_P108">
<Row>
<Index_id>1</Index_id>
<Provider_ID_description>GNRCN</Provider_ID_description>
<Provider_ID>GNRCN</Provider_ID>
</Row>
<Row>
<Index_id>2</Index_id>
<Provider_ID_description>EGUT</Provider_ID_description>
<Provider_ID>EGUT </Provider_ID>
</Row>
</AuxiliaryObject>
</AuxiliaryType>'
).EXTRACT ('AuxiliaryType/*')
COLUMNS
Name varchar (30) Path 'R/@NAME',
Index_Id VARCHAR2 (10) PATH 'Row/Index_id',
Provider_id_description VARCHAR2 (30) PATH 'Row/Provider_ID_description',
provider_id VARCHAR2 (30) PATH 'Row/Provider_ID')
输出:所需:
ID, Provider_Name, Index, Provider_ID_description, Provider_ID
1 Provider_P107 1 GNRCN GNRCN
1 Provider_P107 2 INDF1 INDF1
2 Provider_P108 2 EGUT12 EGUT12
2 Provider_P108 1 EGUT EGUT
输出结果在sqlfiddle链接中.
Output coming is in the sqlfiddle link.
以上引用是对此的链接: 从XML中提取数据并将其加载到桌子
The above Queation is a link to this : Extract data from a XML and load it into a table
当我在Toad上运行查询时:输出为:
When I run the query on Toad : Output is :
NAME INDEX_ID PROVIDER_ID_DESCRIPTION PROVIDER_ID
Provider_P107Provider_P108 1 GNRCN GNRCN
Provider_P107Provider_P108 2 EGUT12 EGUT12
Provider_P107Provider_P108 1 GNRCN GNRCN
Provider_P107Provider_P108 2 EGUT EGUT
推荐答案
我将分阶段提取数据:
SELECT xobjects.id, xobjects.name, xrows.index_id,
xrows.provider_id_description, xrows.provider_id
FROM XMLTABLE(
'/AuxiliaryType/AuxiliaryObject'
PASSING xmltype(
'<AuxiliaryType>
<AuxiliaryObject id="1" NAME="Provider_P107">
<Row>
<Index_id>1</Index_id>
<Provider_ID_description>GNRCN</Provider_ID_description>
<Provider_ID>GNRCN</Provider_ID>
</Row>
<Row>
<Index_id>2</Index_id>
<Provider_ID_description>EGUT12</Provider_ID_description>
<Provider_ID>EGUT12 </Provider_ID>
</Row>
</AuxiliaryObject>
<AuxiliaryObject id="2" NAME="Provider_P108">
<Row>
<Index_id>1</Index_id>
<Provider_ID_description>GNRCN</Provider_ID_description>
<Provider_ID>GNRCN</Provider_ID>
</Row>
<Row>
<Index_id>2</Index_id>
<Provider_ID_description>EGUT</Provider_ID_description>
<Provider_ID>EGUT </Provider_ID>
</Row>
</AuxiliaryObject>
</AuxiliaryType>'
)
COLUMNS
name VARCHAR2(30) PATH '@NAME',
id VARCHAR2(10) PATH '@id',
xrows XMLTYPE PATH 'Row') xobjects,
XMLTABLE(
'/Row'
PASSING xobjects.xrows
COLUMNS
index_id VARCHAR2(10) PATH 'Index_id',
provider_id_description VARCHAR2(30) PATH 'Provider_ID_description',
provider_id VARCHAR2(30) PATH 'Provider_ID') xrows;
XMLTable xobjects包含来自原始XML文本的AuxiliaryType中的每个AuxiliaryObject实例.它具有属性name和id，以及包含嵌套行的子XMLType.第二个XMLTable xrows进行了扩展，以便可以提取元素. XML类型的联接和传递创建了层次结构，该层次结构提供了所需的输出:
The XMLTable xobjects contains each of the AuxiliaryObject instances within the AuxiliaryType, from your original XML text. It has the attributes name and id, plus a sub-XMLType containing the nested rows. The second XMLTable, xrows, expands that so the elements can be extracted. The joins and passing of the XML types creates the hierarchy that gives the output you want:
ID NAME INDEX_ID PROVIDER_ID_DESCRIPTION PROVIDER_ID
---------- ------------------------------ ---------- ------------------------------ ------------------------------
1 Provider_P107 1 GNRCN GNRCN
1 Provider_P107 2 EGUT12 EGUT12
2 Provider_P108 1 GNRCN GNRCN
2 Provider_P108 2 EGUT EGUT
这适用于针对11.2.0.3数据库的SQL Developer和 SQL提琴 a>.
This works in SQL Developer against an 11.2.0.3 database, and in SQL Fiddle.
此答案的基于CTE的早期版本也可以在SQL Developer中使用，但 SQL Fiddle 出现ORA-600错误；以及您在问题中遇到的问题，这可能表明SQL Fiddle处于未修补或至少修补不同的11gR2版本上，该版本在XML处理方面存在错误.
An earlier CTE-based version of this answer also worked in SQL Developer but SQL Fiddle got an ORA-600 error; that along with the issue you had in the question suggests maybe SQL Fiddle is on an unpatched, or at least differently patched, version of 11gR2 which has bugs in the XML handling.
这篇关于使用oracle查询提取xml数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
