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

sql-server-2005xmlxquery

I have a column in a SQL Server 2005 table defined as an XML data type. 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.

Best Answer

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.