T-Sql xml query

Question

How to get the title in the following xml document

DECLARE @xVar XML SET @xVar = '<reportdata genre="security" publicationdate="2002" ISBN="0-7356-1588-2"> <title>Writing Secure Code</title> <author> <first-name>Michael</first-name> <last-name>Howard</last-name> </author> <author> <first-name>David</first-name> <last-name>LeBlanc</last-name> </author> <price>39.99</price> </reportdata>' SELECT [Title]= reportdata.item.value('@title', 'varchar(40)') FROM @xVar.nodes('/reportdata') AS reportdata(item) 

This query always returns null, any ideas?

Answer 1

title is not an attribute. The following work.

 SELECT [Title]= reportdata.item.value('.', 'varchar(40)') FROM @xVar.nodes('/reportdata/title[1]') AS reportdata(item) 

Answer 2

There is no need to trim the XML in the FROM if you only need one value.

 SELECT [Title]= @xVar.value('(/reportdata/title)[1]', 'varchar(40)')