I am the first to admit that I am not an XML expert; in fact, I don't know much about it at all, but interestingly I use FOR XML to achieve a result that doesn't have anything to do with XML; rather, I use it to turn a set of rows into a comma delimited list. I can't even take the credit for the idea of using FOR XML in this way. About the only "new" approach I will take credit for is moving the approach out of the FROM clause as a "cross apply" function and into the SELECT clause as a subquery function.
I think perhaps the best way to explain this is to take a simple example and break it down into its component parts, one portion of the query at a time. In Shelby v.5's ShelbyQUERY module the following query will return one per person, and each row will have a comma delimited list of phone numbers for that person. The bold portion is the "primary query" that returns one row per person in the database. Each row contains the NameCounter and the full name, first and last concatenated together.
select
NameCounter,
Name = FirstMiddle + ' ' + LastName,
PhoneList = stuff((select ', ' + Descr + ': ' + PhoneNu from Shelby.NAPhones p inner join Shelby.NAPhoneTypes t on p.PhoneCounter = t.Counter where p.NameCounter = n.NameCounter order by t.Counter for xml path('')), 1, 2, '')
from Shelby.NANames nNow let's break down the column that constructs the phone list. It will make the most sense to work from the inside out. The bold portion below is a subquery that returns all of the phone numbers (and their type information) for each name. Normally this would trigger an error because subqueries in the SELECT clause cannot return more than one row of results. The FOR XML clause will allow us to ignore that rule in a way. Notice that the subquery includes an ORDER BY clause to sort the results. Notice that the subquery adds a comma in front of every value returned.
select
NameCounter,
Name = FirstMiddle + ' ' + LastName,
PhoneList = stuff((select ', ' + Descr + ': ' + PhoneNu from Shelby.NAPhones p inner join Shelby.NAPhoneTypes t on p.PhoneCounter = t.Counter where p.NameCounter = n.NameCounter order by t.Counter for xml path('')), 1, 2, '')
from Shelby.NANames n
The FOR XML clause finishes off the subquery. The PATH('') specification tells the FOR XML function to return one row of values separated by a space.
select
NameCounter,
Name = FirstMiddle + ' ' + LastName,
PhoneList = stuff((select ', ' + Descr + ': ' + PhoneNu from Shelby.NAPhones p inner join Shelby.NAPhoneTypes t on p.PhoneCounter = t.Counter where p.NameCounter = n.NameCounter order by t.Counter for xml path('')), 1, 2, '')
from Shelby.NANames nFinally, the STUFF() function takes the first two characters from the string returned by the subquery and replaces them with an empty string (thereby cutting off the extra comma at the front of the string).
select
NameCounter,
Name = FirstMiddle + ' ' + LastName,
PhoneList = stuff((select ', ' + Descr + ': ' + PhoneNu from Shelby.NAPhones p inner join Shelby.NAPhoneTypes t on p.PhoneCounter = t.Counter where p.NameCounter = n.NameCounter order by t.Counter for xml path('')), 1, 2, '')
from Shelby.NANames nTo give you a little more of a taste of how this function can work for you, here is a query that includes the phone list created above plus a list of Servant module profiles and a list of children for the individual.
select
NameCounter,
Name = FirstMiddle + ' ' + LastName,
ServantProfiles = stuff((select ', ' + Profile from Shelby.NAProfiles p where p.NameCounter = n.NameCounter and p.Profile like 'Z%' order by Profile for xml path('')), 1, 2, ''),
PhoneList = stuff((select ', ' + Descr + ': ' + PhoneNu from Shelby.NAPhones p inner join Shelby.NAPhoneTypes t on p.PhoneCounter = t.Counter where p.NameCounter = n.NameCounter order by t.Counter for xml path('')), 1, 2, ''),
Children = stuff((select ', ' + c.FirstMiddle from Shelby.NANames c where c.FamNu = n.FamNu and n.UnitNu in (1,2) and c.UnitNu = 2 order by c.Birthdate for xml path('')), 1, 2, '')
from Shelby.NANames n
