Solved

Query XML

Posted on 2009-04-10
2
710 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
[X]
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
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

734 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