t-sql: convert varchar to xml

Question

I have some valid and invalid xml values stored in a varchar column.

I'd like to cast the valid xml values to the actual xml data type and invalid to nulls.

What's a good way to do this ?

Something like:

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

Answer 1

In response solely to this:

Yes, but the beef of my question is how do I check is XML is valid in the first place?

Is your XML invalid in the sense that you'll get a break like the second line below this:

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

One solution I see is a row-by-row approach, where you try and CAST a row as XML, and if it casts as XML successfully insert the valid row into a table with valid XML values, if it won't cast correctly, the value isn't inserted. See this thread for examples.

Answer 2

Try to use 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

Output -

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

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