URGENT!! Datalists + Users - frontend sorting by AccessUserSortXML?

Jacob Jensen

Posted on 13/10/2011 13:42:50

Hi,
I've got 5 groups of users (company departments). Each user can be connected to more than one group + plus be present in more than one country.
I've made a number of datalists where i use the following to extract the correct users:
AccessUserGroups CONTAINS @xxx@
AccessUserCountry CONTAINS Countryname

Now my problem - I wan't the datalist to sort by the value of AccessUserSortXML, which is defined by the manual sorting that my client has made in the User module. HOW TO DO THIS??

Replies

Morten Snedker

Posted on 13/10/2011 14:02:52

I'm not sure if I'm getting you wrong, but as I read you question,

SELECT * FROM AccessUser 
	WHERE AccessUserGroups LIKE '%@341@%' AND AccessUserCountry = 'DK' 
		ORDER BY AccessUserSortXML

would do the trick (apart from me using LIKE instead of CONTAINS). Not?

Though, I think my proposal is too obvious. :-)

Regards /Snedker

Jacob Jensen

Posted on 13/10/2011 14:43:53

Hi Snedker, the problem is that AsseccUserSortXML doesn't contain only one sorting value, but the sorting values from every group in which the user is present... For every user present in more that one group AccessUserSortXML will look somewhat like this:

<?xml version="1.0"?> 
<ArrayOfUserSort xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
    <UserSort>
        <GroupID>256</GroupID>
        <Sort>10</Sort>
    </UserSort>
    <UserSort>
        <GroupID>304</GroupID>
        <Sort>2</Sort>
    </UserSort>
</ArrayOfUserSort>

So I don't know how to get to the value for the group that I'm dealing with, and sort on that one.

Morten Snedker

Posted on 13/10/2011 15:02:42

Didn't I say that it wasn't that obvious!? :-)

Anyhow, you can query columns containing XML. An example from StackOverflow (http://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008):

select
  (list of fields)
from
  MyTable
where
  MyXmlField.value('(/root/altitude)[1]', 'int') > 10000

Is that enough to get you going. If not, please let me know.

Regards /Snedker

Morten Snedker

Posted on 13/10/2011 15:07:43

PS: I'm not sure, but you may need to cast to field type XML:

DECLARE @xml XML
Select @xml = CAST(ColData AS XML)
@xml.value('<xquery expression goes here>');

Jacob Jensen

Posted on 13/10/2011 16:21:53

Hi Morten,

I can't really figure it out.. This is what I've got this far... can you point out what to do:


SELECT [AccessUserName] ,[AccessUserEmail] ,[AccessUserPhone] ,[AccessUserJobTitle] 
,[AccessUserMobile] ,[AccessUserSortXML] FROM [AccessUser] WHERE [AccessUserGroups] 
LIKE '%@304@%' ORDER BY AccessUserSortXML.value('(/ArrayOfUserSort/UserSort[GroupID=304]/Sort)[1]', 'int') ASC

I don't know how to do the CAST you mention... (read: I'm totally green here :-))
What am I doing wrong?

Morten Snedker

Posted on 20/10/2011 16:10:27

Hi Jacob,

Good news and bad news

Good news is a working example of SQL-statement that applies to your specific site:

SELECT CAST(AccessUserSortXML as XML).query ('//ArrayOfUserSort/UserSort[GroupID="304"]/Sort') AS SortValue,*
FROM AccessUser 
WHERE AccessUserUserName = 'mt'

Bad news is that there seems to be a bug. If you from the backend perform a sorting then the XML seems to be saved with an invalid character somewhere (probably a CR or LF), which will make the SQL-server cast an error on the otherwise working SQL statement.

The bug has been registered as bug #7336, and has not yet been prioritized.

Regards
Morten Snedker

Morten Bengtson

Posted on 20/10/2011 16:32:07

A workaround is to create a view in sql server that makes use of the sql statement provided by snedker. You can then query this view in your datalist without any problems... as far as i know.

Jacob Jensen

Posted on 25/10/2011 09:51:52

Any ideas on when a possible bug-fix can be made? My client is getting a bit frustrated... And I don't know how to do the thing Morten Bengtson suggests :-) PS. I there any logic to the bug-numbering in the known issues list? Are all the bug-numbers in between solved or not? :-)
http://developer.dynamicweb-cms.com/releases/known-issues.aspx

Morten Snedker

Posted on 27/10/2011 12:21:26

Hi Jacob,

Bugs are not fixed in same order as they come in. They are prioritized and resolved based on priority. I wouldn't say that we have an awful lot of incidents with this particular bug! :-)

However, I understand where you're at, so I'll try to get it fixed asap. I'll give you an update tomorrow.

Regards /Snedker

Jacob Jensen

Posted on 07/11/2011 14:00:55

Hi Morten,

Now the bug is fixed in 19.2.6.1, I've tried to run the following code, but I get an error (The XML data type cannot be compared or sorted, except when using the IS NULL operator.):

SELECT CAST(AccessUserSortXML as XML).query ('//ArrayOfUserSort/UserSort[GroupID="302"]/Sort') AS SortValue,*
FROM AccessUser 
WHERE [AccessUserGroups] LIKE '%@302@%' ORDER BY SortValue ASC

Morten Snedker

Posted on 08/11/2011 09:18:46

Hi Jacob,

You're trying to sort the result of an XML query. This doesn't make sense, since the result can be "what-ever", and thus not recognized as sortable. So you need to cast the result to a sortable data type. Get inspiration from http://nativexmldatabase.com/2010/08/11/how-to-order-query-results-based-on-xml-values/.

Jacob Jensen

Posted on 09/11/2011 00:33:20

It might be easy for a person with SQL experience to grab inspiration, but I cant get it together.
I know I'm trying to sort on the result of an XML query, but I don't know enough about the SQL syntax to write something that works.

Morten Bengtson

Posted on 09/11/2011 09:00:43

This post has been marked as an answer

You can try using the value function...

SELECT CAST(AccessUserSortXML as XML).value('(/ArrayOfUserSort/UserSort[GroupID="320"]/Sort)[1]','int') AS SortValue,* FROM AccessUser WHERE [AccessUserGroups] LIKE '%@320@%' ORDER BY SortValue ASC

The syntax is correct, but it threw an error when I tested it, because the xml stored in AccessUserSortXML contains invalid characters.

Votes for this answer: 0

Jacob Jensen

Posted on 09/11/2011 16:16:32

The above answer by Morten Bengtson solved the problem when running on a 19.2.6.1 core where bug #7336 is solved. My initial project was to publish several users frontend. All users had relations to multiple user groups, and needed individual sorting per user group. Each user group is sortable in the backend, but my problem was that it wasn't possible to sort the output by AccessUserSortXML in frontend, because this column includes the sort values from every user group the individual user has a relation to, and it's formed as XML. The above SQL-sentence solves the problem, all you now have to change is the GroupID for every data list you create.

THANX 2 x Morten!!

You must be logged in to post in the forum

Developer forum

URGENT!! Datalists + Users - frontend sorting by AccessUserSortXML?

Replies