Sql-Server
查詢 XML 嵌套節點
我需要將每日 XML 文件集成到我的系統中。
XML 文件的架構類似於：
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>');正如您所看到的，對於每個
<lin>部分，您都可以找到未知數量的<pLin>行。到目前為止，我正在使用 C# 和 .Net 控制台應用程序完成這項工作，但我想使用 SQL 來完成這項工作。到目前為止，我可以
<lin>使用下一個查詢獲得一組標籤：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);這會產生下一個結果：
Id | CoArt | CoArtCust | lastOrderId | QtyLastOrd -: | :------ | :-------- | :---------- | :--------- 1 | 1111111 | 05220001 | 95767 | 12000.00 2 | 2222222 | 05269958 | 49956 | 5000.00
<pLin>但我應該添加與標籤對應的嵌套行並產生下一個結果：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我在*這裡*設置了一個dbfiddle
您可以添加一個附加項
CROSS APPLY以按您需要的方式拆分行：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);我不得不猜測 DINi 的數據類型，按要求正確。