Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query XML

Posted on 2009-04-10
2
708 Views
Last Modified: 2013-11-11
Hello,

This is a very basic XML question, but I have yet to find a simple, easy to understand solution.  I have an application that contains images.  There is a SQL Server 2008 database which contains an Images table.  The Images table contains an XML data attribute called "Tags".  This contains an XML document like the following:

<ProductImage>
  <tag>Movies</tag>
  <tag>Funny</tag>
  <tag>Men</tag>
  <tag>Green</tag>
  <tag>Callihan</tag>
</ProductImage>

What I want to do is query the Tags column for all the rows in the Images table.  For example, I want to find all images that have a tag of "Green".  How would I write the TSQL code to do this?  Idealy, I want the selection to return the ImageID of the matching rows that contain the desired tag values.  

Thanks!
0
Comment
Question by:ideamatics
2 Comments
 
LVL 11

Accepted Solution

by:
CraigYellick earned 500 total points
ID: 24122429
SQL Server's XML capabilities are very powerful but also difficult to master so don't be discouraged if you have trouble getting started.

You need to use the .Exist method of the Tags column to test for the presence of a particular node or value in the XML.  The .Exist method returns 0 or 1.

The .Exist method uses an XPath expression which is expected to return a node set, so the expression must be placed in parenthesis.

Further, you need to reference which node in the node list is of interest. In your case you only expect to see one tag with "Green" (and even if there were multiple "Green" tags, the first one is good enough for a match) so the square brackets around the 1 indicate the first node in the node set.

See? Nothing could be easier! (Joke)
select * from Images
where Tags.exist('(/ProductImage/tag[text()="Green"])[1]') = 1

Open in new window

0
 

Author Closing Comment

by:ideamatics
ID: 31569087
This is exactly what I was looking for.  Thanks!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question