Using SQL to Generate XML

To create this XML block:

<EventArguments>
<OccurrenceId>300031738</OccurrenceId>
<HoursInProcess>123</HoursInProcess>
<ContactName>McCusker &amp; Ogborne &lt;Other&gt;</ContactName>
<AccidentFormId>795</AccidentFormId>
<BusinessUnitNumber>*</BusinessUnitNumber>
</EventArguments>

You would do this:

SELECT '300031738' AS OccurrenceId,
123 AS HoursInProcess,
'McCusker & Ogborne <Other>' AS ContactName,
795 AS AccidentFormId,
'*' AS BusinessUnitNumber,
FOR XML RAW('EventArguments'), ELEMENTS

-

For more sophisticated XML, you can do sub queries. This may not make sense to include this particular table, but it shows how it would work.

SELECT '300031738' AS OccurrenceId,
123 AS HoursInProcess,
'McCusker & Ogborne <Other>' AS ContactName,
795 AS AccidentFormId,
'*' AS BusinessUnitNumber,
(SELECT TOP 5 ProcessedMessageLogId FROM ProcessedMessageLog FOR XML RAW(''), ELEMENTS, TYPE) AS Children
FOR XML RAW('EventArguments'), ELEMENTS

- Which creates the following XML:

<EventArguments>
<OccurrenceId>300031738</OccurrenceId>
<HoursInProcess>123</HoursInProcess>
<ContactName>McCusker &amp; Ogborne &lt;Other&gt;</ContactName>
<AccidentFormId>795</AccidentFormId>
<BusinessUnitNumber>*</BusinessUnitNumber>
<Children>
<ProcessedMessageLogId>3281</ProcessedMessageLogId>
<ProcessedMessageLogId>3282</ProcessedMessageLogId>
<ProcessedMessageLogId>3283</ProcessedMessageLogId>
<ProcessedMessageLogId>3284</ProcessedMessageLogId>
<ProcessedMessageLogId>3285</ProcessedMessageLogId>
</Children>
</EventArguments>