XML Parsing in Oracle

I am trying to parse an XML text. It is stored in a table t_testxml, in column xml_data which is CLOB type.

The xml looks like:

<?xml version="1.0" encoding="UTF-8"?>
<defaultmpftest:defaultmpftest xmlns:defaultmpftest="http://test.com"
 test_id = "1231"
 test_name = "name_test">
</mpftestdata:additionalLinkUrl xmlns:mpftestdata="http://test2.com"/>
</defaultmpftest:defaultmpftest>

How can I extract the values for test_id and test_name ?

I tried:

Select extract(xmltype.createxml(t.xml_data),'//defaultmpftest:defaultmpftest/@test_id').getStringVal() from t_testxml t;

But is not working. I get the following error:

ORA-31011: XML Parsing failed
LPX-00601: Invalid token in defaultmpftest:defaultmpftest/@test_id

Can you please give me some advices on this matter ?

Thank you !

Solution

The XML shown in the question is invalid, and would cause an "LPX-00231: invalid character" error if you passed it in to XMLType. So that isn't the string you're actually using. I'm assuming is a typo when posting the question, and you are actually getting the "LPX-00601: Invalid token" error you claimed. So I'll base this on that assumption, and on a string without that typo.

extract is deprecated; but even so, to use it here (with corrected raw XML) you need to specify the namespace with the optional third argument:

select extract(xmltype.createxml(t.xml_data),
  '//defaultmpftest:defaultmpftest/@test_id',
  'xmlns:defaultmpftest="http://test.com"').getStringVal()
from t_testxml t;

EXTRACT(XMLTYPE.CREATEXML(T.XML_DATA),'//DEFAULTMPFTEST:DEFAULTMPFTEST/@TEST_ID','XMLNS:DEFAULTMPFTEST="HTTP://TEST.COM"').GETSTRINGVAL()
-----------------------------------------------------------------------------------------------------------------------------------------
1231

Rather than using the deprecated function, you could use XMLQuery:

select xmlquery(
  'declare namespace defaultmpftest="http://test.com"; (: :)
   //defaultmpftest:defaultmpftest/@test_id'
  passing xmltype.createxml(t.xml_data)
  returning content).getStringVal()
from t_testxml t;

XMLQUERY('DECLARENAMESPACEDEFAULTMPFTEST="HTTP://TEST.COM";(::)//DEFAULTMPFTEST:DEFAULTMPFTEST/@TEST_ID'PASSINGXMLTYPE.CREATEXML(T.XML_DATA)RETURNINGCONTENT).GETSTRINGVAL()
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1231

You would need two XMLQuery clauses to get both values. I'd usually use XMLTable instead, shown here with the (fixed) XML-as-string provided via a CTE:

with t_testxml(xml_data) as (select '<?xml version="1.0" encoding="UTF-8"?>
<defaultmpftest:defaultmpftest xmlns:defaultmpftest="http://test.com"
 test_id="1231"
 test_name="name_test">
<mpftestdata:additionalLinkUrl xmlns:mpftestdata="http://test2.com"/>
</defaultmpftest:defaultmpftest>' from dual
)
select x.test_id, x.test_name
from t_testxml t
cross join xmltable(
  xmlnamespaces('http://test.com' as "defaultmpftest"),
  '//defaultmpftest:defaultmpftest'
  passing xmltype(t.xml_data)
  columns test_id number path '@test_id',
    test_name varchar2(30) path '@test_name'
) x;

   TEST_ID TEST_NAME                     
---------- ------------------------------
      1231 name_test