4

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 ('
<order>
    <trans DTrans="20170102" HTrans="10:52"></trans>
    <head Id="552665566"></head>
    <lin headId="552665566">
        <dLin Id="00001" CoArt="1111111" CoArtCust="05220001" NameArt="Product1" dateVal="20161115" />
        <acum DCalAcm="20170101" DIniAcm="20161115"><qtyAcm Qty="1101163.00" /></acum>
        <lastOrd Id="95767" QtyLastOrd="12000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="20000.00"/><dIni Date="20170109"/><dEnd Date="20170112"/></pLin>
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170113"/><dEnd Date="20170116"/></pLin>
    </lin>
    <lin headId="552665566">
        <dLin Id="00002" CoArt="2222222" CoArtCust="05269958" NameArt="Product2" dateVal="20161101" />
        <acum DCalAcm="20170101" DIniAcm="20161101"><qtyAcm Qty="552652.00" /></acum>
       <lastOrd Id="49956" QtyLastOrd="5000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="2200.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="3000.00"/><dIni Date="20170109"/><dEnd Date="20170116"/></pLin>
    </lin>
</order>');

As you can see for each <lin> section you can find an unknown number of <pLin> 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 <lin> 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 <pLin> 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

Improve this question

1 Answer 1

Reset to default
8

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.