T-SQL：将varchar转换为XML

Question

我有一些存储在varchar列中的有效和无效的xml值。

我想将有效的XML值强制转换为实际的XML数据类型，而将无效的转换为null。

有什么好的方法可以做到这一点吗？

类似这样：

SELECT 
    CASE WHEN dbo.isValidXML(xml_data) THEN CAST(xml_data as XML)
    ELSE null 
END

- David

---

SQL Server 的哪个版本？ - lc.

微软 SQL Server 2008（SP1） - David

你能否在数据加载期间不运行进程以拒绝无效的XML，否则每次使用此选择语句时都会通过函数运行此查询，这对性能来说并不理想。 - Christian Phillips

是的，我知道。这就是我在加载之前尝试识别无效的XML值的方式。 - David

请在此处查看答案：https://dev59.com/cG435IYBdhLWcg3wfgMf#43740683 - Guy Schalnat

Answer 1

仅回答这个问题：

是的，但我的问题实质上是如何首先检查XML是否有效？

您的XML是否无效，就像下面第二行一样会中断：

SELECT CAST('<xml>Yep this is xml</xml>' AS XML)
SELECT CAST('<xml>Nope, not xml</x' AS XML)

我看到的一个解决方案是逐行处理，尝试使用CAST将一行作为XML，如果可以成功转换成XML，就将有效的行插入具有有效XML值的表中；如果无法正确转换，则不插入该值。有关示例，请参见此线程。

- Question3CPO

---

感谢您提供另一个线程的链接，建议手动解析相关令牌。这就是我最终所做的。 - Alan

Answer 2

尝试使用sp_xml_preparedocument -

SET NOCOUNT ON;

DECLARE @XML NVARCHAR(MAX)
SELECT @XML = '<t>test</'

DECLARE @hDoc INT

BEGIN TRY

     EXEC sys.sp_xml_preparedocument @hDoc OUTPUT, @XML 
     SELECT '"' + @XML + '" is valid'
     EXEC sys.sp_xml_removedocument @hDoc

END TRY
BEGIN CATCH
     SELECT '"' + @XML + '" is invalid'     
END CATCH

SELECT @XML = '<t>test</t>'

BEGIN TRY

     EXEC sys.sp_xml_preparedocument @hDoc OUTPUT, @XML 
     SELECT '"' + @XML + '" is valid'
     EXEC sys.sp_xml_removedocument @hDoc

END TRY
BEGIN CATCH
     SELECT '"' + @XML + '" is invalid'     
END CATCH

输出 -

-------------------------
"<t>test</" is invalid

-------------------------
"<t>test</t>" is valid

- Devart

---

非常棒！非常感谢您提供的解决方案。 - AeyJey

Answer 3

如果您需要保留无论是有效还是无效的XML，您可以在表上设置一个类似于"IsValid (bit)"的标志。加载过程可以对此数据进行一次性检查，以确定XML是否有效，并根据结果设置标志。这样，您可以通过以下查询获取有效的XML：

SELECT 
    CASE WHEN IsValid = 1 THEN CAST(xml_data as XML)
    ELSE null 
END

如果您不需要保留无效的XML，则在加载期间拒绝它，将字段设置为NULL，因此查询将如下所示：

SELECT 
    CASE WHEN xml_data IS NOT NULL THEN CAST(xml_data as XML)
    ELSE null 
END

无论哪种方式，都比每个查询都通过SQL函数获得更好的性能。

- Christian Phillips

---

是的，但我问题的重点是如何首先检查XML是否有效？ - David

我有一些有效和无效的 XML 值存储在一个 varchar 列中。- 这个存储步骤是否在你的控制之下？还是你正在对一个预先填充的表进行查询？ - Christian Phillips

一个预填表格。基本上，我需要一种使用T-SQL检查XML字符串有效性的方法。 - David