XML Column Query
I have a SQL Server 2012 table where one of the columns is XML datatype.
Below is one of the values:
<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>
Question: How can I write a query that displays all of the above as columns such as ...
SELECT
??? as processed,
??? as deferred,
??? as delivered, --- etc.
FROM mytable
+3
source to share
1 answer
How about this:
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)
Gives me the output:
+3
source to share