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    lt  xml version  1 0  encoding  iso-8859-1   gt   lt info gt   lt id gt  954  lt  id gt   lt idboss gt  954  lt  idboss gt   lt name gt  Fausto  lt  name gt   lt sorname gt  Anonimo  lt  sorname gt   lt phone gt  040000000  lt  phone gt   lt fax gt  040000001  lt  fax gt   lt  info gt    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

User · Answer

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;

User · Answer

You can achieve with below queries    select extract xmltype xml      fax text     getStringVal   from mytab  select extractvalue xmltype xml      fax   from mytab

User · Answer

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

User · Answer

this query runs perfectly in my case  select xmltype t axi content  extract    Lexis-NexisFlag text     getStringVal   from ax bib entity t

User · Answer

Try this instead   select xmltype t xml  extract    fax text     getStringVal   from mytab t

User · Answer

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

User · Answer

In case of      lt  xml version  1 0  encoding  iso-8859-1   gt   lt info xmlns  http   namespaces default  xmlns ns2  http   namespaces ns2   gt       lt id gt  954  lt  id gt       lt idboss gt  954  lt  idboss gt       lt name gt  Fausto  lt  name gt       lt sorname gt  Anonimo  lt  sorname gt       lt phone gt  040000000  lt  phone gt       lt fax gt  040000001  lt  fax gt   lt  info gt    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

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

Examples related to xml

Examples related to oracle

Examples related to oracle10g

Examples related to ora-00932