How to query the attribute value of an XML element in SQL Server with xpath

Question

I want to read an attribute out of XML stored to a SQL Server database table. I confirmed that the column has an XML datatype.

I use this query to read the attribute but the result is always NULL values. I don't get why. All the examples I find here confirm that I do it exactly right.

SELECT 
    SDMPackageDigest.value('(//DesiredConfigurationDigest/SoftwareUpdateBundle/Annotation/DisplayName/@Text)[1]', 'NVARCHAR(256)')
FROM
    v_UpdateInfo

Here is an excerpt from the XML I work with.

<DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">
  <SoftwareUpdateBundle AuthoringScopeId="Site_28E438B4-CB2F-43A2-AD6B-5A993F805BCB" LogicalName="SUM_4ca07bc3-8c95-4644-be17-040a3964a02a" Version="201">
<Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">
  <DisplayName Text="Security Update for Microsoft OneNote 2010 (KB3114885) 32-Bit Edition" />
  <Description Text="A security vulnerability exists in Microsoft OneNote 2010 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability." />
</Annotation>
...

marc_s · Accepted Answer · 2016-08-23 15:21:12Z

2

You need to respect the XML namespaces!

Try something like this:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration',
                           'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS ns)
SELECT 
    SDMPackageDigest.value('(/DesiredConfigurationDigest/SoftwareUpdateBundle/ns:Annotation/ns:DisplayName/@Text)[1]', 'NVARCHAR(256)')
FROM
    v_UpdateInfo

answered Aug 23, 2016 at 15:21

marc_s

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

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

1 Comment

sHahnanigans Over a year ago

Thank you. That works. And I see how I would be able to use it with other XML contents.

Devart · Accepted Answer · 2016-08-23 14:24:41Z

0

SELECT @x.value('(/*:DesiredConfigurationDigest/*:SoftwareUpdateBundle/*:Annotation/*:DisplayName/@Text)[1]', 'NVARCHAR(256)')

answered Aug 23, 2016 at 14:24

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

sHahnanigans Over a year ago

Thanks, but I don't know how to make use of this :(

Collectives™ on Stack Overflow

How to query the attribute value of an XML element in SQL Server with xpath

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related