SQL Server:使用 XML 进行内联条件转换? [英] SQL Server: inline conditional convert with XML?

问题描述

我需要将纯文本列中的数据转换为 xml 列.但是，并非所有内容都是有效的 XML，因此我需要忽略任何带有无效 XML 的行.有没有办法可以使用某种内联条件转换或 WHERE 子句中的某些内容过滤掉无效数据?

I need to convert data in plain text columns into xml columns. However, not all of the content is valid XML, so I need to ignore any rows with invalid XML. Is there a way I can filter out the invalid data either using some sort of inline conditional convert or something in the WHERE clause?

推荐答案

听起来您需要一个版本的 ISNUMERIC 函数用于 XML 数据.

It sounds like you need a version of the ISNUMERIC function for XML data.

遗憾的是不存在这样的内置函数 - 因此您必须设计自己的替代方案，为此有几个选项:

Regrettably no such built-in function exists - so you'll have to devise your own alternative, for which there are a couple of options:

如果这是一个一次性或小规模的过程，其性能并不重要，您可以使用 TRY...CATCH 处理无效转换的块(未经测试):

If this is a one-off or small scale process for which performance isn't critical, you could process the input tables one row at a time inside a cursor, using a TRY...CATCH block to handle invalid casts (untested):

DECLARE xmlCur CURSOR FOR
SELECT textcol 
FROM inputTable

OPEN xmlCur
DECLARE @string nvarchar(MAX)
DECLARE @xml xml

FETCH NEXT FROM xmlCur into @string

WHILE @@fetch_status = 0
BEGIN
    BEGIN TRY
        SET @xml = CAST(@string AS XML)
        -- Do something with XML
    END TRY
    BEGIN CATCH
        -- log failure/mark source row as invalid
    END CATCH

    FETCH NEXT FROM xmlCur into @string
END

CLOSE xmlCur
DEALLOCATE xmlCur 

或者，如果您对 .Net 编程感到满意(并且它已在您的服务器上启用)，您可以使用 CLR 创建您自己的 IsXML 函数..Net 代码不需要比 这个主题.

Alternatively, if you're comfortable with .Net programming (and it's enabled on you server) you could use the CLR to create your own IsXML function. The .Net code would need to be not much more complex than the third post on this thread.

CLR 解决方案的性能可能不会比游标好多少 - 您需要进行测试才能确定这一点.

The performance of the CLR solution may not be that much better than the cursor - you'd need to test to establish this.

(一个明显的尝试，但不起作用，是一个标量值 T-SQL 函数，它试图将字段转换为 TRY...CATCH 块内的 XML.但是, TRY...CATCH 不允许在函数内部使用.)

(An obvious thing to try, which doesn't work, is a scalar-valued T-SQL function which attempts to cast the field to XML inside a TRY...CATCH block. However, TRY...CATCH is not permitted inside a function.)

这篇关于SQL Server:使用 XML 进行内联条件转换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！