SQL Server XML query : SO 44690465, Sql Server

Language: Layout:

DECLARE @xml XML = '<Root>
  <Header>
    <refNo>S350282535-100060</refNo>
    <doNumber>0</doNumber>
  </Header>
  <Header>
    <refNo>S350282535-100061</refNo>
    <doNumber>1</doNumber>
  </Header>
  <Detail>
    <item>114495</item>
    <qty>2</qty>
    <uom>PC</uom>
    <refNo>S350282535-100060</refNo>
    <remarks>-</remarks>
    <reason>-</reason>
 </Detail>
 <Detail>
    <item>114496</item>
    <qty>2</qty>
    <uom>PC</uom>
    <refNo>S350282535-100061</refNo>
    <remarks>-</remarks>
    <reason>-</reason>
 </Detail>
 <Detail>
    <item>114497</item>
    <qty>2</qty>
    <uom>PC</uom>
    <refNo>S350282535-100061</refNo>
    <remarks>-</remarks>
    <reason>-</reason>
 </Detail>
</Root>'

;WITH Header(refNo, doNumber)
AS
(
    SELECT 
        T.C.value('refNo[1]', 'varchar(100)')
        ,T.C.value('doNumber[1]', 'int')
    FROM @xml.nodes('/Root/Header') AS T(C)
)

SELECT 
    Header.*
    ,T.C.value('item[1]', 'varchar(100)') 'item'
    ,T.C.value('qty[1]', 'int') 'qty'
    ,T.C.value('uom[1]', 'varchar(100)') 'uom'
FROM @xml.nodes('/Root/Detail') AS T(C)
INNER JOIN Header on T.C.value('refNo[1]', 'varchar(100)') = refNo

View schema
Execution time: 0,05 sec, rows selected: 4, rows affected: 0, absolute service time: 0,2 sec