Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2005 Xpath query help needed

Posted on 2009-07-08
2
Medium Priority
?
469 Views
Last Modified: 2013-11-11
Hi experts,

I need some help with making a query with a SQL 2005 XML column.

I have a table called EventLog, and this table has a column Details that is of type XML and contains details about an event log entry. Different event log entries may have different details. The XML for some of the entries looks like this:

(simplified)

<EventDetails>
  <item>
    <key>
      <string>UserID</string>
    </key>
    <value>
      <string>1</string>
    </value>
  </item>
  <item>
    <key>
      <string>InvoiceID</string>
    </key>
    <value>
      <string>13</string>
    </value>
  </item>
</EventDetails>

This one has two elements, UserID and InvoiceID, and their respective values.

What I need to do is to write a SQL query that will return * FROM EventLog WHERE the Details column has a InvoiceID element and its value is N (e.g. 123). This way I can find all event log entries that are somehow related to invoice 123.

Thanks in advance!
0
Comment
Question by:kerzner
[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 1

Author Comment

by:kerzner
ID: 24857981
OK, nobody here can write that xpath query!?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 24875253
SELECT      *
FROM      EventLog
WHERE      Details.exist('/EventDetails/item/key[string="InvoiceID"]') = 1
      AND Details.exist('/EventDetails/item[value="13"]') = 1
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

604 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