从XML到Oracle PL/SQL环境中的路径列表 [英] From XML to list of paths in Oracle PL/SQL environment
问题描述
请假定您有一个XML文件(例如,存储在还具有CLOB列的Oracle表中):
Please suppose you have a XML file (stored, for example, in an Oracle table which has also a CLOB column):
<ALFA>
<BETA>0123</BETA>
<GAMMA>2345</GAMMA>
<DELTA>
<EPSILON>3</EPSILON>
</DELTA>
</ALFA>
如何在输出中生成所有可能路径的列表?
How can I produce, in output, the list of all possible paths?
/ALFA/BETA/text()
/ALFA/GAMMA/text()
/ALFA/DELTA/EPSILON/text()
我的需求如下:我必须从长XML中提取许多信息,并且必须将XMLEXTRACT与所有可能的路径一起使用,因此我想知道是否有可能自动将它们"dbms_output.put_line"方式.
My need is the following: I have to EXTRACT many information from a long XML and I have to use XMLEXTRACT with all possible paths, so I would like to know if is it possible to "dbms_output.put_line" them in an automatic way.
我需要一个独立于标签名称的解决方案.
I need a solution which is independent from the name of the tags.
请假定XML格式正确.
Please suppose that the XML is well-formed.
在此先感谢您的帮助.
- 在第二种情况下:
如果尚未安装Oracle Java Extension,如何处理并收到以下错误?
How can I proceed if Oracle Java Extension has not been installed, and I receive the following error?
ORA-19112: error raised during evaluation:
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
请假设我不是DBA,并且DBA不授权Oracle Java Extension安装.
Please suppose that I am not DBA, and DBA don't authorize Oracle Java Extension installation.
推荐答案
您可以使用 XMLTable
以使用XQuery生成路径列表.
You can use XMLTable
to produce list of paths with XQuery.
例如
( SQLFiddle )
with params as (
select
xmltype('
<ALFA>
<BETA>0123</BETA>
<GAMMA>2345</GAMMA>
<DELTA>
<EPSILON>3</EPSILON>
</DELTA>
</ALFA>
') p_xml
from dual
)
select
path_name || '/text()'
from
XMLTable(
'
for $i in $doc/descendant-or-self::*
return <element_path> {$i/string-join(ancestor-or-self::*/name(.), ''/'')} </element_path>
'
passing (select p_xml from params) as "doc"
columns path_name varchar2(4000) path '//element_path'
)
但这是一种错误的方式,至少是因为它没有达到预期的效果.
but it's a wrong way at least because it's not effective as it can.
只需使用相同的XQuery提取所有值: ( SQLFiddle )
Just extract all values with same XQuery: (SQLFiddle)
with params as (
select
xmltype('
<ALFA>
<BETA>0123</BETA>
<GAMMA>2345</GAMMA>
<DELTA>
<EPSILON>3</EPSILON>
</DELTA>
</ALFA>
') p_xml
from dual
)
select
element_path, element_text
from
XMLTable(
'
for $i in $doc/descendant-or-self::*
return <element>
<element_path> {$i/string-join(ancestor-or-self::*/name(.), ''/'')} </element_path>
<element_content> {$i/text()}</element_content>
</element>
'
passing (select p_xml from params) as "doc"
columns
element_path varchar2(4000) path '//element_path',
element_text varchar2(4000) path '//element_content'
)
这篇关于从XML到Oracle PL/SQL环境中的路径列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!