I had a little bit of an issue at work when I had to create a query that returned values that were stored in an XML column. Normally you can use the XML value method to get these. My problem was that the column I wanted had XML stored in it but wasn’t actually defined as an XML type.
This requires a conversion into XML. For anyone looking at the same problem, this is what I did
SELECT tbl.ColumnName ref.value('FirstName', 'nvarchar(100)') as [FirstName], ref.value('LastName', 'nvarchar(100)') as [LastName], FROM myTable tbl CROSS APPLY (SELECT CONVERT(xml, tbl.MyNonXmlCol)) as T(X) CROSS APPLY T.X.nodes('//AuthorDetails') R(ref)