Extract XML tag with namespace

Question

I need to extract a XML tag value as in the code below.

set serveroutput on declare lv_xml varchar2(500); xmlstring XMLTYPE; lv_val varchar2(10); BEGIN lv_xml := '<?xml version="1.0" encoding="UTF-8"?> <TestMain xmlns="http://www.w3.org/2001/XMLSchema-instance"> <testTag1>VAL 1</testTag1> <testTag2>VAL 2</testTag2> <testTag3>VAL 3</testTag3> </TestMain>'; xmlstring := XMLTYPE.CREATEXML(lv_xml); SELECT EXTRACTVALUE(VALUE(fd), 'testTag2') INTO lv_val FROM TABLE(XMLSEQUENCE(xmlstring.EXTRACT('TestMain'))) f, TABLE(XMLSEQUENCE(EXTRACT(VALUE(f), 'TestMain/testTag2'))) fd; dbms_output.put_line('Val: '||lv_val); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error: '||SQLCODE||' '||SQLERRM); END; /

When I am running it, I am getting an exception ORA-01403: no data found.

I found that, if I remove the XML namespace xmlns="http://www.w3.org/2001/XMLSchema-instance", then it is working fine. But, the XML stream from which I will extract values will be in this format(that is, it will contain a namespace).

Is there any way to be able extract value with xmlns present?

A 10 second search finds the documentation for EXTRACT that indicates that it can take a further optional parameter called namespace_string. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 17, 2014 at 14:36

peter.hrasko.sk · Accepted Answer · 2014-07-18 15:31:33Z

Here you are:

declare
    lv_xml     varchar2(500);
    xmlstring  XMLTYPE;
    lv_val     varchar2(10);
BEGIN
    lv_xml :=
    '<?xml version="1.0" encoding="UTF-8"?>
    <TestMain xmlns="http://www.w3.org/2001/XMLSchema-instance">
        <testTag1>VAL 1</testTag1>
        <testTag2>VAL 2</testTag2>
        <testTag3>VAL 3</testTag3>
    </TestMain>';

    xmlstring := XMLTYPE.CREATEXML(lv_xml);

    SELECT EXTRACTVALUE(VALUE(fd), '.')
    INTO lv_val
    FROM TABLE(XMLSEQUENCE(xmlstring.EXTRACT('/TestMain', 'xmlns="http://www.w3.org/2001/XMLSchema-instance"'))) f,
        TABLE(XMLSEQUENCE(EXTRACT(VALUE(f), '/TestMain/testTag2', 'xmlns="http://www.w3.org/2001/XMLSchema-instance"'))) fd
    ;

    dbms_output.put_line('Val: '||lv_val);
EXCEPTION
    WHEN OTHERS THEN
       dbms_output.put_line('Error: '||SQLCODE||' '||SQLERRM);
END;
/

And for the code readability sake you can write the particular inside select as

SELECT EXTRACTVALUE(xmlstring, '/TestMain/testTag2', 'xmlns="http://www.w3.org/2001/XMLSchema-instance"')
INTO lv_val
FROM dual;

or even more readable (though functionally not 100% identical)

lv_val := xmlstring.extract('/TestMain/testTag2/text()', 'xmlns="http://www.w3.org/2001/XMLSchema-instance"').getStringVal();

Collectives™ on Stack Overflow

Extract XML tag with namespace

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related