Xpath returning NULL for valid Xpath expression

2
votes

I'm using a fairly straightforward Xpath query (below) to query a SQL Server 2008 database column whose values all follow the form of the example (below that). My query returns NULL for all rows, rather than the values of the example attribute (e.g. "VALUE"), even though the example attribute is defined for every row I'm querying.

I've tried declaring the xsd and xsi namespaces in my Xquery expression, and selecting from multiple different attribute names, but neither had any effect. What am I missing?

Query:

select ColumnName.value('(/RootNode/@example)[1]', 'nvarchar(15)') [Result]
  from TableName

XML sample node (actual XML contains many more attributes, which I've omitted):

<RootNode xmlns:xsd="http://www.w3.org/2001/XMLSchema"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns="http://intranet"
          example="VALUE">
  <IsValid>true</IsValid>
</RootNode>

Attempted query using namespaces, with same result:

select ColumnName.value('
declare namespace xsd="http://www.w3.org/2001/XMLSchema";
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";

(/RootNode/@example)[1]', 'nvarchar(15)') [Result]
  from TableName
2
sql-serverxmlxpathxqueryxml-namespaces
I don't see the namespace mentioned in your query. not xsi or xsd, but the default set one, http://intranet – Sten Petrov
@StenPetrov I tried adding those, but it made no difference, so I removed them – Dov
xsd and xsi won't make difference, the default namespace will. I'm having trouble also decyphering your xpath. Looks like you're looking to retrieve IsValid for a RootNode that has @example, which would be //RootNode[@example]/IsValid – Sten Petrov
@StenPetrov you're right, it did have to do with the default namespace. I will add an answer shortly. Thanks. – Dov

2 Answers

4
votes

This query works. The problem is that the data nodes declare a custom default namespace.

select ColumnName.value('
declare default element namespace "http://intranet";

(/RootNode/@example)[1]', 'nvarchar(15)') [Result]
  from TableName
4
votes

You need to add the default namespace for your xml data and most likely modify the xpath query.

I haven't run this to test but it's my best guess and should get you going:

WITH XMLNAMESPACES (N'http://intranet' as intra)
SELECT ColumnName.value('/intra:RootNode[@example]/intra:IsValid[1]', 'nvarchar(15)') [Result]
FROM TableName