Querying XML nested nodes I need to integrate a daily XML file into my system.

The XML file has an schema similar to this:

create table txml(id int identity, data xml);
insert into txml (data) values ('
&lt;order&gt;
    &lt;trans DTrans="20170102" HTrans="10:52"&gt;&lt;/trans&gt;
    &lt;head Id="552665566"&gt;&lt;/head&gt;
    &lt;lin headId="552665566"&gt;
        &lt;dLin Id="00001" CoArt="1111111" CoArtCust="05220001" NameArt="Product1" dateVal="20161115" /&gt;
        &lt;acum DCalAcm="20170101" DIniAcm="20161115"&gt;&lt;qtyAcm Qty="1101163.00" /&gt;&lt;/acum&gt;
        &lt;lastOrd Id="95767" QtyLastOrd="12000.00" DLastOrd="20170101" /&gt;
        &lt;pLin LType="4"&gt;&lt;uni Qty="24000.00"/&gt;&lt;dIni Date="20170104"/&gt;&lt;dEnd Date="20170108"/&gt;&lt;/pLin&gt;
        &lt;pLin LType="4"&gt;&lt;uni Qty="20000.00"/&gt;&lt;dIni Date="20170109"/&gt;&lt;dEnd Date="20170112"/&gt;&lt;/pLin&gt;
        &lt;pLin LType="4"&gt;&lt;uni Qty="24000.00"/&gt;&lt;dIni Date="20170113"/&gt;&lt;dEnd Date="20170116"/&gt;&lt;/pLin&gt;
    &lt;/lin&gt;
    &lt;lin headId="552665566"&gt;
        &lt;dLin Id="00002" CoArt="2222222" CoArtCust="05269958" NameArt="Product2" dateVal="20161101" /&gt;
        &lt;acum DCalAcm="20170101" DIniAcm="20161101"&gt;&lt;qtyAcm Qty="552652.00" /&gt;&lt;/acum&gt;
       &lt;lastOrd Id="49956" QtyLastOrd="5000.00" DLastOrd="20170101" /&gt;
        &lt;pLin LType="4"&gt;&lt;uni Qty="2200.00"/&gt;&lt;dIni Date="20170104"/&gt;&lt;dEnd Date="20170108"/&gt;&lt;/pLin&gt;
        &lt;pLin LType="4"&gt;&lt;uni Qty="3000.00"/&gt;&lt;dIni Date="20170109"/&gt;&lt;dEnd Date="20170116"/&gt;&lt;/pLin&gt;
    &lt;/lin&gt;
&lt;/order&gt;');


As you can see for each &lt;lin&gt; section you can find an unknown number of &lt;pLin&gt; lines. By now I'm doing this job using C# and a .Net console app, but I'd like to do it by using SQL.

Till now I can get a rowset of &lt;lin&gt; tags using the next query:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]',  'decimal(18,2)') QtyLastOrd
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin);


That produces next result:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd
-: | :------ | :-------- | :---------- | :---------
 1 | 1111111 | 05220001  | 95767       | 12000.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00   


But I should add nested lines corresponding to &lt;pLin&gt; tags and produce the next result:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd | DIni       | DEnd       | NextQty  
-: | :------ | :-------- | :---------- | :--------- | ---------- | ---------- | ---------
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 04/01/2017 | 08/01/2017 | 24000.00  
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 09/01/2017 | 12/01/2017 | 20000.00  
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 13/01/2017 | 16/01/2017 | 24000.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 04/01/2017 | 08/01/2017 | 2200.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 09/01/2017 | 16/01/2017 | 3000.00  


I've set up a dbfiddle here
You can add an additional CROSS APPLY to break out the rows in the way you require:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]', 'decimal(18,2)') QtyLastOrd,
        T3.lin.value('(./dIni/@Date)[1]', 'date') DIni,
        T3.lin.value('(./dEnd/@Date)[1]', 'date') DEnd,
        T3.lin.value('(./uni/@Qty)[1]', 'decimal(7, 2)') DIni
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin)
CROSS APPLY T2.lin.nodes('pLin') as T3(lin);


I had to guess the datatype for DIni, correct as required.