t-sql: convert varchar to xml
sql-server, sql-server-2008, t-sql, xml-parsing
Solution
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.
Problem
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 ```