Parse Xml node values from CLOB field in ORACLE

Question

I am trying to parse XML Node values from CLOB field and it is throwing invalid Token Error: XML:

create table traptabclob(testclob clob);
insert into traptabclob values('<?xml version="1.0" encoding="UTF-8"?>
<tns:InputOutputScoringDetails xmlns:tns="jdhajdjh"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="dasd.xsd ">
     <tns:InputsOutputs>
               <tns:NameValue>                                                            
                   <tns:Name>ABSC_APPLICANTBUREAUSCORE</tns:Name>
                   <tns:Value>0.11030000000000001</tns:Value>
               </tns:NameValue>
               <tns:NameValue>
                    <tns:Name>ABS_BN_SCORE_DETAIL_PK</tns:Name>
                    <tns:Value>10035</tns:Value>
               </tns:NameValue>
      </tns:InputsOutputs>
</tns:InputOutputScoringDetails>
                               ');

SQL Query:

SELECT EXTRACTVALUE(xmltype(testclob), '/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name')
FROM traptabclob;

Can anyone help me as how can I use xmltype and extractvalue to extract values for Name or Value nodes?

thanks

The tns; is an xml namespace. You need to tell extract value about the namespaces, by passing a third argument giving the space-separated list of the xmlns values. So, the third argument would be 'xmlns:tns="jdhajdjh"'. — Devon_C_Miller
– Devon_C_Miller, Commented Mar 24, 2021 at 21:39

Alex Poole · Accepted Answer · 2021-03-24 19:18:12Z

The invalid token is the tns: namespace prefix. You need to supply the tns namespace information as part of the query:

SELECT EXTRACTVALUE(
    xmltype(testclob),
    '/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name',
    'xmlns:tns="jdhajdjh"'
  ) AS result
FROM traptabclob;

Although extractvalue has been deprecated for a long time, so you could use XMLQuery instead:

SELECT XMLQuery(
    'declare namespace tns="jdhajdjh";
    /tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name/text()'
    PASSING xmltype(testclob)
    RETURNING CONTENT
  ) AS result
FROM traptabclob;

Both of those get:

RESULT
-------------------------
ABSC_APPLICANTBUREAUSCORE

Depending on what you're doing you might find it more convenient to use XMLTable:

SELECT x.name, x.value
FROM traptabclob t
CROSS APPLY XMLTable(
  XMLNamespaces ('jdhajdjh' AS "tns"),
  '/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue'
  PASSING xmltype(testclob)
  COLUMNS
    name VARCHAR2(30) PATH 'tns:Name',
    value NUMBER PATH 'tns:Value'
) x;

which gets:

NAME                                          VALUE
------------------------------ --------------------
ABSC_APPLICANTBUREAUSCORE        .11030000000000001
ABS_BN_SCORE_DETAIL_PK                        10035

db<>fiddle

Collectives™ on Stack Overflow

Parse Xml node values from CLOB field in ORACLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related