chlade
asked on
SELECT on XML field type
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
<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
ASKER
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?
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?
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
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
can u share the error message
ASKER
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
imitchie, I tried that but it returned all rows with the actual value in the column
i think the xpath is incorrect, but test null is wrong. i'll test some more
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Here's the statement I'm now using:
SELECT * FROM tblSQLEventLog
WHERE keyInfo.exist('/Customer[/
Thanks for the help!
http://msdn2.microsoft.com/en-us/library/ms189075.aspx