t-sql: convert varchar to xml
36,284
Solution 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.
Solution 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
Author by
David
Updated on July 30, 2022Comments
-
David almost 2 years
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