Для XML явно вложенных данных неправильно

Question

ОТВЕТИЛ НА СЛЕДУЮЩИЙ ВСЕ - ВЫДЕРЖИЛ НИЖЕ В СЛУЧАЕ, ПОМОЖЕТ НЕКОТОРЫЕ ДРУГИЕ НЕУДАЧНЫЕ ДУШИ, КОТОРЫЕ ДОЛЖНЫ ИСПОЛЬЗОВАТЬ XML EXPLICIT:)

У меня есть следующий код, который выводит XML, включенный ниже. Проблема, с которой я сталкиваюсь, заключается в том, что в каждом из разделов request_status_update должен быть по одному разделу shipping_info и related_pin_pad, но вместо этого все они появляются только в последнем request_status_update.

С другой стороны, если кто-нибудь может указать мне на способ выполнения такого рода вложений с помощью XML PATH, это может быть проще, я тоже за это. Я просто не мог понять, как получить один раздел заголовка, но затем все еще несколько разделов request_status_update. XML PATH, кажется, ожидает, что вы всегда будете делать что-то построчно, а не иметь только заголовочный раздел.

Заранее спасибо за любую помощь в этом. Я знаю, что это длинный код такой же (извините за это)

SQL-код:

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: (включен только раздел request_status_update s, так как это проблемная область

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

ОЖИДАЕМЫЙ ВЫХОД:

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

Мне нужно было добавить этот порядок по пункту,

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

Кроме того, начиная с запроса для тега 4, мне нужно было убедиться, что RequestID действительно включен в результат вместо NULL, чтобы сортировка работала.

Я также собираюсь изучить использование XML PATH, который должен быть проще, чем XML EXPLICIT, но пока это работает.

Answer 1

ИСПРАВИТЬ:

Мне нужно было добавить этот заказ по пункту,

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

Кроме того, начиная с запроса для тега 4, мне нужно было убедиться, что RequestID действительно включен в результат вместо NULL, чтобы сортировка работала.

Я также собираюсь изучить использование XML PATH, который должен быть проще, чем XML EXPLICIT, но пока это работает.