How to break up two xml tags with the same subchild names in SQL

I have created a script which takes data from a table in SQL and generates an XML output. The parent, child and sub-child tags are all the same which for 2 tags. The SQL script is outputting them as one XML value instead of 2.

 SELECT
 Request.TransactionRef AS [RequestHeader/RequestID],
'Deal.Trial' AS [RequestHeader/Action],
'DoDealValidate' AS [RequestHeader/ActionFlags/Flag],
'DoDealDerive' AS [RequestHeader/ActionFlags/Flag]

The current results are:

<ActionFlags>
<Flag>DoDealValidateDoDealDerive</Flag>
</ActionFlags>




<ActionFlags>
<Flag>DoDealValidate</Flag>
<Flag>DoDealDerive</Flag>
</ActionFlags>

Solution

Just place something empty in between:

SELECT
 'blah' AS [RequestHeader/RequestID],
'Deal.Trial' AS [RequestHeader/Action],
'DoDealValidate' AS [RequestHeader/ActionFlags/Flag],
NULL AS [RequestHeader/ActionFlags],
'DoDealDerive' AS [RequestHeader/ActionFlags/Flag] 
FOR XML PATH('row');

The background:

The engine is running through the SELECT's columns and builds them one after the other.

Well, there is a <RequestHeader> to open
and there is a <RequestID> to open
Again the <RequestHeader>, still open, nothin to to
and there is <Action> below... Oh, we must close the <RequestID> and open a new <Action>
and so on...

In your code the <Flag> is still open, therefore the content is written into the open element.

My change will let the engine think

Ah, we move up one level, so we close the <Flag> first... Oops, there's nothing to write...
Now there is something for <Flag>, which is not open anymore, we have to re-open a (new) <Flag> node
and so on...