Select XML from varchar(max) column

I have some XML data stored in a varchar(max) column on SQL Server 2005. The data is in the form (FQTN = fully qualified type name):

<?xml version="1.0" encoding="utf-16"?>
<History xmlns:xsi="http://www.w3.org/2001/XMLSchem开发者_开发知识库a-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <EntityViews>
    <EntityProxy Type="FQTN" Key="386876" />
    <EntityProxy Type="FQTN" Key="387981" />
    <!-- etc. -->
  </EntityViews>
</History>

How can I select Type, Key so that I get a tabular result from the XML data in this column for a single row? The table has an identity primary key named HistoryId.

---

;with cteCastToXML as (
    select CAST(YourColumn as xml) as x
        from YourTable
)
select h.ep.value('@Type','varchar(10)') as [Type],
       h.ep.value('@Key', 'varchar(10)') as [Key]
    from cteCastToXML
        cross apply x.nodes('/History/EntityViews/EntityProxy') as h(ep)

---

My recommendation would be two fold.

1. If this is what you will be doing with the column, change the column to be an XML column.
2. If you need to do this one time, look at taking the value and converting it to XML, then you can operate on it like you would normally. (Here is a link on how to convert).