Select records from XML column (SQL Server 2005) based on node order

Question

I have a column in a SQL Server 2005 table defined as an XML column. Is there a way to select records from this table based on the order of two nodes in that column? For example, we have the following structure in our XML:

<item>
   <latitude/>
   <longitude/>
</item>

I want to see if there are any records that have latitude/longitude in the opposite order i.e. <longitude/> then <latitude/>. I've tried some XPath expressions but with no luck.

Answer 1

Well, you could use an associated xsd to enforce the order. I'll see it I can query it conveniently...

(update) I'm not sure you can... the appropriate axis are all disabled, as is position(name) and node/position()

Answer 2

We do have an xsd associated with the column but it's not enforcing order. We want to enforce order but we want to see if any of the columns are in the opposite order before we make this change.

Answer 3

You can check if the first child of item element is longitude,

SELECT COUNT(1)
FROM tblFoo
WHERE xmlCol.value('local-name((/item/*)[1])', 'varchar(max)') = 'longitude'

Testing script

DECLARE @xml as xml
SET @xml = 
'<item>
   <longitude/>
   <latitude/>      
</item>
'
IF @xml.value('local-name((/item/*)[1])', 'varchar(max)') = 'longitude'
    PRINT 'first element is longitude'

--Output: first element is longitude

P.S. It is good idea to use geometry columns from SQL 2008 to store coordinates.