Solved

Query XML

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

823 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