November 3, 2008 at 10:08 am
Everyone, we have an important legacy lob attribute that stores well formed and also malformed xml. From time to time we need data corrections to change nodes in the xml. The malformed xml have caused real problems. This is a legacy TEXT field, so a cast to XML thows a batch terminating excpetion.
the only solution I have found that works is a cursor with a try catch, and logging the bad ones to a table in the catch block. this is very slow, and unfortunately we have 100-200k wide xml files that number in the millions.
I have not had luck getting this code to run in any acceptable time. I understand this script is giong to be be slow, I would just love a set based way to deal with malformed xml. Pseduo code is below, anyone having a different approach would be much appreciated. My current attempt is a udf, but the xml parse error is still terminating the batch.
The app team has been punished for putting bad xml in the db in the first place.
DECLARE C CURSOR FAST_FORWARD FOR
SELECT xml, xmlkey
FROM PhyiscalTemp
OPEN C_POD_XML
FETCH NEXT FROM C_POD_XML INTO @xml,@xmlid
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SELECT CASE WHEN CAST(@strCurrItemConfig AS XML) IS NULL THEN 1 ELSE 1 END AS 'IsParsable' --error throws to catch
...xml processing
END TRY
BEGIN CATCH
--deal with malformed xml
END CATCH
END
CLOSE C
DEALLOCATE C
November 3, 2008 at 1:34 pm
Please don't cross post. It just wastes people's time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic595934-338-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply