Querying XML data with SQL

Question

I have a log table with XML column. I studied several examples of querying XML but they don't match my needs.

Id   TimeStamp            Message    Properties
------------------------------------------------------------------------------
1    2015-10-27 08:45:38  Text       <properties><property key="time">23.10.2015 15:00:40</property><property key="interceptors">False</property></properties>
2    2015-10-27 09:21:09  Text       <properties><property key="Product">Azure</property><property key="Source">Azure Infrastructure</property></properties>
3    2015-10-27 16:51:32  Text       <properties><property key="Product">Azure</property><property key="Source">Azure Infrastructure</property><property key="Uptime">00:00:27.3874982</property></properties>
4    2015-10-28 11:03:25  Text       <properties><property key="Uptime">00:19:21.4729461</property></properties>

I want to have a result set of all node values where the property node has a key of 'uptime'. The result set could look like:

Id   Uptime
-----------------
3    00:00:27.3874982
4    00:19:21.4729461

Dan Guzman · Accepted Answer · 2015-10-28 11:10:41Z

1

Below is one method which uses xquery expression with an attribute filter along with a CTE to avoid repeating the expression:

WITH Uptimes AS (
    SELECT
          Id
        , Properties.value('(/properties/property[@key="Uptime"])[1]', 'time(7)') AS Uptime
    FROM dbo.test
    )
SELECT Id, Uptime
FROM Uptimes
WHERE Uptime IS NOT NULL;

answered Oct 28, 2015 at 11:10

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HaveNoDisplayName · Accepted Answer · 2015-10-28 11:31:13Z

1

You can use below sql

select id, act.value('(/properties/property[@key="Uptime"])[1]', 'varchar(25)')
from #t
where act.exist('/properties/property[@key="Uptime"]') = 1

edited Oct 28, 2015 at 11:31

answered Oct 28, 2015 at 11:15

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Collectives™ on Stack Overflow

Querying XML data with SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related