In SQL Server, what is the best way to determine if a given string is a valid XML or not?

Question

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).

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.

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. 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.

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?

Answer 1

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

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.

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

Result:

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