For XML Explicit Nesting Data Incorrectly

Question

ANSWERED THIS AFTER ALL - OUTLINED BELOW IN CASE IT WILL HELP SOME OTHER UNFORTUNATE SOUL THAT HAS TO USE XML EXPLICIT :)

I have the following code which outputs the XML included below. The issue I'm running into is that there should be one shipping_info and associated_pin_pad section in each of the request_status_update sections but instead they are all appearing in the last request_status_update only.

Alternately, if anybody can point me to a way to do this kind of nesting with XML PATH, that might be simpler, I'm all for that too. I just couldn't figure out how to to get one header section but then still multiple request_status_update sections. XML PATH seems to expect you to always be doing something on a row-by-row basis and not have just a header section.

Thanks in advance for any help on this. I know it's a long code same (sorry about that)

SQL CODE:

SELECT    
    1 AS Tag
    , null AS Parent
    , 2 as 'process_document!1!version'

    , null as 'header!2!'
    , null as 'header!2!sender!Element'
    , null as 'header!2!receiver!Element'
    , null as 'header!2!creation_dt!Element'

    , null as 'request_status_updates!3!'
    , null as 'request_status_update!4!request_id!Element'
    , null as 'request_status_update!4!status_code_id!Element'
    , null as 'request_status_update!4!status_reason_code_id!Element'
    , null as 'request_status_update!4!comments!Element'
    , null as 'request_status_update!4!serialnumber!Element'

    , null as 'associated_pin_pad!5!'
    , null as 'associated_pin_pad!5!device_type_id!Element'
    , null as 'associated_pin_pad!5!serialnumber!Element'

    , null as 'shipping_info!6!'
    , null as 'shipping_info!6!shipping_method_id!Element'
    , null as 'shipping_info!6!shipping_dt!Element'
    , null as 'shipping_info!6!shipping_no!Element'
    , null as 'shipping_info!6!shipped_with!Element'

union all

-- header element
select 
    2 as Tag
    , 1 as Parent
    , null

    , null
    , 'SENDER'
    , 'RECIPIENT'
    , getdate()

    , null  -- request status updates wrapper tag

    , null  -- request status update wrapper tag
    , null  -- request status update element 1
    , null  -- request status update element 2
    , null  -- request status update element 3
    , null  -- request status update element 4

    , null  -- pinpad wrapper tag
    , null  -- pinpad element 1
    , null  -- pinpad element 2

    , null  -- shipping wrapper tag
    , null  -- shipping element 1
    , null  -- shipping element 2
    , null  -- shipping element 3
    , null  -- shipping element 4

union all

select  
    3 as Tag
    , 1 as Parent
    , null

    , null
    , null
    , null
    , null

    , null  -- request status updates wrapper tag

    , null  -- request status update wrapper tag
    , null  -- request status update element 1
    , null  -- request status update element 2
    , null  -- request status update element 3
    , null  -- request status update element 4

    , null  -- pinpad wrapper tag
    , null  -- pinpad element 1
    , null  -- pinpad element 2

    , null  -- shipping wrapper tag
    , null  -- shipping element 1
    , null  -- shipping element 2
    , null  -- shipping element 3
    , null  -- shipping element 4

union all

-- request status update element    
select
    4 as Tag
    , 3 as Parent
    , null

    , null -- header element wrapper tag
    , null -- header element 1
    , null -- header element 2
    , null -- header element 3

    , null  -- request status updates wrapper tag

    , RequestID
    , StatusCodeID
    , StatusReasonCodeID
    , Comment
    , SerialNumber

    , null  -- pinpad wrapper tag
    , null  -- pinpad element 1
    , null  -- pinpad element 2

    , null  -- shipping wrapper tag
    , null  -- shipping element 1
    , null  -- shipping element 2
    , null  -- shipping element 3
    , null  -- shipping element 4

from dbo.usr_StatusUpdate u

union all

-- Get pinpad information
select
    5 as Tag
    , 4 as Parent
    , null

    , null -- header element wrapper tag
    , null -- header element 1
    , null -- header element 2
    , null -- header element 3

    , null  -- request status updates wrapper tag

    , null  -- request status update wrapper tag
    , null  -- request status update element 1
    , null  -- request status update element 2
    , null  -- request status update element 3
    , null  -- request status update element 4

    , null      -- pinpad wrapper tag
    , 'PHRSA'   -- placeholder until we get the device type table set
    , ltrim(rtrim(dp.CONTSERIALNBR))

    , null  -- shipping wrapper tag
    , null  -- shipping element 1
    , null  -- shipping element 2
    , null  -- shipping element 3
    , null  -- shipping element 4

from dbo.usr_StatusUpdate up
     inner join dbo.SOP30300 dp on dp.SOPNUMBE = up.SOPNUMBE
where dp.ITEMNMBR like '%PINPAD%'

union all

-- Get shipping information
select
    6 as Tag
    , 4 as Parent
    , null

    , null -- header element wrapper tag
    , null -- header element 1
    , null -- header element 2
    , null -- header element 3

    , null  -- request status updates wrapper tag

    , null  -- request status update wrapper tag
    , null  -- request status update element 1
    , null  -- request status update element 2
    , null  -- request status update element 3
    , null  -- request status update element 4

    , null      -- pinpad wrapper tag
    , null      -- pinpad element 1
    , null      -- pinpad element 2

    , null                                      -- shipping main container
    , ltrim(rtrim(sh.SHIPMTHD))                 -- shipping element 1
    , sh.ACTLSHIP                               -- shipping element 2
    , null                                      -- shipping element 3
    , null                                      -- shipping element 4
from dbo.usr_StatusUpdate sp
     inner join dbo.SOP30200 sh on sh.SOPNUMBE = sp.SOPNUMBE

FOR XML explicit

XML OUTPUT: (only included the request_status_updates section since this is the problem area

<process_document version="2">
  <header>
    <sender>SENDER</sender>
    <receiver>RECIPIENT</receiver>
    <creation_dt>2013-05-10T10:24:08.593</creation_dt>
  </header>
  <request_status_updates>
    <request_status_update>
      <request_id>2016582</request_id>
      <status_code_id>CMPLT</status_code_id>
      <status_reason_code_id>COMPLT</status_reason_code_id>
      <comments>Test request status update interface.</comments>
      <serialnumber>123456</serialnumber>
    </request_status_update>
    <request_status_update>
      <request_id>2016583</request_id>
      <status_code_id>CMPLT</status_code_id>
      <status_reason_code_id>COMPLT</status_reason_code_id>
      <comments>Test request status update interface.</comments>
      <serialnumber>123457</serialnumber>
      <associated_pin_pad>
        <device_type_id>PHRSA</device_type_id>
        <serialnumber>30500835-AC812195</serialnumber>
      </associated_pin_pad>
      <associated_pin_pad>
        <device_type_id>PHRSA</device_type_id>
        <serialnumber>30501048-ac013910</serialnumber>
      </associated_pin_pad>
      <shipping_info>
        <shipping_method_id>OTHER</shipping_method_id>
        <shipping_dt>2013-04-24T00:00:00</shipping_dt>
      </shipping_info>
      <shipping_info>
        <shipping_method_id>OTHER</shipping_method_id>
        <shipping_dt>2012-08-30T00:00:00</shipping_dt>
      </shipping_info>
    </request_status_update>
  </request_status_updates>
</process_document>

EXPECTED OUTPUT:

<request_status_updates>
    <request_status_update>
      <request_id>2016582</request_id>
      <status_code_id>CMPLT</status_code_id>
      <status_reason_code_id>COMPLT</status_reason_code_id>
      <comments>Test request status update interface.</comments>
      <serialnumber>123456</serialnumber>
      <associated_pin_pad>
        <device_type_id>PHRSA</device_type_id>
        <serialnumber>30501048-ac013910</serialnumber>
      </associated_pin_pad>
      <shipping_info>
        <shipping_method_id>OTHER</shipping_method_id>
        <shipping_dt>2013-04-24T00:00:00</shipping_dt>
      </shipping_info>
    </request_status_update>
    <request_status_update>
      <request_id>2016583</request_id>
      <status_code_id>CMPLT</status_code_id>
      <status_reason_code_id>COMPLT</status_reason_code_id>
      <comments>Test request status update interface.</comments>
      <serialnumber>123457</serialnumber>
      <associated_pin_pad>
        <device_type_id>PHRSA</device_type_id>
        <serialnumber>30500835-AC812195</serialnumber>
      </associated_pin_pad>
      <shipping_info>
        <shipping_method_id>OTHER</shipping_method_id>
        <shipping_dt>2012-08-30T00:00:00</shipping_dt>
      </shipping_info>
    </request_status_update>
  </request_status_updates>

FIX:

I needed to add this order by clause,

order by [request_status_update!4!request_id!Element], Tag

In addition, starting with the query for Tag 4, I needed to make sure that RequestID was actually included in the result instead of NULL so that the sorting would work.

I'm also going to look into using XML PATH which is supposed to be simpler than XML EXPLICIT but so far this is working for now.

Answer 1

FIX:

I needed to add this order by clause,

order by [request_status_update!4!request_id!Element], Tag

In addition, starting with the query for Tag 4, I needed to make sure that RequestID was actually included in the result instead of NULL so that the sorting would work.

I'm also going to look into using XML PATH which is supposed to be simpler than XML EXPLICIT but so far this is working for now.