I have two tables Tasks and Notes
Tasks
| Id | Task | |----|------| | 1 | task1| | 2 | task2| | 3 | task3|
Notes
| Id | TaskId | Text | |----|--------|------| | 1 | 1 | text1| | 2 | 1 | text2| | 3 | 1 | text3| | 4 | 2 | text4| | 5 | 2 | text5| | 6 | 3 | text6|
I want the result of the fetchxml to be:
[ {Id: 1, Task: "task1", Notes: ["text1", "text2", "text3"]},{Id: 2, Task: "task2", Notes: ["text4", "text5"]},{Id: 3, Task: "task3", Notes: ["text6"]}]
I know how to do it using SQL, struggling to find any help to do it using fetchxml can anyone please help me?
SELECT n.TaskId as Id,t.Task,
group_concat(n.Text) end AS Notes
FROM tasks t,notes n
On t.Id=n.TaskId
WHERE
group by n.TaskId,t.Task
I am writing fetchxml below
Select
n.TaskId as [@id],
t.Task as [@Task],
(
Select
group_concat(n.Text) as [@Notes],
FROM tasks t,notes n
On t.Id=n.TaskId
WHERE
group by n.TaskId,t.Task
FOR XML Raw('notes'),TYPE
)
FROM tasks t
WHERE
group by t.Id,t.Task
FOR XML PATH('tasks'),
ROOT('myFetchXml')