ORDER BY syntax with an XML column in SQL 2005

Question

I have a user profile(more than one profile based on user type) which I'm storing in a DB column(xml).

I can query on this using XPATH in my stored procedure, however I am unsure how to then perform an ORDER BY.

SELECT U.UserId, UP.Profile, UP.UserParentID
FROM aspnet_Users U
LEFT OUTER JOIN UserProperties UP ON U.UserId = UP.UserId
WHERE
UP.Profile.exist('/Properties/property[contains(.,sql:variable("@cLookup"))]') = 1

Example XML:

<Properties>
      <property id="BusinessName" name="Business Name"></property>
      <property id="AccountNumber" name="Account Number"></property>
      <property id="Address" name="Address"></property>
      <property id="Phone" name="Phone"></property>
      <property id="Fax" name="Fax"></property>
      <property id="Web" name="Web"></property>
      <property id="ABN" name="ABN"></property>
      <property id="Logo" name="Logo"></property>
      <property id="Photos" name="Photos"></property>
      <property id="Map" name="Location Map"></property>
     </Properties>

AND

<Properties>
      <property id="FirstName" name="First Name"></property>
      <property id="LastName" name="Last Name"></property>
     </Properties>

Would want to ORDER BY BusinessName

Answer 1

Consider this answer a work in progress, because I don't know for sure that this is the right query.

The answer lies in the use of the .nodes() function combined with .value() so you can pull the value for the "name" attribute out of the element with "BusinessName" as the value of the "id" attribute.

There's a bit of a guide to using .nodes() to replace the old OPENXML syntax here.

Anyway, here's the query as it stands. Try it on your data and let's see if we can adjust it until it works.

SELECT U.UserId, UP.Profile, UP.UserParentID
FROM aspnet_Users U
    LEFT OUTER JOIN UserProperties UP ON U.UserId = UP.UserId
    OUTER APPLY UP.Profile.nodes('/Properties/property') p(prof)

WHERE UP.Profile.exist('/Properties/property[contains(.,sql:variable("@cLookup"))]') = 1
    AND p.prof.value('@id', 'nvarchar(20)') = 'BusinessName'

ORDER BY p.prof.value('@name', 'nvarchar(100)')

Answer 2

Another option you might want to look at is computed columns, especially if you have one or a few columns that you need frequently.

A computed column in general is just that - a column that has a value computed from something else, and that is up to date at any time without you having to refresh it all the time.

In conjunction with XML, you can write a small stored function and use that to create a computed column on your "base" table, so that you can query and order by that column without having to "reach into" the XML all the time.

In your case here, you could write a stored function to retrieve the "BusinessName" and make it available on your UserProfile table like this:

CREATE FUNCTION dbo.GetBusinessName(@input XML)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS BEGIN
  DECLARE @Result VARCHAR(50)

  SELECT 
    @Result = @input.value('(Properties/property[@id="BusinessName"]/@name)[1]', 'VARCHAR(50)')

  RETURN @Result
END

This defines a stored function that will take your profile XML and go look for the "property" with the "id" of "Business Name" and returns its "name" attribute back.

To add this to your UserProfile table, use this SQL code:

ALTER TABLE UserProfile
    ADD BusinessName AS dbo.GetBusinessName(Profile) PERSISTED

With this, you add a new computed field called "BusinessName" to your table, and from now on, you can select and order by using this new field, e.g.

SELECT ID, BusinessName FROM UserProfiles ORDER BY BusinessName

Enjoy!

Cheers! Marc

PS: A word about performance: I have found in many cases, this is much faster than constantly keeping to query your XML in an XML field. So if you have certain elements in your XML that you need access to frequently, or that might show up in an ORDER BY statement, using a computed column can actually speed things up considerably.