Ora error ORA-31011

Hi experts,

I am reading xml data from a table with clob xml column type.The procedure some times through xml parsing errors because of bad characters within xml tag values.
Is there a generic approach to translate or replace all bad characters that might be causing the xml parsing error?Since we have only read rights, we do not have control over the xml getting stored.basically i need to handle the xml read functionality in a generic way, since for large records, may be only one or two xml's might through the parsing errors.the read operation is nothing but a select of few extracted xml element values.
DevildibAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I think everything has been covered in your very closely related previous questions on this subject.

An just like in your previous questions:
Please provide some sample data that is 'bad' that generates the error.  'bad' can mean many different things.

I realize that you cannot provide actual data but please dummy something up that represents what you need.  Also provide the expected results from the sample data.
sdstuberCommented:
>>> Is there a generic approach to translate or replace all bad characters that might be causing the xml parsing error

I showed you this in a previous question.  
Validate the clob against an xsd.

If you can't get that far because the clob won't convert to xml, then wrap it all in a function that will validate both the generic structure as well as the xsd conformance.

Think through what you are asking.

1 Some text shows up, the contents of which are unknown.
2 Read the text looking for anything that might not be correct, but correct isn't specified.
3 Fix the bad stuff which isn't specified so it becomes valid, where valid is not specified.
4 Parse the fixed data as if it were xml and extract unknown fields from it into a standard format

Hopefully that shows why you'll never get an answer that shows you how to do what you want.
It's simply not possible.
slightwv (䄆 Netminder) Commented:
>> then wrap it all in a function that will validate both the generic structure as well as the xsd conformance.

This is sort of what I started with a couple of questions ago with the addition of the XSD validation built in.

You mentioned in the previous question that the database is large and doing all the checking with a function at run time isn't feasible.

I know you mentioned that you only have read-only access but I'm pretty sure you cannot get where you need to be 'efficiently' and 'read-only'.  Some code will likely need to be written.  I say 'likely' because as sdstuber restated:  We really don't know your specific requirements because you never provide us the details.

Have you considered a trigger?  Then you could set a flag when a row is inserted or updated that can tell you the 'status' of the CLOB as far as XML goes.

Off the top of my head the flag would have three values:
'V': Valid
'P':Parse-able XML but fails XSD checks
'I':Invalid XML

More if you need them based on requirements you have yet to tell us about.

Then you offload the check at the DML layer and take advantage at query time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DevildibAuthor Commented:
Trigger solution is something that i can propose.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.