Extracting child node value from XML using T-SQL

Question

I'm stuck on trying to get the 'availability' node's value out of an envelope returned via T-SQL from a Microsoft Lync database. The usual methods of .value('(/MyElement/Something)[1]') doesn't seem to work for me.

<state xsi:type="aggregateState" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2006/09/sip/state">
<availability>3500
</availability>
<delimiter xmlns="http://schemas.microsoft.com/2006/09/sip/commontypes" />
<timeZoneBias>-60
</timeZoneBias>
<timeZoneName>GMT Daylight Time
</timeZoneName>
<timeZoneAbbreviation>GMT Daylight Time
</timeZoneAbbreviation>
<device>computer
</device>
<end xmlns="http://schemas.microsoft.com/2006/09/sip/commontypes" />
</state>

This is the query I've been experimenting with:

SELECT TOP 1 
    CAST(SUBSTRING(i.Data, 0, 4000) as XML).value('(/state//availability)[1]', 'varchar(256)')
FROM dbo.PublishedCategoryInstanceView AS i
INNER JOIN dbo.CategoryDef AS d
    ON (d.CategoryId = i.CategoryId)
WHERE i.PublisherId = (SELECT ResourceId FROM dbo.Resource
                        WHERE UserAtHost = '[email protected]')
ORDER BY i.LastPubTime DESC

All I get back is 'NULL' unless I do CAST(SUBSTRING(i.Data, 0, 4000) as XML).value('(/)[1]', 'varchar(256)') which returns 3500-60GMT Daylight TimeGMT Daylight Timecomputer

I do know that when I strip out the three attributes on the state element I can perform normal XML queries against the data so I can get around this by manipulating the string with a few replace statements but I'd rather learn exactly what I'm doing wrong here, if anyone can help?

marc_s · Accepted Answer · 2013-09-11 09:44:44Z

3

You're just plain ignoring the XML namespace that exists on your XML root node:

<state xsi:type="aggregateState" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   
       xmlns="http://schemas.microsoft.com/2006/09/sip/state">
       ******************************************************

You need to include that in your T-SQL query!

Try something like this:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/2006/09/sip/state')
SELECT TOP 1 
    CAST(SUBSTRING(i.Data, 0, 4000) as XML).value('(/state//availability)[1]', 'varchar(256)')
FROM 
    dbo.PublishedCategoryInstanceView AS i

answered Sep 11, 2013 at 9:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Extracting child node value from XML using T-SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related