troubleshooting Question

Select xml field value from SQL Server 2005 table

Avatar of Cynamon
Cynamon asked on
Microsoft SQL Server
6 Comments1 Solution316 ViewsLast Modified:
I am totally new at using XQuery.
I inserted some xml as a string into a field called xml_doc with an xml data type.
I need to select a value from that field..

Example xml:
 <bookstore>
 <book category="COOKING">
  <title lang="en">Everyday Italian</title>
  <author>Giada De Laurentiis</author>
  <year>2005</year>
  <price>30.00</price>
  </book>
 <book category="CHILDREN">
  <title lang="en">Harry Potter</title>
  <author>J K. Rowling</author>
  <year>2005</year>
  <price>29.99</price>
  </book>

Example SQL:
Select year from tblXML

I've tried this, but it's returning four blank rows.
SELECT xml_doc.query('data(/root/bookstore/year[@year="2005"])') FROM tblXML

Any help would be appreciated.

Thanks,
CB
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros