XML Parsing in Oracle

Question

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 !

What you've posted as your raw XML is invalid, as pointed out in an answer, but if run as shown you'd get a different error - LPX-00231, before it has a chance to hit LPX-00601. Removing the stray / at the start of the inner node name would make the XML valid and would then give the error you showed. It's helpful to show what you are actually using. See how to create an MCVE. — Alex Poole
– Alex Poole, Commented Apr 21, 2017 at 9:45

Alex Poole · Accepted Answer · 2017-04-21 10:19:16Z

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

2 Comments

codi05ro Over a year ago

Thank you for the answer, but I can't change the XML. And I need information from the upper node. defaultmpftest:defaultmpftest

Wernfried Domscheit Over a year ago

Provided data is not a XML. Thus you cannot use any XML function. You have to use REGEXP functions in this case.

peter.hrasko.sk · Accepted Answer · 2017-04-21 09:39:33Z

As Wernfried Domscheit correctly stated: Your XML is not well-formed. And for non-well-formed XMLs there's no way to extract information thereof in regular ways. Simply because regular ways are for XML; and your "XML" is not really an XML.

Let's try non-regular ways then...

with t_testxml as (
    select q'{<?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>}' as xml_data
    from dual
)
select xmlcast(xmlparse(content regexp_substr(T.xml_data, '<defaultmpftest:defaultmpftest[^>]+')||' />').extract('*/@test_id') as integer) as test_id
from t_testxml T
;

Collectives™ on Stack Overflow

XML Parsing in Oracle

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related