/
Load xml file using BULK.txt
134 lines (116 loc) · 5.96 KB
/
Load xml file using BULK.txt
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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
XML SAMPLE:
-----------
<ROWSET>
<DESCRIPTION1>₪ כל הסכומים במיליוני</DESCRIPTION1>
<DESCRIPTION2>כל התשואות הינן נומינליות ברוטו</DESCRIPTION2>
<Row>
<ID>101</ID>
<SHM_KUPA>הראל אג"ח עד 25% מניות </SHM_KUPA>
<TAARICH_HAFAKAT_HADOCH>30/01/2016</TAARICH_HAFAKAT_HADOCH>
<SUG_KUPA>תגמולים ואישית לפיצויים</SUG_KUPA>
<SHM_TAAGID_SHOLET>הראל השקעות בביטוח ושירותים פיננסיים בע"מ</SHM_TAAGID_SHOLET>
<SUG_TAAGID_SHOLET>קבוצות ביטוח</SUG_TAAGID_SHOLET>
<SHM_HEVRA_MENAHELET>הראל גמל והשתלמות בע"מ</SHM_HEVRA_MENAHELET>
<TAARICH_HAKAMA>01/04/1979</TAARICH_HAKAMA>
<UCHLUSIYAT_YAAD>כלל האוכלוסיה</UCHLUSIYAT_YAAD>
<HITMAHUT_RASHIT>אג"ח</HITMAHUT_RASHIT>
<HITMAHUT_MISHNIT>אג"ח</HITMAHUT_MISHNIT>
<TKUFAT_DIVUACH>201511</TKUFAT_DIVUACH>
<HAFKADOT_LLO_HAAVAROT>15.93</HAFKADOT_LLO_HAAVAROT>
<MSHICHOT_LLO_HAAVAROT>49.22</MSHICHOT_LLO_HAAVAROT>
<HAAVAROT_BEIN_HAKUPOT>-6.99</HAAVAROT_BEIN_HAKUPOT>
<TZVIRA_NETO>-40.28</TZVIRA_NETO>
<YITRAT_NCHASIM_LSOF_TKUFA>12061.20</YITRAT_NCHASIM_LSOF_TKUFA>
<SHIUR_DMEI_NIHUL_AHARON>0.77</SHIUR_DMEI_NIHUL_AHARON>
<SHIUR_D_NIHUL_AHARON_HAFKADOT>1.04</SHIUR_D_NIHUL_AHARON_HAFKADOT>
<TSUA_NOMINALIT_BRUTO_HODSHIT>-0.07</TSUA_NOMINALIT_BRUTO_HODSHIT>
<TSUA_MITZT_MI_THILAT_SHANA>3.39</TSUA_MITZT_MI_THILAT_SHANA>
<TSUA_MEMUZAAT_36_HODASHIM>0.50</TSUA_MEMUZAAT_36_HODASHIM>
<TSUA_MEMUZAAT_60_HODASHIM>0.45</TSUA_MEMUZAAT_60_HODASHIM>
<TSUA_MITZTABERET_36_HODASHIM>19.86</TSUA_MITZTABERET_36_HODASHIM>
<TSUA_MITZTABERET_60_HODASHIM>30.83</TSUA_MITZTABERET_60_HODASHIM>
<TSUA_SHNATIT_MEMUZAAT_3_SHANIM>6.22</TSUA_SHNATIT_MEMUZAAT_3_SHANIM>
<TSUA_SHNATIT_MEMUZAAT_5_SHANIM>5.52</TSUA_SHNATIT_MEMUZAAT_5_SHANIM>
<STIAT_TEKEN_36_HODASHIM>1.20</STIAT_TEKEN_36_HODASHIM>
<STIAT_TEKEN_60_HODASHIM>1.41</STIAT_TEKEN_60_HODASHIM>
<MATZAV_DIVUACH>דווח</MATZAV_DIVUACH>
<ALPHA_SHNATIT>2.04</ALPHA_SHNATIT>
<SHARP_ANAF>0.41</SHARP_ANAF>
<SHARP_KOL_HAKUPOT>0.52</SHARP_KOL_HAKUPOT>
<SHARP_RIBIT_HASRAT_SIKUN>0.79</SHARP_RIBIT_HASRAT_SIKUN>
<YAHAS_NEZILUT>77.80</YAHAS_NEZILUT>
<MISPAR_KUPA_AV>0</MISPAR_KUPA_AV>
<NUM_HEVRA>512205204</NUM_HEVRA>
</Row>
</ROWSET>
READ TYPES:
-----------
SINGLE_BLOB // reads as varbinary(max)
SINGLE_CLOB // reads as varchar(max)
SINGLE_NCLOB // reads as nvarchar(max)
READ FILE INTO AN XML VARIABLE:
-------------------------------
declare @xDoc XML
SET @xDoc =
(
SELECT *
FROM OPENROWSET(BULK 'C:\Test.xml', SINGLE_BLOB) AS x
)
READ FILE INTO A NEW TABLE WITH MAPPING:
----------------------------------------
-- OPENROWSET (STEP 1) --
declare @result xml
set @result = (SELECT * FROM OPENROWSET (BULK 'C:\Test.xml', SINGLE_NCLOB) AS x)
//select @result
-- PREPARE DOCUMENT (STEP 2) --
declare @hDoc AS INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @result
-- OPENXML (STEP 3) --
select *
from OPENXML(@hDoc, '/ROWSET/Row')
WITH(
Id nvarchar(200) 'ID',
Name nvarchar(200) 'SHM_KUPA',
ProduceDate nvarchar(200) 'TAARICH_HAFAKAT_HADOCH',
FundType nvarchar(200) 'SUG_KUPA',
CorporationName nvarchar(200) 'SHM_TAAGID_SHOLET',
CorporationType nvarchar(200) 'SUG_TAAGID_SHOLET',
ManagmentCompanyName nvarchar(200) 'SHM_HEVRA_MENAHELET',
EstablishedDate nvarchar(200) 'TAARICH_HAKAMA',
Population nvarchar(200) 'UCHLUSIYAT_YAAD',
PrimarySpeciality nvarchar(200) 'HITMAHUT_RASHIT',
SecondarySpeciality nvarchar(200) 'HITMAHUT_MISHNIT',
PeriodReporting nvarchar(200) 'TKUFAT_DIVUACH',
Deposits nvarchar(200) 'HAFKADOT_LLO_HAAVAROT',
Withdrawals nvarchar(200) 'MSHICHOT_LLO_HAAVAROT',
Transfers nvarchar(200) 'HAAVAROT_BEIN_HAKUPOT',
TotalNeto nvarchar(200) 'TZVIRA_NETO',
PeriodBalance nvarchar(200) 'YITRAT_NCHASIM_LSOF_TKUFA',
ManagmentFee nvarchar(200) 'SHIUR_DMEI_NIHUL_AHARON',
DepositsManagmentFee nvarchar(200) 'SHIUR_D_NIHUL_AHARON_HAFKADOT',
MonthlyYieldBruto nvarchar(200) 'TSUA_NOMINALIT_BRUTO_HODSHIT',
TotalYearlyYield nvarchar(200) 'TSUA_MITZT_MI_THILAT_SHANA',
AverageYield36Months nvarchar(200) 'TSUA_MEMUZAAT_36_HODASHIM',
AverageYield60Months nvarchar(200) 'TSUA_MEMUZAAT_60_HODASHIM',
TotalYield36Months nvarchar(200) 'TSUA_MITZTABERET_36_HODASHIM',
TotalYield60Months nvarchar(200) 'TSUA_MITZTABERET_60_HODASHIM',
AverageYearlyYield3Years nvarchar(200) 'TSUA_SHNATIT_MEMUZAAT_3_SHANIM',
AverageYearlyYield5Years nvarchar(200) 'TSUA_SHNATIT_MEMUZAAT_5_SHANIM',
Anomaly36Months nvarchar(200) 'STIAT_TEKEN_36_HODASHIM',
Anomaly60Months nvarchar(200) 'STIAT_TEKEN_60_HODASHIM',
ReportingStatus nvarchar(200) 'MATZAV_DIVUACH',
YearlyAlpha nvarchar(200) 'ALPHA_SHNATIT',
IndustrySharp nvarchar(200) 'SHARP_ANAF',
TotalSharp nvarchar(200) 'SHARP_KOL_HAKUPOT',
RisklessInterestSharp nvarchar(200) 'SHARP_RIBIT_HASRAT_SIKUN',
UtilizationRatio nvarchar(200) 'YAHAS_NEZILUT',
FundNumber nvarchar(200) 'MISPAR_KUPA_AV',
CompanyNumber nvarchar(200) 'NUM_HEVRA'
)
-- REMOVE DOCUMENT (STEP 4) --
EXEC sp_xml_removedocument @hDoc
FLWOR
-----
select xTbl.xRows.query('data(ID/.)'),
xTbl.xRows.query('data(SHM_KUPA/.)')
from @result.nodes('/ROWSET/Row') as xTbl(xRows)