how to ignore xml parsing error for individual records when fetching multiple records from clob xml in oracle

Hi experts,
I have a table say "mytable" with some varchar columns eg col1, col2 and a clob xml column "studentdata".
I am able to select the normal columns and extract required xml columns successfully using xmltable function on most occasions .Now, since I do not have control over the xml clob data that resides in the table, it is happening that, for multiple records to be returned, due to some rows where the xml clob is not well formed or having some format issue, I keep getting xml parsing error.I need to have some logic where I can ignore those rows where the xml clob is throwing xml parsing error and return all other data.for eg there are 200 row items to be returned.2 clob xml values within those rows are incorrect or erroneous..In that case, I should be able to return 198 rows.Also suggest if there is any way I can convert the individual xml parsing into a common function.Below is my sample query section from the stored proc :


SELECT * FROM
            (
                (
                  SELECT a.col1, a.col2, x.col3,x.col4,x.col5,x.col6,x.col7
              FROM table a,
                   XMLTABLE(
                       xmlnamespaces(
                           'http://www.w3.org/2003/05/soap-envelope' AS "tns",            
                           'urn:com:abc:def' AS "ttt",
                           'urn:com:pqr:stu' as "stu"
                       ),
                       '/tns:Envelope/tns:Body/ttt:Message/ttt:Body'
                       PASSING xmltype(trim(studentdata))
                       columns
                       col3 varchar2(100) path 'ttt:students/stu:student/stu:stulChrgInf/stu:BeneChrgs/stu:TrfrCcyAmt',
                       col4 varchar2(100) path 'ttt:students/stu:student/stu:stulChrgInf/stu:BeneChrgs/stu:FeeCcyAmt',
                       col5 varchar2(10) path 'ttt:students/stu:student/stu:stulChrgInf/stu:BeneChrgs/stu:FeeCcyAmt/@Estimated',
                       col6 varchar2(100) path  'ttt:students/stu:student/stu:stulHdr/stu:ValDt',
                       col7 VARCHAR2 (200)  PATH  'ttt:students/stu:student/stu:OrgRmtInfo/stu:AddtlRmtInf/stu:Ustrd'
                   ) x  WHERE a.col2= 'somevalue1'
                            AND a.col1 = 'somevalue2'
                            AND a.creationdatetime >= start_date AND a.creationdatetime < end_date
                and rownum < 2001
                            ORDER BY a.creationdatetime DESC
               -- ) b
               
            );
DevildibAsked:
Who is Participating?
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.

sdstuberCommented:
you would need to use the pl/sql interface or build a much more complicated sql statement that could check before parsing sub fields.

Do you have sample data you can post with expected results?
0
DevildibAuthor Commented:
Unfortunately i do not sample data handy.
0
DevildibAuthor Commented:
I would really appreciate any sample approach you could provide.I could try that on the data whose access is restricted to office use only.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
You can create a function but it probably isn't very efficient.

something like:
Drop table tab1 purge;
Create table tab1(col1 clob);
Insert into tab1 values('a');
Insert into tab1 values('<a/>');
Commit;

Create or replace function isxml(p_clob in clob) return number 
Is
                Junk xmltype;
Begin
                Junk := xmltype(p_clob);
                Return 1;

                Exception when others then
                                Return 0;
End;
/

Show errors


Select * from tab1
Where isxml(col1) = 1
/

Open in new window

0
DevildibAuthor Commented:
This is good,but as you already said might take long time in case of 1000's of recs.Is there any way where in this can be handled in one go using try catch or exception handling?
0
slightwv (䄆 Netminder) Commented:
sdstuber claims there might be some "much more complicated" SQL that might be able to do it.

Maybe he can come up with something.

As he asked:  Some sample data would help.  Just make some up that is close to what you have.  It doesn't have to be 'live' data.
0
sdstuberCommented:
if you can't expose your real data, mock up a structure and some sample data that demonstrates what you're looking for.

How about

3 rows of xml, each xml having enough nodes to demonstrate what the problem, but no more.  I would expect this to be just a few populated tags. Probably 10 at most, probably less.
0
slightwv (䄆 Netminder) Commented:
Can we get some clarification?

there are 200 row items to be returned.2 clob xml values within those rows are incorrect or erroneous..In that case, I should be able to return 198 rows

Does this mean the XML is 'valid' but the data in the node is 'bad'?
or the XML is invalid and you want to 'ignore' the 'bad' nodes and process what is left?

This is where sample data and expected results help.  We can see the issue without having to figure it out from the description.
0
DevildibAuthor Commented:
Just clarifying once again.table structure is such that is has few varchar columns and ine clob xml column which might have say 100 xml elements within.Now consider the table having 200 rows.when selecting data, what we actually fetch is all the varchar columns and some xml elements extracted out of each clob.Might be in few rows when this extraction of elements happen, there is xml parsing error.So i wanted to get to a generic solution as in select only when correct xml else ignore the bad xml.I will try to get the data required by tomorrow.The isxml solution that you provided would have worked on a relatively smaller set of recs and simple clob data.In this case i have already spent long time tuning the select query i shared.Main issue is i have no control over the xml.And for each issue i cant go up, analyse the raw xml and put in a fix in the query.I wanted a generic fix as in ignoring xml prser errors and returning good data back. I Am here to solely get your advice and open to all your opinions.
0
DevildibAuthor Commented:
And as you asked, xml might be valid but may be some reqd tags are missing or have some extra spaces or unwanted characters and so on that might be causing the xml prser error.
0
slightwv (䄆 Netminder) Commented:
I'm afraid we'll need sample data and expected results.

I'll wait until you can dummy up some test data.

>>The isxml solution that you provided would have worked on a relatively smaller set of recs and simple clob data

It wouldn't work for valid XML with 'bad' data.
0
sdstuberCommented:
Do you have an xsd?  If so, you could validate the xml against the xsd before parsing.
0

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:
Xsd validation fixed everything for me
0
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
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.