Get stored procedure output in desired XML format

Question

I have this query in a stored procedure:

SELECT 
    IncidentEventID AS Id, -- Always select first column as the primary key column and use alias as Id 
    CreatedDate,
    IncidentEventName,
    IncidentEventDesc
FROM
    vIncidentEvent
WHERE
    IncidentID = 987148059
    AND AuditTypeID IS NULL;

That generates a response like this:

Id	CreatedDate	IncidentEventName	IncidentEventDesc
121144	8/2/2024 11:32:57 AM	Request Initiated	RC_GA_BASELINE NBI initiated by Admin, LBMS
121145	8/2/2024 11:38:17 AM	Task Completed	Enter Parties and Key Info completed by Admin, LBMS

I want XML response from the same query like,

<xml>
  <rows>
    <row id="121144">
      <cell>
        <![CDATA[8/2/2024 11:32:57 AM]]>
      </cell>
      <cell>
        <![CDATA[Request Initiated]]>
      </cell>
      <cell>
        <![CDATA[RC_GA_BASELINE NBI initiated by Admin, LBMS]]>
      </cell>
    </row>
    <row id="121145">
      <cell>
        <![CDATA[8/2/2024 11:38:17 AM]]>
      </cell>
      <cell>
        <![CDATA[Task Completed]]>
      </cell>
      <cell>
        <![CDATA[Enter Parties and Key Info completed by Admin, LBMS]]>
      </cell>
    </row>
  </rows>
</xml>

I tried the modification as below,

SELECT 
    IncidentEventID AS [@id], -- Alias the primary key as an XML attribute
    CreatedDate AS 'cell',
    IncidentEventName AS 'cell',
    IncidentEventDesc AS 'cell'
FROM
    vIncidentEvent
WHERE
    IncidentID = 987148059
    AND AuditTypeID IS NULL
FOR XML PATH('row'), ROOT('rows'), TYPE;

But I get the response as follows, all in one cell - I don't want that.

<rows>
  <row id="121144">
    <cell>8/2/2024 11:32:57 AMRequest InitiatedRC_GA_BASELINE NBI initiated by Admin, LBMS</cell>
  </row>
  <row id="121145">
    <cell>8/2/2024 11:38:17 AMTask CompletedEnter Parties and Key Info completed by Admin, LBMS</cell>
  </row>
</rows>

How can I get the desired XML response?

Answer 1

Note the NULL's between the CELL columns

Example

Declare @YourTable Table ([Id] int,[CreatedDate] datetime,[IncidentEventName] varchar(50),[IncidentEventDesc] varchar(150))  
Insert Into @YourTable Values 
 (121144,'8/2/2024 11:32:57 AM','Request Initiated','RC_GA_BASELINE NBI initiated by Admin, LBMS')
,(121145,'8/2/2024 11:38:17 AM','Task Completed','Enter Parties and Key Info completed by Admin, LBMS')
 
select xml=(
SELECT 
    id AS [@id], -- Alias the primary key as an XML attribute
    null,
    CreatedDate AS 'cell',
    null,
    IncidentEventName AS 'cell',
    null,
    IncidentEventDesc AS 'cell'
FROM
    @YourTable
FOR XML PATH('row'), ROOT('rows'), TYPE
) 
For XML path('')

Results

<xml>
  <rows>
    <row id="121144">
      <cell>2024-08-02T11:32:57</cell>
      <cell>Request Initiated</cell>
      <cell>RC_GA_BASELINE NBI initiated by Admin, LBMS</cell>
    </row>
    <row id="121145">
      <cell>2024-08-02T11:38:17</cell>
      <cell>Task Completed</cell>
      <cell>Enter Parties and Key Info completed by Admin, LBMS</cell>
    </row>
  </rows>
</xml>