-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathread-xml-codes.sql
53 lines (44 loc) · 3.27 KB
/
read-xml-codes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
DECLARE @Xml TABLE ( Data XML );
INSERT INTO @Xml ( Data ) SELECT * FROM OPENROWSET(BULK N'C:\xmls\SALES_DISPATCHES.xml', SINGLE_BLOB) O;
SELECT DISPATCH.[Row].value('TYPE[1]', 'NVARCHAR(50)') AS [TYPE] ,
DISPATCH.[Row].value('NUMBER[1]', 'NVARCHAR(50)') AS NUMBER ,
DISPATCH.[Row].value('DATE[1]', 'NVARCHAR(50)') AS DATE ,
DISPATCH.[Row].value('DOC_NUMBER[1]', 'NVARCHAR(50)') AS DOC_NUMBER ,
DISPATCH.[Row].value('ARP_CODE[1]', 'NVARCHAR(50)') AS ARP_CODE ,
[TRANSACTION].[Row].value('TYPE[1]', 'NVARCHAR(50)') AS [T_TYPE] ,
[TRANSACTION].[Row].value('MASTER_CODE[1]', 'NVARCHAR(50)') AS MASTER_CODE ,
[TRANSACTION].[Row].value('QUANTITY[1]', 'NVARCHAR(50)') AS QUANTITY ,
[TRANSACTION].[Row].value('UNIT_CODE[1]', 'NVARCHAR(50)') AS UNIT_CODE ,
[TRANSACTION].[Row].value('UNIT_CONV1[1]', 'NVARCHAR(50)') AS UNIT_CONV1 ,
[TRANSACTION].[Row].value('VAT_INCLUDED[1]', 'NVARCHAR(50)') AS VAT_INCLUDED ,
[TRANSACTION].[Row].value('VAT_RATE[1]', 'NVARCHAR(50)') AS VAT_RATE
FROM @Xml X
CROSS APPLY X.Data.nodes('/SALES_DISPATCHES/DISPATCH') DISPATCH ( [Row] )
CROSS APPLY DISPATCH.[Row].nodes('TRANSACTIONS/TRANSACTION') [TRANSACTION] ( [Row] )
--SALES_DISPATCHES (root node)
--SALES_DISPATCHES > DISPATCH (child node)
--SALES_DISPATCHES > DISPATCH > TRANSACTIONS (child node)
--SALES_DISPATCHES > DISPATCH > TRANSACTIONS > TRANSACTION (child node)
--DISPATCH ile TRANSACTION arasında bire çok bağlantı vardır.
--DISPATCH = İrsaliye
--TRANSACTION = İrsaliye Detayları (Kalemleri)
--Örnek Çıktısı: (Datalar orjinali temsil etmeyecek şekilde aynı benzerlikte değiştirilmiştir.)
--TYPE NUMBER DATE DOC_NUMBER ARP_CODE
--99 DENEMETEST000234 24.09.2021 236570015306 9017897 0 sdfsdfsdfwerwerr 4.000 ADET 1 1 8
--99 DENEMETEST000234 24.09.2021 236570015306 9017897 0 wer5345365344545 3.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 2342342342344444 1.000 ADET 1 1 18
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 5454545454545454 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 sdfsdfsdfwerwerr 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 98w7er98w7er787r 2.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 98w7er98w7er787r 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 wer5345365344545 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 2342342342342344 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 wer5345365344545 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 2342342342344444 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 5454545454545454 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 2342342342342344 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 sdfsdfsdfwerwerr 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 2342342342342344 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 sdfsdfsdfwerwerr 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 2342342342344444 1.000 ADET 1 1 8
--99 DENEMETEST000233 24.09.2021 236570015307 9019000 0 wer5345365344545 1.000 ADET 1 1 8