Solved

Query XML

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 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