Парсим XML в MS SQL

Один из вариантов разбора XML в Microsoft SQL. Вытащим из сообщения детальные записи (два списка), которые потом объединим по ключу и добавим поля из заголовка.

with q as (select 1 as submit_id, cast(
'<xsubmission>
  <global>
    <location>location1</location>
  </global>
  <global>
    <location_property>some text</location_property>
  </global>
  <repeat_labor>
    <labor_technician>technician1</labor_technician>
    <labor_total>10.0</labor_total>
  </repeat_labor>
  <repeat_labor>
    <labor_technician>technician2</labor_technician>
    <labor_total>5.0</labor_total>
  </repeat_labor>
  <repeat_expense>
    <expense_technician>technician1</expense_technician>
    <expense_total>23500.45</expense_total>
  </repeat_expense>
  <repeat_expense>
    <expense_technician>technician3</expense_technician>
    <expense_total>40670.00</expense_total>
  </repeat_expense>
</xsubmission>'
 as XML) submit_xml
)


, ql as --subquery for labors
(select q.submit_id
  , repeat.value('labor_technician[1]', 'varchar(250)') labor_technician
  , repeat.value('labor_total[1]', 'varchar(250)') labor_total
from q CROSS APPLY submit_xml.nodes('/xsubmission/repeat_labor') r(repeat)
)


, qe as --sub
(select q.submit_id
  , repeat.value('expense_technician[1]', 'varchar(250)') expense_technician
  , repeat.value('expense_total[1]', 'varchar(250)') expense_total
from q CROSS APPLY submit_xml.nodes('/xsubmission/repeat_expense') r(repeat)
)


, qle as -- join labors and expenses
(select isnull(ql.submit_id, qe.submit_id) submit_id
     , isnull(ql.labor_technician, qe.expense_technician) technician
     , ql.labor_total, qe.expense_total
from ql full join qe on (ql.submit_id = qe.submit_id and ql.labor_technician = qe.expense_technician)
)




select q.submit_id
  , q.submit_xml.value('(/xsubmission/global/location)[1]', 'varchar(250)') location
  , q.submit_xml.value('(/xsubmission/global/location_property)[1]', 'varchar(250)') location_property
  , qle.technician
  , qle.labor_total
  , qle.expense_total
from q left join qle on (q.submit_id = qle.submit_id)
;

Результат: