Manage XML data in SQL column when column is not of type XML

Below is a way to manage XML in SQL when the column is not of type XML

First, cast the data into a temp table as XML
DECLARE @results TABLE (Id INT, Detail XML)
--Filter results
INSERT INTO @results
select t.Id, d.extrainfo
from tableOne t
inner join into i on i.Id=t.Id
where i.institutionid=6 and t.state < 270

Combine relevent data for easier manipulation
DECLARE @x XML;
SELECT @x = ( SELECT
  [Id],
  [Detail].query('
  for $a in /*:RootNode
  return ')
FROM @results AS results
FOR XML AUTO);


Simple select statement
SELECT Id = T.Item.value('../@Id', 'INT'),
  Date = T.Item.value('@date', 'DATETIME')
FROM @x.nodes('results/detail') as T(Item)