How can I add an attribute to the root-CodePudding

Is there a way I can add the StartDate and EndDate variables to the Report node? I tried to add select @StartDate as 'StartDate',@EndDate as 'StartDate' after the ROOT('Report') but that returns a different result.

Looking for something like this?

<Report>
<StartDate>03/01/2022</StartDate>
<EndDate>03/30/2022</EndDate>
  <Member>
    <UserActivityLogID>179</UserActivityLogID>
    <LogDate>03/10/2022</LogDate>
  </Member>
  <Member>
    <UserActivityLogID>180</UserActivityLogID>
    <LogDate>03/10/2022</LogDate>
  </Member>
</Report>

 IF @StartDate IS NULL 
        BEGIN
            SET @StartDate = DateAdd(dd, -90, GetDate())
        END
    IF @EndDate IS NULL
        BEGIN
            SET @EndDate = GetDate()
        END
    BEGIN
        SELECT [UserActivityLogID]
              ,LogDate =Convert(varchar(10),[LogDate],101)
          and LogDate BETWEEN @StartDate AND @EndDate
          FROM [UserActivityLog]
          order by LogDate desc
        FOR XML PATH('Member'),
        ROOT('Report')
    END

CodePudding user response：

At a best guess, with no meaningful sample data nor expected results, what you want is:

SELECT UserActivityLogID AS [Member/UserActivityLogID],
       @StartDate AS [@StartDate],
       @EndDate AS [@EndDate]
FROM dbo.YourTable --You didn't even have a from in your query
WHERE --This was also missing
      LogDate >= @StartDAte
  AND LogDAte <= @EndDate
FOR XML PATH ('Report');