Solved

Query XML

Posted on 2009-04-10
2
698 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now