在 SQL Server 中，确定给定字符串是否为有效 XML 的最佳方法是什么? [英] In SQL Server, what is the best way to determine if a given string is a valid XML or not?

问题描述

第三方组件正在用一些值填充表中的 nvarchar 列.大多数时候它是一个人类可读的字符串，但偶尔它是 XML(以防 3rd 方组合中的一些内部异常).

A third party component is filling up an nvarchar column in a table with some values. Most of the time it is a human-readable string, but occassionally it is XML (in case of some inner exceptions in the 3rd party comp).

作为临时解决方案(直到他们修复它并始终使用字符串)，我想解析 XML 数据并提取实际消息.

As a temporary solution (until they fix it and use string always), I would like to parse the XML data and extract the actual message.

环境: SQL Server 2005；字符串的大小总是小于 1K；此表中可能有几千行.

Environment: SQL Server 2005; strings are always less than 1K in size; there could be a few thousand rows in this table.

我遇到了几个解决方案，但我不确定它们是否足够好:

I came across a couple of solutions, but I'm not sure if they are good enough:

1. 调用 sp_xml_preparedocument 存储过程并将其包裹在 TRY/CATCH 块中.检查返回值/句柄.
2. 编写托管代码(在 C# 中)，再次处理异常并查看它是否为有效字符串.

1. Invoke sp_xml_preparedocument stored proc and wrap it around TRY/CATCH block. Check for the return value/handle.
2. Write managed code (in C#), again exception handling and see if it is a valid string.

这些方法似乎都没有效率.我正在寻找类似于 ISNUMERIC() 的东西:一个 ISXML() 函数.有没有其他更好的方法来检查字符串?

None of these methods seem efficient. I was looking for somethig similar to ISNUMERIC(): an ISXML() function. Is there any other better way of checking the string?

推荐答案

我想解析 XML 数据并提取实际消息.

I would like to parse the XML data and extract the actual message.

也许没有必要检查有效的 XML.您可以在 case 语句中使用 charindex 检查适当的 xml 标记的存在，并使用 substring 提取错误消息.

Perhaps it is not necessary to check for valid XML. You could check for the presence of the appropriate xml tag with charindex in a case statement and extract the error message using substring.

这是一个带有简化 XML 字符串的示例，但我认为您明白了.

Here is a sample with a simplified XML string but I think you get the idea.

declare @T table(ID int, Col1 nvarchar(1000))

insert into @T values
(1, 'No xml value 1'),
(2, 'No xml value 2'),
(3, '<root><item>Text value in xml</item></root>')

select
  case when charindex('<item>', Col1) = 0
  then Col1
  else
    substring(Col1, charindex('<item>', Col1)+6, charindex('</item>', Col1)-charindex('<item>', Col1)-6)
  end  
from @T

结果:

No xml value 1
No xml value 2
Text value in xml

这篇关于在 SQL Server 中，确定给定字符串是否为有效 XML 的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！