Is it possible to skip a bad record when selecting records from one Table?

[snehasismishra1]

Hi,

I am using below select query.

SELECT	EmpID,CAST(EmployeeInformation AS xml) as emp, FROM EmployeeTable

when there is some problem in EmployeeInforma tion, then XML parsing is throwing error and select statement is not retriving data after that record.

My question is, can i retrive all the record by neglecting bad record?

Please reply ASAP.


Thanks,
Snehasis

[Rabbit]

How do you know if it's a bad record?

[snehasismishra1]

Suppose in EmployeeTable table, 10 records are there. I Have used below query to retrive all the record.

SELECT    EmpID,CAST(EmployeeInformation AS xml) as emp, FROM EmployeeTable Order By EmpID

Let say, after retrving 4th record XML parsing error threw. That means there is some error in 5th EmpID record(i.e in EmployeeInforma tion which is not casting to XML)

So i want to neglect that 5th record and retrive other all 9 records.

Please let me know, if you want any other clarification.

Thanks,
Snehasis

[Rabbit]

What makes it not cast to XML?

[snehasismishra1]

Due to some reason,the EmployeeInforma tion column's data may contain the corrupted data like Tag mismatching, missing '>' or '<' symbol . when i am trying to use CAST(EmployeeIn formation AS xml)in my query, XML error is throwing . So i want to skip that record or to handle the corrupted XML.

Is there any possible way?

Thanks,
Snehasis

[Rabbit]

You can use the WHERE clause to filter out the ones that match the pattern of the ones that don't cast to XML. But to do that, you need to figure out what that exactly is.