T-Sql xml query

Question

How can i 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?

Martin Smith · Accepted Answer · 2012-01-18 12:01:47Z

3

title is an element not an attribute. The following works.

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

answered Jan 18, 2012 at 12:01

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Comments

Mikael Eriksson · Accepted Answer · 2012-01-18 12:05:35Z

2

There is no need to shred the XML in the FROM clause if you only want one value.

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

answered Jan 18, 2012 at 12:05

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

T-Sql xml query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related