将文本安全转换为 XML [英] Safe-casting text to XML

问题描述

我在 SQLServer2005 数据库中有超过一百万行，其中有一个包含 XML 字符串的文本列.我想将文本转换为 XML 数据类型以提取部分数据.

I have over a million rows in an SQLServer2005 database, with a text column that contains XML strings. I want to cast the text to the XML datatype in order to extract parts of the data.

问题是有些记录在转换时会抛出错误(即无效的 XML).如何忽略这些错误，以便正确转换所有有效的 XML，并将无效的 XML 存储为 null?

The problem is there are some records that will throw errors when casting (ie. invalid XML). How can I ignore these errors so that all the valid XML is casted correctly and invalid XML is stored as null?

推荐答案

有一次在类似的情况下，我将 XML 列添加到与 Text 列相同的表中.然后我使用 RBAR 过程尝试将XML"从文本列复制到新的 XML 列(不是最快的但提交单次写入，这将是一次性的，对吧?).这是假设您的表具有 int 数据类型的 PK.

Once in a similar situation I added the XML column to the same table as the Text column. Then I used a RBAR process to attempt to copy the "XML" from the text column to the new XML column (not the fastest but commits single writes and this will be a one time thing, right?). This is assuming your table has a PK of an int data type.

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

这篇关于将文本安全转换为 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！