Solved

SELECT on XML field type

Posted on 2007-11-27
8
697 Views
Last Modified: 2012-06-27
I have a database with a column of type "xml".  I have data such as this in the column:

<Customer>ABC Company</Customer>
<Customer>XYZ Company</Customer>
<OrderNo>123</OrderNo>
<Customer>ABC Company</Customer>

Can I run a SELECT statement to find all records that have a Customer of "ABC Company" in this column?  In other words, the first and fourth records only?

How would I do this?

Thanks,
Chris

0
Comment
Question by:chlade
  • 3
  • 3
  • 2
8 Comments
 
LVL 7

Expert Comment

by:Chandan_Gowda
Comment Utility
Please follow the below link i think it might help you
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
0
 

Author Comment

by:chlade
Comment Utility
Thanks.  I can't seem to quite get it.  Here is my SELECT statement:

SELECT keyInfo.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";        
    /AWMI:root[@Customer="ABC Company"]
') AS Result
FROM tblSQLEventLog

My data in the KeyInfo column looks like the examples in my first post.

What am I missing?
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
SELECT *
FROM tblSQLEventLog
WHERE keyInfo.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";        
    /AWMI:root[@Customer="ABC Company"]
') Is not null
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
Comment Utility
can u share the error message
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:chlade
Comment Utility
My original select statement was returning all rows but the columns showed up empty (not sure if it's null, spaces, or empty - to me it looks empty)

imitchie, I tried that but it returned all rows with the actual value in the column
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
i think the xpath is incorrect, but test null is wrong. i'll test some more
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
SELECT *
FROM tblSQLEventLog
WHERE keyInfo.exist('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";        
    /Customer[/="ABC Company"]
') = 1

I can't get the namespace to work, probably because you have not shown the full XML. however the key points are:
1. use xml.exist() = 1 to test
2. /Customer[@Customer="X"] is testing for the Attribute Customer in the node Customer, whereas /Customer[/="X"] is testing for the node value of Customer
0
 

Author Comment

by:chlade
Comment Utility
I just copied the namespace part from another sample.  It looks like it's not necessarily needed.  But the SELECT statement you gave worked great.

Here's the statement I'm now using:
SELECT * FROM tblSQLEventLog
WHERE keyInfo.exist('/Customer[/="ABC Company"]') = 1

Thanks for the help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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.

762 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

15 Experts available now in Live!

Get 1:1 Help Now