How can I get data from joined tables using fetchxml

Question

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?

Answer 1

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')