Getting error: "Top-level attribute nodes are not supported"

Question

Hi all,
I'm hoping this is a really simple one. I'm executing the following:

declare @x xml = '<Property Name="SomePropertyName">3</Property>';

select	@x.query('@Name');

and its giving me the error you see in the subject of this thread. Please can someone
1) Tell me what the rror means and why I'm getting it
2) (More importantly) Tell me how to fix it.

Oh, FYI, I'm simply trying to extract the value of the "Name" attribute (i.e. 'SomePropertyName')


Thanks in advance all!

JamieT

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 1

Hi, Jamie.:

  There's actually several different things going on here, first, AFAIK, an XQuery of '@Name' means {"return all of the nodes and their children that are attributes, called 'Name', and that are at the top"} (because there's no child or descent operators like '/' or '//').  So the error is just saying that Attributes cannot be at the top, only Elements can be at the top (because Attributes are basically like scalar parameter values). To fix this you need syntax like ("ElementName/@AttributeName").

Secondly, most of the XML methods require single values or XML nodes to be returned (".nodes()" is the exception) and unlike SQL, Xquery is not willing to wait until execution to see if this works out, you must supply an Xquery that syntactically, can *only* return a single value.  Thus ("Property/@Name") will not work, because it *might* find more than one "<Property>" element.  The usual way to fix this is to include an occurrence ordinal predicate ("[..]") value, typically "1" to indicate the first occurrence of the preceding Xquery spec.  So, ("Property[1]/@Name") would tell it to return the first top-level Property element's Name attribute.  You do not have to throw another "[1]" after @Name only because Attributes have to be unique by name within their parent Elements.

Finally, the XML.query() method is going to try to return an XML data-type and a scalar value like "SomePropertyName" isn't XML, however you can coax it into converting it into an Element (which is XML) with the Xquery string() function.  Alternatively, you can use the XML.value() function which normally does return scalar values.  Here's an example of both that should work for you:

declare @x xml = '<Property Name="SomePropertyName">3</Property>';

select	@x.query('string(Property[1]/@Name)');
select	@x.value('Property[1]/@Name','varchar(32)');

Hope this helps.

(ps., Love your blog.  It was a huge help to me in learning and starting up on SSIS.)

---

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please! Remember to Vote all helpful replies as Helpful

Answer 2

Correction: the reason why x.query('Property[1]./@Name') does not work is because it returns an Attribute, which cannot be XML on its own.  The Xquery string() or data() function fix this because they extract the Attribute's value only, which is returned as a string that x.query() then implicitly converts into a XML "Data".

(I think that I have this right, now.  I basically had it backwards before.)

---

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please! Remember to Vote all helpful replies as Helpful

Answer 3

Barry,
Thankyou SO much, you've really helped me out of a hole here. Look for something along these lines turning up on the new blog in the next few days.

I admit I don't yet fully understand WHY it works but to be honest I don't really care :)

Oh, and thank you for the kind words about the blog too - nice to know it is appreciated.

cheers
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 4

Stil there Barry, fancy another brain teaser? :)

The ultimate aim here is to query the contents of a .dtsx file using xquery. What I want to do is get all the name-value pair properties of the package. Your code above works but for some reason when I plug it into a bigger query it doesn't and I can't work out why.

Run the following query:

SELECT	Pkg.props.query('.')						as property
,		Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";p1:Property[1]/@p1:Name','varchar(32)')					as name
,		Pkg.props.value('.', 'nvarchar(max)')		as value
FROM	(
		select	cast(pkgblob.BulkColumn as XML) pkgXML
		from	openrowset(bulk 'C:\tmp\MyPkg.dtsx',single_blob) as pkgblob
		) t
CROSS	APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
						/DTS:Executable/DTS:Property') Pkg(props)

(replacing 'C:\tmp\MyPkg.dtsx' with a package on your hard drive.)

For some reason it doesn't pull out the name of the property and I cannot for the life of me work out why. Any help is much appreciated.

-Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 5

Barry,
...
I admit I don't yet fully understand WHY it works but to be honest I don't really care :)
...
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Honestly, I've been using it intermittently for over a year now, and I still feel like that most of the time.  :-)

---

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please! Remember to Vote all helpful replies as Helpful

Answer 6

The XML.nodes() function returns a rowset of pointers into the XML structure, that point to each matching instance of the Xquery.  In your case that searxht is "/DTS:Executable/DTS:Property", which means that for each returned row, the "current pointer" or "." is pointing to each instance of "/DTS:Executable/DTS:Property".  However, this also means that you need to replace the previous references to "Property" with a reference to the current pointer (".") instead.  

This works for me:

SELECT	Pkg.props.query('.')						as property
,		Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Name','varchar(32)')					as name
,		Pkg.props.value('.', 'nvarchar(max)')		as value
FROM	(
		select	cast(pkgblob.BulkColumn as XML) pkgXML
		from	openrowset(bulk 'C:\byoung\SQL Server\SSIS\TemplatePackage\TemplatePackage.dtsx',single_blob) as pkgblob
	) t
CROSS	APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
						/DTS:Executable/DTS:Property') Pkg(props)
						

---

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please! Remember to Vote all helpful replies as Helpful

Answer 7

GENIUS! Thanks Barry!

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet