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,
Testing script
P.S. It is good idea to use geometry columns from SQL 2008 to store coordinates.