Cannot get FOR XML PATH to work

Question

I'm having a terrible time trying to figure out how to add XML PATH to my code to concatenate some information, let alone understanding how XML PATH works. I've spent the better part of the last two days working on this and would appreciate some help!!

Here's the code I'm working with:

Select Top 100 Percent Agreements.AgrmntID, Agreements.Description As
  AgrmntDesc, Agreements.Status, AgreementSchedules.SchedDate, DateName(dw,
  AgreementSchedules.SchedDate), LaborCodeTypes.Description As LaborCode,
  Customers.CustName, Customers.CompanyName, JobSites.SiteName,
  AgreementSchedules.AgrmntSchedID
From Agreements Inner Join
  AgreementTypes On Agreements.AgrmntTypeID = AgreementTypes.AgrmntTypeID
  Inner Join
  AgreementSchedules On Agreements.AgrmntID = AgreementSchedules.AgrmntID
  Inner Join
  Customers On Agreements.CustID = Customers.CustID Inner Join
  JobSites On Agreements.CustSiteID = JobSites.CustSiteID Left Outer Join
  LaborCodeTypes On AgreementSchedules.RepairID = LaborCodeTypes.RepairID
Where Agreements.Status = 2 And Month(AgreementSchedules.SchedDate) =
  Month(GetDate())

Sample Data:

| AgreementID | LaborCodeTypes.Description   | DateName(dw, AgreementSchedules.SchedDate)|
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
| 1           | Landscaping                  | Tuesday                                   |
| 1           | Landscaping                  | Friday                                    |
| 1           | Sweeping                     | Monday                                    |
| 1           | Sweeping                     | Wednesday                                 |
| 1           | Sweeping                     | Friday                                    |
| 2           | Landscaping                  | Monday                                    |

Expected Output:

| AgreementID | LaborCode   | Days Of Week              |
| - - - - - - - - - - - - - - - - - - - - - - - - - - - |
| 1           | Landscaping | Tuesday, Friday           |
| 1           | Sweeping    | Monday, Wednesday, Friday |
| 2           | Landscaping | Monday                    |

I would greatly, greatly appreciate it if someone could help me.

Thank you in advance!!

Jamie S

Answer 1

;with C as
(
  select A.AgreementID,
         LCT.Description as LaborCode,
         Ags.ShedDate
  from Agreements as A
    inner join AgreementSchedules as AgS
      on A.AgreementID = AgS.AgreementID
    inner join LaborCodeTypes as LCT
      on AgS.RepairID = LCT.RepairID
  where A.[Status] = 2 and
        AgS.ShedDate >= dateadd(month, datediff(month, 0, getdate()), 0) and
        AgS.ShedDate < dateadd(month, 1+datediff(month, 0, getdate()), 0)
)
select C1.AgreementID,
       C1.LaborCode,
       stuff((select ', '+datename(weekday, C2.ShedDate)
              from C as C2
              where C1.AgreementID = C2.AgreementID and
                    C1.LaborCode = C2.LaborCode
              order by C2.ShedDate
              for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as [Days Of Week]
from C as C1
group by C1.AgreementID, C1.LaborCode;

SQL Fiddle

Answer 2

how XML PATH works

I will try to explain that using this setup:

create table Grp
(
  GrpID int primary key,
  Name varchar(10)
)

create table Item
(
  ItemID int identity primary key,
  Name varchar(10),
  GrpID int references Grp(GrpID)
)

insert into Grp values
(1, 'G1'),
(2, 'G2')

insert into Item values
('A', 1),
('B', 1),
('C', 1),
('D', 2),
('E', 2)

The goal is to create a result with a comma separated list of names for each group.

GroupName  ItemNames
---------- ----------
G1         A,B,C
G2         D,E

FOR XML is used to turn a query result into XML documents or XML fragments.

This query will create a XML fragment.

select I.Name
from Item as I
for xml path(''), type

Result:

<Name>A</Name>
<Name>B</Name>
<Name>C</Name>
<Name>D</Name>
<Name>E</Name>

The query above can be used in a correlated sub-query to create the XML fragment for each group like this.

select G.Name as GroupName,
       (
         select I.Name
         from Item as I
         where G.GrpID = I.GrpID
         for xml path(''), type
       ) as ItemNames
from Grp as G

Result:

GroupName  ItemNames
---------- --------------------------------------------
G1         <Name>A</Name><Name>B</Name><Name>C</Name>
G2         <Name>D</Name><Name>E</Name>

You can then use the value() function to extract the values in the XML.

select Name as GroupName,
       (
         select I.Name
         from Item as I
         where G.GrpID = I.GrpID
         for xml path(''), type
       ).value('.', 'varchar(max)') as ItemNames
from Grp as G

Result:

GroupName  ItemNames
---------- ----------
G1         ABC
G2         DE

To finish this you need to add comma as a separator and that can be done by adding a comma to every item name in the sub-query select ','+I.Name. It will leave you with an extra comma before the first value. You can use the STUFF function to remove that STUFF(Value, 1, 1, '').

Final query:

select Name as GroupName,
       stuff((
         select ','+I.Name
         from Item as I
         where G.GrpID = I.GrpID
         for xml path(''), type
       ).value('.', 'varchar(max)'), 1, 1, '') as ItemNames
from Grp as G

Answer 3

I believe I have a different way to achieve the results you are looking for...

SELECT * FROM (
    SELECT DISTINCT AgreementId, UPPER(Description), 1 AS IsLabor
    FROM Table
    WHERE ...
    UNION
    SELECT AgreementId, DateName AS Description, 0 AS IsLabor
    FROM Table
    WHERE ...
) x
ORDER BY AgreementId, IsLabor DESC, Description

This should output the following (the UPPER is just to emphasize the labor descriptions):

| AgreementId | Description | IsLabor |
---------------------------------------
| 1           | LANDSCAPING | 1       |
| 1           | Friday      | 0       |
| 1           | Tuesday     | 0       |
| 2           | SWEEPING    | 1       |
| 2           | Friday      | 0       |
| 2           | Monday      | 0       |
| 2           | Wednesday   | 0       |

Hopefully I understood your question and this will work.