How to locate Rows in SQL Table where xPath

Question

Imagine the following table:

-------------------------------------------------------------
   ID   |    XML
-------------------------------------------------------------
    1   | <Form><object ID="1" /></Form>
    2   | <Form><object ID="2" /></Form>
    3   | <Form><object ID="2" /></Form>
-------------------------------------------------------------

I need some SQL to find all rows where ID and Form/object@ID are not the same

I'm using SQL2000. ID is int field and XML is a Text field.

Also the data in the above table is simplified and there will be additional varying elements inside the form tag.

How do I do this? Can I do this?

Answer 1

You can try Contains in your select statement, but you'll need to ensure your table is full-text indexed.

Nice field names by the way...

Answer 2

I don't think there is a nice way to do this in SQL2000. With SQL2005 you can use the xml data type to pull out the value.

My suggestion would be along the lines of

SELECT *
FROM TABLE t
WHERE CHARINDEX('ID="' + CAST(t.ID AS VARCHAR) + '"',t.XML) = 0

But it's nasty and slow.