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?

xml sql-server-2005 t-sql
Edit
Report