sqlsql-serverxml

Get stored procedure output in desired XML format


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?


Solution

  • 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>