在SQL Server中，确定给定字符串是否为有效的XML的最佳方法是什么？

Question

一个第三方组件正在向一个表中的nvarchar列填充一些值。大多数情况下，它是人类可读的字符串，但是偶尔会出现XML（在第三方组件内部异常的情况下）。

作为一个临时解决方案（直到他们修复并始终使用字符串），我想解析XML数据并提取实际消息。

环境：SQL Server 2005；字符串大小始终小于1K；这个表可能有几千行。

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

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

这些方法都不太高效。我正在寻找类似于ISNUMERIC()的东西：一个ISXML()函数。还有其他更好的检查字符串的方法吗？

- Venkat

---

为什么这两个解决方案不够？缺少了什么？ - rene

如果字符串不以 < 开头，那么你可以确定它肯定不是 XML。 - Martin Smith

@rene：我是指“高效” :) 如果行数大幅增加，上述两种解决方案听起来并不可扩展。 - Venkat

请参见下面的TRY_CONVERT答案 - Guy Schalnat

Answer 1

我希望解析XML数据并提取实际消息。

也许不需要检查有效的XML。您可以使用charindex在一个case语句中检查适当的xml标签的存在，并使用substring提取错误消息。

这里有一个简化的XML字符串示例，但我认为您已经明白了。

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

- Mikael Eriksson

---

谢谢！这可能不是所有类似情况的解决方案，但对于我的情况来说看起来还不错，作为一个简单的临时修复！此外，我将尝试将其用作简单的内联SQL，而不是将其包装在函数中。我会试一试 :) - Venkat

Answer 2

根据被接受的答案，我创建了这个检查有效的XML并可选地转换输入字符串为XML（或从XML中提取所需的元素/属性），因为我发现TRY_CONVERT在只传入纯文本时能够成功工作，这是我没有预料到的，因此需要进行另一个检查以防止最终强制转换为XML时源列仅包含一些文本（示例行1） ：

declare @T table(ID int, Col1 nvarchar(1000))
insert into @T values
(1, 'random text value 1'),
(2, '<broken> or invalid xml value 2'),
(3, '<root><item>valid xml</item></root>')
select id, Col1,
 Converted_XML = CASE 
        when [Col1] IS NULL THEN NULL                   /* NULL stays NULL */ 
        when TRY_CONVERT(xml, [Col1]) is null THEN NULL /* Xml Document Error */
        when CHARINDEX('<', [Col1]) < 1 AND CHARINDEX('>', [Col1]) < 1 THEN NULL        /* no xml */
        else CONVERT(xml, [Col1])                       /* Parsing succesful. => in this case you can convert string to XML and/or extract the values */
    END,
    Result_Comment = CASE 
        when [Col1] IS NULL THEN 'NULL always stays NULL'
        when TRY_CONVERT(xml, [Col1]) is null THEN 'Xml Document Error'
        when CHARINDEX('<', [Col1]) < 1 AND CHARINDEX('>', [Col1]) < 1 THEN 'no xml'
        else [Col1]
    END
FROM @T ;

- Magier

---

好的解决方案。只是需要注意的是，有时候SQL会将带有无效XML的字符串解释为混合内容。所以如果item的父级是<valid>，但是起始括号'<'与整个结束标记一起省略了，它会假定是混合内容：valild><item>valid xml</item> = valid&gt;<item>xml</item>。这是需要注意的事项。 - Charles Byrne

Answer 3

你可以创建一个XML模式并用它来验证XML字符串。

在这里查看更多信息：http://msdn.microsoft.com/en-us/library/ms176009.aspx 以下是一个示例：

CREATE XML SCHEMA COLLECTION UserSchemaCollection AS 
N'<?xml version="1.0" encoding="UTF-16"?>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name = "User" >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name = "UserID" />
                <xsd:element name = "UserName" />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
  </xsd:schema>';


DECLARE @x XML(UserSchemaCollection)
SELECT @x = '<User><UserID>1234</UserID><UserName>Sebastian</UserName></User>'

示例：

DECLARE @y XML(UserSchemaCollection)
SELECT @y = '<User><UserName>Sebastian</UserName></User>'

错误信息 6965，级别 16，状态 1，行 2 XML 验证：无效内容。期望元素为 UserID，而指定的是元素 'UserName'。位置：/:User[1]/:UserName[1]

DECLARE @z XML(UserSchemaCollection)
SELECT @z = 'Some text'

错误信息 6909，级别 16，状态 1，行 2 XML 验证：此位置不允许文本节点。该类型的定义是仅包含元素内容或简单内容。位置：/

- jim31415

---

他只是在检查有效的XML，而不是针对一个方案。否则你的答案和我的一样。 - SQLMason

Answer 4

我不知道最好的方法，但是这里有一种方法：

DECLARE @table TABLE (myXML XML)

INSERT INTO @table
SELECT  
'
    <Employee>
        <FirstName>Henry</FirstName>
        <LastName>Ford</LastName>
    </Employee>
'

SELECT myXML 
FROM @table 
FOR XML RAW

如果XML无效，它将会抛出一个错误：

DECLARE @table TABLE (myXML XML)

INSERT INTO @table
SELECT  
'
    <Employee
        <FirstName>Henry</FirstName>
        <LastName>Ford</LastName>
    </Employee>
'

SELECT myXML 
FROM @table 
FOR XML RAW

---

需要澄清的是，您所需做的只是将其投射：

BEGIN TRY
    DECLARE @myXML XML
    SET @myXML = CAST
    ('
        <Employee>
            <FirstName>Henry</FirstName>
            <LastName>Ford</LastName>
        </Employee>
    ' AS XML)
    SELECT 'VALID XML'
END TRY
BEGIN CATCH
    SELECT 'INVALID XML'
END CATCH;

vs

BEGIN TRY
    DECLARE @myXML XML
    SET @myXML = CAST
    ('
        <Employee
            <FirstName>Henry</FirstName>
            <LastName>Ford</LastName>
        </Employee>
    ' AS XML)
    SELECT 'VALID XML'
END TRY
BEGIN CATCH
    SELECT 'INVALID XML'
END CATCH;

- SQLMason

---

这如何适用于 OP 的情况，他们有一堆字符串，可能是 XML，也可能不是，他们只想将有效的字符串转换为 XML？select cast('foo' as xml) 不是 xml，但可以无错误地转换。select cast('foo < bar' as xml) 会导致错误。 - Martin Smith

我对它进行了修改，使其更像一个函数。你可以让你的isXML函数接受一个varchar并返回它是否为有效的XML。然后，你可以对有效的XML做任何你想做的事情。 - SQLMason

函数中不能使用 try..catch。 - Martin Smith

返回一个值的过程，抱歉。 - SQLMason

似乎没有好的方法可以避免使用RBAR。 - SQLMason

使用函数/存储过程和错误处理似乎不太具有可扩展性！这个表会与大约十几个表进行连接。我更喜欢一些简单的内联XML。我将尝试Mikael Eriksson的解决方案。 - Venkat