Secure text input in XML

Question

I have over a million rows in a SQLServer2005 database with a text column containing XML rows. I want to pass text to an XML data type in order to extract pieces of data.

The problem is that there are some entries that will cause casting errors (i.e. invalid XML). How can I ignore these errors so that all valid XML are correctly selected and the invalid XML is stored as null?

Answer 1

Once, in a similar situation, I added an XML column to the same table as the Text column. Then I used the RBAR process to try to copy the “XML” from the text column to the new XML column (not the fastest, but it will make single entries and it will be one-time, right?). It is assumed that your table has a PK data type int.

declare @minid int, @maxid int;

select @minid=min(ID), @maxid=max(ID) from XMLTable;

while @minid <= @maxid
begin

begin try

update t
set XMLColumn = cast(TextColumn as XML)
from XMLTable t
where ID = @minid;

set @minid = @minid+1

end try
begin catch

print('XML transform failed on record ID:'+cast(@minid as varchar))

--advance to the next record
set @minid = @minid+1
end catch


end

Answer 2

I know this is the functionality of SQL Server 2012+, but since this question is the main result of Google, this is:

SELECT 
COALESCE(TRY_CONVERT(xml, '</bad xml>'), 'InvalidXML')

The documentation can be found here: TRY_CONVERT (Transact-SQL)

Answer 3

.net, xml XMLdocument, BOOL, xml , sql