Oracle 10g：从 XML 中提取数据（选择）（CLOB 类型）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4884421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 10g: Extract data (select) from XML (CLOB Type)
提问by mymark
I'm new in Oracle and I've - maybe trivial - a problem in a select. (I'm using Oracle 10g Express Edition).
我是 Oracle 的新手，我在选择中遇到了 - 也许微不足道的问题。（我使用的是 Oracle 10g Express 版）。
I've a DB with a field CLOB: mytab.xml This column have an XML like this:
我有一个带有 CLOB 字段的数据库：mytab.xml 此列有一个像这样的 XML：
<?xml version="1.0" encoding="iso-8859-1"?>
<info>
<id> 954 </id>
<idboss> 954 </idboss>
<name> Fausto </name>
<sorname> Anonimo </sorname>
<phone> 040000000 </phone>
<fax> 040000001 </fax>
</info>
I'm trying to do a 'simple' select to get, for example, the value of 'fax' tag. But I've a bit of problem and I'm not able to understand my error. For example:
我正在尝试进行“简单”选择以获取例如“传真”标签的值。但我有一点问题，我无法理解我的错误。例如：
select extract(xml, '//fax').getStringVal() from mytab;
ORA-00932: inconsistent datatypes: expected - got
select extract(xmltype(xml), '//fax').getStringVal() from mytab;
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 254
I've tryed also with 'extractvalue', but I've the same problems. where I'm wrong to do this?
我也尝试过使用“extractvalue”，但我遇到了同样的问题。我这样做错在哪里？
回答by dogbane
Try this instead:
试试这个：
select xmltype(t.xml).extract('//fax/text()').getStringVal() from mytab t
回答by Silva
Try using xmltype.createxml(xml).
尝试使用xmltype.createxml(xml).
As in,
就像在，
select extract(xmltype.createxml(xml), '//fax').getStringVal() from mytab;
It worked for me.
它对我有用。
If you want to improve or manipulate even further.
如果您想进一步改进或操纵。
Try something like this.
尝试这样的事情。
Select *
from xmltable(xmlnamespaces('some-name-space' as "ns",
'another-name-space' as "ns1",
),
'/ns/ns1/foo/bar'
passing xmltype.createxml(xml)
columns id varchar2(10) path '//ns//ns1/id',
idboss varchar2(500) path '//ns0//ns1/idboss',
etc....
) nice_xml_table
Hope it helps someone.
希望它可以帮助某人。
回答by user6691970
this query runs perfectly in my case
这个查询在我的情况下运行完美
select xmltype(t.axi_content).extract('//Lexis-NexisFlag/text()').getStringVal() from ax_bib_entity t
回答by Ravi
You can achieve with below queries
您可以通过以下查询实现
select extract(xmltype(xml), '//fax/text()').getStringVal() from mytab;select extractvalue(xmltype(xml), '//fax') from mytab;
select extract(xmltype(xml), '//fax/text()').getStringVal() from mytab;select extractvalue(xmltype(xml), '//fax') from mytab;
回答by S.Roshanth
You can try creating DBMS_XMLPARSER.parser object from the CLOB XML and get a DBMS_XMLDOM.DOMDocument object from it. Then use DBMS_XMLDOM package methods to get the value of any node.
您可以尝试从 CLOB XML 创建 DBMS_XMLPARSER.parser 对象并从中获取 DBMS_XMLDOM.DOMDocument 对象。然后使用 DBMS_XMLDOM 包方法来获取任何节点的值。
xml_ CLOB := 'X';
p DBMS_XMLPARSER.parser;
doc_ DBMS_XMLDOM.DOMDocument;
-- Convert the CLOB into a XML-document
P := DBMS_XMLPARSER.newparser();
-- Parse the clob and get the XML-document
DBMS_XMLPARSER.parseclob(p, xml_);
doc_ := DBMS_XMLPARSER.getDocument(p);
Then use the below methods to extract node value
然后使用以下方法提取节点值
DBMS_XMLDOM.getElementsByTagName(doc_, 'NodeName'); DBMS_XMLDOM.GetNodeValue(node_obj_);
DBMS_XMLDOM.getElementsByTagName(doc_, 'NodeName'); DBMS_XMLDOM.GetNodeValue(node_obj_);
Refer more about DBMS_XMLDOM methods here.
在此处参考有关 DBMS_XMLDOM 方法的更多信息。
回答by O'sama
In case of :
的情况下 ：
<?xml version="1.0" encoding="iso-8859-1"?>
<info xmlns="http://namespaces.default" xmlns:ns2="http://namespaces.ns2" >
<id> 954 </id>
<idboss> 954 </idboss>
<name> Fausto </name>
<sorname> Anonimo </sorname>
<phone> 040000000 </phone>
<fax> 040000001 </fax>
</info>
Query :
询问 ：
Select *
from xmltable(xmlnamespaces(default 'http://namespaces.default'
'http://namespaces.ns2' as "ns",
),
'/info'
passing xmltype.createxml(xml)
columns id varchar2(10) path '/id',
idboss varchar2(500) path '/idboss',
etc....
) nice_xml_table
