Oracle 10g: Extract data (select) from XML (CLOB Type)

Question

I'm new in Oracle and I've - maybe trivial - a problem in a select. (I'm using Oracle 10g Express Edition).

I've a DB with a field CLOB: mytab.xml This column have an XML like this:

<?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?

dogbane · Accepted Answer · 2011-02-03 09:46:08Z

33

Try this instead:

select xmltype(t.xml).extract('//fax/text()').getStringVal() from mytab t

answered Feb 3, 2011 at 9:46

dogbane

276k77 gold badges407 silver badges415 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mymark Over a year ago

not yet:ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.XMLTYPE", line 254 ORA-06512: at line 1

mymark Over a year ago

Sorry.... yes, your answer work perfectly! In previous case I've got an xml null ^^!

user272735 Over a year ago

In 11g I can leave .getStringVal()-part out. No idea about 10g as I don't have any here.

Kiquenet · Accepted Answer · 2015-05-14 13:28:40Z

3

Try using 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.

edited May 14, 2015 at 13:28

Kiquenet

15.2k36 gold badges152 silver badges250 bronze badges

answered Feb 20, 2012 at 21:50

Silva

3705 silver badges17 bronze badges

Comments

sɐunıɔןɐqɐp · Accepted Answer · 2020-08-10 06:35:05Z

1

In case the XML store in the CLOB field in the database table. E.g for this XML:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Awmds>
    <General_segment>
        <General_segment_id>
        <Customs_office_code>000</Customs_office_code>
    </General_segment_id>
</General_segment>
</Awmds>

This is the Extract Query:

SELECT EXTRACTVALUE (
    xmltype (T.CLOB_COLUMN_NAME),
    '/Awmds/General_segment/General_segment_id/Customs_office_code')
    AS Customs_office_code
FROM TABLE_NAME t;

edited Aug 10, 2020 at 6:35

sɐunıɔןɐqɐp

3,59516 gold badges41 silver badges43 bronze badges

answered Aug 10, 2020 at 6:05

Yasir Arafat

112 bronze badges

Comments

user6691970 · Accepted Answer · 2013-05-29 11:32:28Z

0

this query runs perfectly in my case

select xmltype(t.axi_content).extract('//Lexis-NexisFlag/text()').getStringVal() from ax_bib_entity t

answered May 29, 2013 at 11:32

user6691970

1

Comments

neubert · Accepted Answer · 2014-04-11 13:23:24Z

0

You can achieve with below queries

select extract(xmltype(xml), '//fax/text()').getStringVal() from mytab;
select extractvalue(xmltype(xml), '//fax') from mytab;

edited Apr 11, 2014 at 13:23

neubert

17k28 gold badges140 silver badges263 bronze badges

answered Apr 11, 2014 at 13:03

Ravi

1

Comments

S.Roshanth · Accepted Answer · 2016-06-16 07:11:01Z

0

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.

   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_);

Refer more about DBMS_XMLDOM methods here.

answered Jun 16, 2016 at 7:11

S.Roshanth

1,5173 gold badges25 silver badges37 bronze badges

Comments

O'sama · Accepted Answer · 2017-03-08 11:44:37Z

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

Collectives™ on Stack Overflow

Oracle 10g: Extract data (select) from XML (CLOB Type)

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related