Link to home
Start Free TrialLog in
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
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

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

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

ASKER

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

This doesn't wotk either.
Avatar of Cynamon

ASKER

It just returns a blank value.
>>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.
Avatar of 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
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial