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