0

How to sort XML Attributes in SQL?

for example for this XML:

<books><book b='' c='' a=''/></books>

I want:

<books><book a='' b='' c=''/></books>
ARZ
  • 2,461
  • 3
  • 34
  • 56
  • Truly, I want to compare two XMl fields that have same attributes in different order and XML comparison is impossible in SQL.so casting them into Varchar type does not return the true result.so I think that the single solution is to sort XML attributes and then compare XML strings. – ARZ May 17 '12 at 05:23
  • Have you seen this? [XQuery Lab 25 - Writing a TSQL Function to compare two XML values (Part 1)](http://beyondrelational.com/modules/2/blogs/28/posts/10306/xquery-lab-25-writing-a-tsql-function-to-compare-two-xml-values-part-1.aspx). – Mikael Eriksson May 19 '12 at 17:25

1 Answers1

0

From http://msdn.microsoft.com/en-us/library/ms187107%28v=sql.90%29.aspx:

The order of the XML attributes is not preserved. When you query the XML instance stored in the xml type column, the order of attributes in the resulting XML may be different from the original XML instance.

So even if you could figure out a way of sorting the attributes, you can not trust that the XML data type in SQL Server will preserve the order you want.

Jordan Running
  • 102,619
  • 17
  • 182
  • 182
Deeps23
  • 51
  • 1