CODEWITHSUNDEEP
sql-serverxml
Jamie Ide
Jamie Ide

Reputation: 49261

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/XMLSchema-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.

Upvotes: 7

Views: 14652

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

;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)

Upvotes: 14

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

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).

Upvotes: 0

Related Questions