Geek Answers Handbook

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 to get a tabular result from the XML data in this column for one row? The table has a primary authentication key named HistoryId.

+4
source share
2 answers
 ;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)
+7
source

My recommendation will be doubled.

  • If this is what you will do with the column, change the column as an XML column.
  • If you need to do this once, take a look at accepting the value and converting it to XML, then you can work on it as usual. (Here is a link on how to convert).
0
source

More articles:


All Articles