sql-server - 查询 XML 列

Question

我有一个 SQL Server 2012 表，其中一列是 XML 数据类型。

以下是其中一个值:

<items>
  <Counter CounterName="processed" CounterValue="70" />
  <Counter CounterName="deferred" CounterValue="1" />
  <Counter CounterName="delivered" CounterValue="70" />
  <Counter CounterName="sent" CounterValue="70" />
  <Counter CounterName="click" CounterValue="2" />
  <Counter CounterName="open" CounterValue="22" />
</items>

问题:如何编写一个查询，将以上所有内容显示为列，例如...

SELECT 
   ??? as processed, 
   ??? as deferred, 
   ??? as delivered, --- etc. 
FROM mytable

Answer 1

这个怎么样:

DECLARE @input TABLE (ID INT NOT NULL, XmlCol XML)

INSERT INTO @input VALUES(1, '<items>
  <Counter CounterName="processed" CounterValue="70" />
  <Counter CounterName="deferred" CounterValue="1" />
  <Counter CounterName="delivered" CounterValue="70" />
  <Counter CounterName="sent" CounterValue="70" />
  <Counter CounterName="click" CounterValue="2" />
  <Counter CounterName="open" CounterValue="22" />
</items>'), (2, '<items>
  <Counter CounterName="processed" CounterValue="170" />
  <Counter CounterName="deferred" CounterValue="11" />
  <Counter CounterName="delivered" CounterValue="170" />
  <Counter CounterName="sent" CounterValue="170" />
  <Counter CounterName="click" CounterValue="12" />
  <Counter CounterName="open" CounterValue="212" />
</items>')

SELECT
    ID,
    Processed = xc.value('(Counter[@CounterName="processed"]/@CounterValue)[1]', 'int'),
    Deferred = xc.value('(Counter[@CounterName="deferred"]/@CounterValue)[1]', 'int'),
    Delivered = xc.value('(Counter[@CounterName="delivered"]/@CounterValue)[1]', 'int'),
    [Sent] = xc.value('(Counter[@CounterName="sent"]/@CounterValue)[1]', 'int'),
    Click = xc.value('(Counter[@CounterName="click"]/@CounterValue)[1]', 'int'),
    [Open] = xc.value('(Counter[@CounterName="open"]/@CounterValue)[1]', 'int')
FROM    
    @input
CROSS APPLY
    XmlCol.nodes('/items') AS XT(XC)

给我一​​个输出: