Avatar of Cynamon
Cynamon
 asked on

Select xml field value from SQL Server 2005 table

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
Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Anthony Perkins

>>I need to select a value from that field..<<
Which one?

There is no "root" in the Xml you posted.
Cynamon

ASKER
SELECT xml_doc.query('data(/bookstore/year[@year="2005"])') FROM tblXML

This doesn't wotk either.
Cynamon

ASKER
It just returns a blank value.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Anthony Perkins

>>This doesn't wotk either.<<
I realize that, I did not say it did, I was merely pointing out one error.

So once again what value are you trying to select?  In other words can you tell us which element or attribute you are attempting to do an XPath.  I can speculate, but I choose not to.
Cynamon

ASKER
It doesn't really matter.  I chose [@year="2005"] just to see if I could pull  the records with the year of 2005 as shown above.
<year>2005</year>

I just need to get the correct syntax to pull something back.

Thanks for your help.
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question