开发者

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜