CODEWITHSUNDEEP
sql-serverxmlxpathsql-server-2012
sHahnanigans
sHahnanigans

Reputation: 55

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

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>
...

Upvotes: 2

Views: 814

Answers (2)

marc_s
marc_s

Reputation: 755451

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

Upvotes: 2

Devart
Devart

Reputation: 122042

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

Upvotes: 0

Related Questions