quote:

---

SELECT ROW_NUMBER() OVER(ORDER BY LEFT(UPPER(xml_index.value_string), 1)) as rownumber,alias_pagename, [content].content_title, LEFT(UPPER(xml_index.value_string), 1) AS Letter, [content].content_id, 2 AS col,value_string
FROM [content] INNER JOIN
xml_index ON [content].content_id = xml_index.content_id AND [content].content_language = xml_index.content_language INNER JOIN url_alias_tbl AS Q ON [content].content_id = Q.content_id
WHERE ([content].folder_id = @folderId) AND ([content].inherit_xml_from = @folderId) AND ([content].published = 1) AND ([content].content_type = 1) AND
([content].xml_config_id = @configId) AND ([content].content_status LIKE 'A') AND (xml_index.xpath LIKE '/root/Surname')
ORDER BY letter,value_string

---

quote:

---

SELECT value_string
FROM xml_index AS p
WHERE (O.content_id = content_id) AND (xpath = '/root/Category')) AS Category

So that in the end in my select I can add

Case category WHEN 'Managing Partner' THEN 'Partner' WHEN 'Chairman' THEN 'Partner' ELSE category End as category

But not sure how to get this done cheers

---