XML parsing failed in SELECT query

Question

I am running the SELECT query from the Oracle SQL developer IDE on my RHEL box as below

SELECT count(*) 
From xyz 
WHERE xmltype(xyz.xmlColumn).existsNode('//name=""') = 1;

Above query works fine if I execute for single record. But when I execute it for the whole table it fails with the error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of 'C'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

Any pointers on above will help me.

One of your XML documents isn't XML. You'll have to find the relevant value and fix it. — Jon Heller
– Jon Heller, Commented Jul 2, 2013 at 4:30
Thanks jonearles for you input. But still I am not able to figure it out where is the issue. The table has multiple columns. The column which is their in Where clause is of type CLOB which has XML data always. So you want to say the XML column doesn't contain XML at some place and due to this query is failing. — Harshal Patil
– Harshal Patil, Commented Jul 2, 2013 at 4:46
1 more thing is that this query runs fine if if I remove count as below: code SELECT * From xyz WHERE xmltype(xyz.xmlColumn).existsNode('//name=""') = 1; code It encounters error only when I want to get count(*) — Harshal Patil
– Harshal Patil, Commented Jul 2, 2013 at 5:03
If your IDE only receives N rows at a time, then select * may appear to work better than select count(*), but only because it hasn't retrieved all the records yet. Try to find a single value that causes the error, you'll probably see something wrong with the XML. Finding that one value can be difficult, because it's difficult to know the exact order of SQL processing. — Jon Heller
– Jon Heller, Commented Jul 2, 2013 at 5:22
It depends on your XML. But in general, trying to parse XML with anything other than a full XML parser is asking for trouble. Depending on what is making the XML invalid, it might be easier to fix the problem before converting it to an XMLType. It all depends on what exactly was wrong with the XML, and if it is consistently wrong. But you really shouldn't need to do this. A huge benefit of XML is that it's well defined, and you should not have to mess around with it like this. Maybe you need to push back and tell someone "this is not XML, it's useless text that kinda looks like XML." — Jon Heller
– Jon Heller, Commented Jul 3, 2013 at 4:26

Imran Lone · Accepted Answer · 2015-11-09 09:42:47Z

1

include /text in your xpath. like select (COLUM_NAME).extract('//parentNode/chileNode/text()').getStringVal() as Colum_Name from TableName where UUID='Value'

answered Nov 9, 2015 at 9:42

Imran Lone

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Raghu Kasturi · Accepted Answer · 2016-02-04 18:58:58Z

0

Oracle is complaining that "xyz.xmlColumn" is not a valid xml column or type. It expects <element>....</element> format

answered Feb 4, 2016 at 18:58

Raghu Kasturi

155 bronze badges

Comments

shashi choudhary · Accepted Answer · 2018-12-12 03:31:32Z

0

Your problem is what is stored in your CLOB is not an XML document as it is missing a root node. Since you are scanning the whole document in your extract you could just wrap it in an element to ensure you have 1 and only 1 root, at which point your XMLTYPE cast would work.

SELECT XMLTYPE(''||clob_field||'').EXTRACT('//se/text()').getStringVal() FROM tablename

answered Dec 12, 2018 at 3:31

shashi choudhary

1

Collectives™ on Stack Overflow

XML parsing failed in SELECT query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related