· 4 years ago · Apr 13, 2021, 06:40 AM
1CREATE TABLE IF NOT EXISTS xml_data
2(
3 __container_name VARCHAR,
4 __entity_name VARCHAR,
5 "_batchId" VARCHAR,
6 "_batchesLeft" VARCHAR,
7 "_batchUuid" VARCHAR,
8 "_dumpComplete" VARCHAR,
9 "_createdTime" VARCHAR,
10 "id" VARCHAR,
11 "name" VARCHAR,
12 "version" VARCHAR,
13 "type" VARCHAR,
14 "sportId" VARCHAR,
15 "venueId" VARCHAR,
16 "rootPartId" VARCHAR,
17 "statusId" VARCHAR,
18 "typeId" VARCHAR,
19 "entityId" VARCHAR,
20 "entityTypeId" VARCHAR,
21 "collectorId" VARCHAR,
22 "scoringUnitId" VARCHAR,
23 "eventId" VARCHAR,
24 "eventTypeId" VARCHAR,
25 "eventPartId" VARCHAR,
26 "eventActionId" VARCHAR,
27 "eventInfoTypeId" VARCHAR,
28 "eventActionTypeId" VARCHAR,
29 "eventActionDetailTypeId" VARCHAR,
30 "eventParticipantInfoTypeId" VARCHAR,
31 "eventParticipantInfoDetailTypeId" VARCHAR,
32 "parentId" VARCHAR,
33 "parentEventId" VARCHAR,
34 "parentParticipantId" VARCHAR,
35 "providerId" VARCHAR,
36 "providerEntityId" VARCHAR,
37 "providerEntityTypeId" VARCHAR,
38 "participantId" VARCHAR,
39 "participantTypeId" VARCHAR,
40 "participantRoleId" VARCHAR,
41 "fromParticipantId" VARCHAR,
42 "toParticipantId" VARCHAR,
43 "startTime" VARCHAR,
44 "endTime" VARCHAR,
45 "retirementTime" VARCHAR,
46 "lastUpdateTime" VARCHAR,
47 "lastCollectedTime" VARCHAR,
48 "locationId" VARCHAR,
49 "fromLocationId" VARCHAR,
50 "toLocationId" VARCHAR,
51 "firstName" VARCHAR,
52 "lastName" VARCHAR,
53 "shortName" VARCHAR,
54 "birthTime" VARCHAR,
55 "countryId" VARCHAR,
56 "language" VARCHAR,
57
58 "isEnabled" VARCHAR,
59 "isAvailable" VARCHAR,
60 "isDrawPossible" VARCHAR,
61 "isMale" VARCHAR,
62 "isPrimary" VARCHAR,
63 "isIndividual" VARCHAR,
64 "isBookmaker" VARCHAR,
65 "isBettingExchange" VARCHAR,
66 "isLiveOddsApproved" VARCHAR,
67 "isNewsSource" VARCHAR,
68 "paramBoolean1" VARCHAR,
69 "paramBoolean1Description" VARCHAR,
70 "paramFloat1" VARCHAR,
71 "paramFloat2" VARCHAR,
72 "paramFloat1Description" VARCHAR,
73 "paramFloat2Description" VARCHAR,
74 "paramString1" VARCHAR,
75 "paramString1Description" VARCHAR,
76 "paramString1PossibleValues" VARCHAR,
77 "paramParticipantId1" VARCHAR,
78 "paramParticipantId2" VARCHAR,
79 "paramParticipantId1Description" VARCHAR,
80 "paramParticipantId2Description" VARCHAR,
81 "paramParticipantIdsMustBeOrdered" VARCHAR,
82 "paramParticipantRoleId" VARCHAR,
83 "paramParticipantRoleIdDescription" VARCHAR,
84 "paramEventPartId1Description" VARCHAR,
85 "paramEventStatusId1Description" VARCHAR,
86 "paramScoringUnitId1Description" VARCHAR,
87 "paramTime1Description" VARCHAR,
88 "hasParamBoolean1" VARCHAR,
89 "hasParamFloat1" VARCHAR,
90 "hasParamFloat2" VARCHAR,
91 "hasParamString1" VARCHAR,
92 "hasParamTime1" VARCHAR,
93 "hasParamEventStatusId1" VARCHAR,
94 "hasParamEventPartId1" VARCHAR,
95 "hasParamParticipantId1" VARCHAR,
96 "hasParamParticipantId2" VARCHAR,
97 "hasParamParticipantRoleId" VARCHAR,
98 "hasParamScoringUnitId1" VARCHAR,
99 "hasName" VARCHAR,
100 "hasFirstName" VARCHAR,
101 "hasLastName" VARCHAR,
102 "hasIsMale" VARCHAR,
103 "hasBirthTime" VARCHAR,
104 "hasNationalityId" VARCHAR,
105 "hasRetirementTime" VARCHAR,
106
107 "logoURL" VARCHAR,
108 "urlTemplate" VARCHAR,
109 "orderNum" VARCHAR,
110 "channel" VARCHAR,
111 "streamingproviderId" VARCHAR,
112 "bettingCommissionVACs" VARCHAR,
113 "description" VARCHAR
114);
115
116CREATE OR REPLACE FUNCTION func_insert_xml(_xml_data VARCHAR)
117 RETURNS VOID AS
118$$
119BEGIN
120 WITH x AS (SELECT XMLPARSE(CONTENT _xml_data) AS source_xml)
121 INSERT
122 INTO xml_data
123 (
124 SELECT decoded.*
125 FROM x,
126 XMLTABLE('//sdql/*/entities/*' PASSING source_xml COLUMNS
127 __container_name VARCHAR PATH 'name(../..)',
128 __entity_name VARCHAR PATH 'name()',
129 "_batchId" VARCHAR PATH '../../@batchId',
130 "_batchesLeft" VARCHAR PATH '../../@batchesLeft',
131 "_batchUuid" VARCHAR PATH '../../@batchUuid',
132 "_dumpComplete" VARCHAR PATH '../../@dumpComplete',
133 "_createdTime" VARCHAR PATH '../../@createdTime',
134 "id" VARCHAR PATH '@id',
135 "name" VARCHAR PATH '@name',
136 "version" VARCHAR PATH '@version',
137 "type" VARCHAR PATH '@type',
138 "sportId" VARCHAR PATH '@sportId',
139 "venueId" VARCHAR PATH '@venueId',
140 "rootPartId" VARCHAR PATH '@rootPartId',
141 "statusId" VARCHAR PATH '@statusId',
142 "typeId" VARCHAR PATH '@typeId',
143 "entityId" VARCHAR PATH '@entityId',
144 "entityTypeId" VARCHAR PATH '@entityTypeId',
145 "collectorId" VARCHAR PATH '@collectorId',
146 "scoringUnitId" VARCHAR PATH '@scoringUnitId',
147 "eventId" VARCHAR PATH '@eventId',
148 "eventTypeId" VARCHAR PATH '@eventTypeId',
149 "eventPartId" VARCHAR PATH '@eventPartId',
150 "eventActionId" VARCHAR PATH '@eventActionId',
151 "eventInfoTypeId" VARCHAR PATH '@eventInfoTypeId',
152 "eventActionTypeId" VARCHAR PATH '@eventActionTypeId',
153 "eventActionDetailTypeId" VARCHAR PATH '@eventActionDetailTypeId',
154 "eventParticipantInfoTypeId" VARCHAR PATH '@eventParticipantInfoTypeId',
155 "eventParticipantInfoDetailTypeId" VARCHAR PATH '@eventParticipantInfoDetailTypeId',
156 "parentId" VARCHAR PATH '@parentId',
157 "parentEventId" VARCHAR PATH '@parentEventId',
158 "parentParticipantId" VARCHAR PATH '@parentParticipantId',
159 "providerId" VARCHAR PATH '@providerId',
160 "providerEntityId" VARCHAR PATH '@providerEntityId',
161 "providerEntityTypeId" VARCHAR PATH '@providerEntityTypeId',
162 "participantId" VARCHAR PATH '@participantId',
163 "participantTypeId" VARCHAR PATH '@participantTypeId',
164 "participantRoleId" VARCHAR PATH '@participantRoleId',
165 "fromParticipantId" VARCHAR PATH '@fromParticipantId',
166 "toParticipantId" VARCHAR PATH '@toParticipantId',
167 "startTime" VARCHAR PATH '@startTime',
168 "endTime" VARCHAR PATH '@endTime',
169 "retirementTime" VARCHAR PATH '@retirementTime',
170 "lastUpdateTime" VARCHAR PATH '@lastUpdateTime',
171 "lastCollectedTime" VARCHAR PATH '@lastCollectedTime',
172 "locationId" VARCHAR PATH '@locationId',
173 "fromLocationId" VARCHAR PATH '@fromLocationId',
174 "toLocationId" VARCHAR PATH '@toLocationId',
175 "firstName" VARCHAR PATH '@firstName',
176 "lastName" VARCHAR PATH '@lastName',
177 "shortName" VARCHAR PATH '@shortName',
178 "birthTime" VARCHAR PATH '@birthTime',
179 "countryId" VARCHAR PATH '@countryId',
180 "language" VARCHAR PATH '@language',
181
182 "isEnabled" VARCHAR PATH '@isEnabled',
183 "isAvailable" VARCHAR PATH '@isAvailable',
184 "isDrawPossible" VARCHAR PATH '@isDrawPossible',
185 "isMale" VARCHAR PATH '@isMale',
186 "isPrimary" VARCHAR PATH '@isPrimary',
187 "isIndividual" VARCHAR PATH '@isIndividual',
188 "isBookmaker" VARCHAR PATH '@isBookmaker',
189 "isBettingExchange" VARCHAR PATH '@isBettingExchange',
190 "isLiveOddsApproved" VARCHAR PATH '@isLiveOddsApproved',
191 "isNewsSource" VARCHAR PATH '@isNewsSource',
192 "paramBoolean1" VARCHAR PATH '@paramBoolean1',
193 "paramBoolean1Description" VARCHAR PATH '@paramBoolean1Description',
194 "paramFloat1" VARCHAR PATH '@paramFloat1',
195 "paramFloat2" VARCHAR PATH '@paramFloat2',
196 "paramFloat1Description" VARCHAR PATH '@paramFloat1Description',
197 "paramFloat2Description" VARCHAR PATH '@paramFloat2Description',
198 "paramString1" VARCHAR PATH '@paramString1',
199 "paramString1Description" VARCHAR PATH '@paramString1Description',
200 "paramString1PossibleValues" VARCHAR PATH '@paramString1PossibleValues',
201 "paramParticipantId1" VARCHAR PATH '@paramParticipantId1',
202 "paramParticipantId2" VARCHAR PATH '@paramParticipantId2',
203 "paramParticipantId1Description" VARCHAR PATH '@paramParticipantId1Description',
204 "paramParticipantId2Description" VARCHAR PATH '@paramParticipantId2Description',
205 "paramParticipantIdsMustBeOrdered" VARCHAR PATH '@paramParticipantIdsMustBeOrdered',
206 "paramParticipantRoleId" VARCHAR PATH '@paramParticipantRoleId',
207 "paramParticipantRoleIdDescription" VARCHAR PATH '@paramParticipantRoleIdDescription',
208 "paramEventPartId1Description" VARCHAR PATH '@paramEventPartId1Description',
209 "paramEventStatusId1Description" VARCHAR PATH '@paramEventStatusId1Description',
210 "paramScoringUnitId1Description" VARCHAR PATH '@paramScoringUnitId1Description',
211 "paramTime1Description" VARCHAR PATH '@paramTime1Description',
212 "hasParamBoolean1" VARCHAR PATH '@hasParamBoolean1',
213 "hasParamFloat1" VARCHAR PATH '@hasParamFloat1',
214 "hasParamFloat2" VARCHAR PATH '@hasParamFloat2',
215 "hasParamString1" VARCHAR PATH '@hasParamString1',
216 "hasParamTime1" VARCHAR PATH '@hasParamTime1',
217 "hasParamEventStatusId1" VARCHAR PATH '@hasParamEventStatusId1',
218 "hasParamEventPartId1" VARCHAR PATH '@hasParamEventPartId1',
219 "hasParamParticipantId1" VARCHAR PATH '@hasParamParticipantId1',
220 "hasParamParticipantId2" VARCHAR PATH '@hasParamParticipantId2',
221 "hasParamParticipantRoleId" VARCHAR PATH '@hasParamParticipantRoleId',
222 "hasParamScoringUnitId1" VARCHAR PATH '@hasParamScoringUnitId1',
223 "hasName" VARCHAR PATH '@hasName',
224 "hasFirstName" VARCHAR PATH '@hasFirstName',
225 "hasLastName" VARCHAR PATH '@hasLastName',
226 "hasIsMale" VARCHAR PATH '@hasIsMale',
227 "hasBirthTime" VARCHAR PATH '@hasBirthTime',
228 "hasNationalityId" VARCHAR PATH '@hasNationalityId',
229 "hasRetirementTime" VARCHAR PATH '@hasRetirementTime',
230
231 "logoURL" VARCHAR PATH '@logoURL',
232 "urlTemplate" VARCHAR PATH '@urlTemplate',
233 "orderNum" VARCHAR PATH '@orderNum',
234 "channel" VARCHAR PATH '@channel',
235 "streamingproviderId" VARCHAR PATH '@streamingproviderId',
236 "bettingCommissionVACs" VARCHAR PATH '@bettingCommissionVACs',
237 "description" VARCHAR PATH '@description') AS decoded
238 );
239END;
240$$ LANGUAGE plpgsql VOLATILE;
241
242SELECT func_insert_xml('<?xml version="1.0" encoding="UTF-8"?>
243 <sdql>
244 <InitialData batchId="180" batchesLeft="1595" dumpComplete="false">
245 <entities>
246 <EventParticipantRelation id="129270956309926400" version="0" eventId="113639288013475840" eventPartId="1" participantId="120828565095763968" participantRoleId="3" parentParticipantId="1439"/>
247 <EventParticipantRelation id="124710927690944512" version="0" eventId="121544264018612224" eventPartId="1" participantId="94921131137433600" participantRoleId="4"/>
248 <EventParticipantRelation id="133982001146417152" version="0" eventId="133941089128206336" eventPartId="1" participantId="6850527117574144" participantRoleId="3" parentParticipantId="570"/>
249 <Lol id="133982001146417152" version="0" eventId="133941089128206336" eventPartId="1" participantId="6850527117574144" participantRoleId="3" parentParticipantId="570"/>
250 </entities>
251 </InitialData>
252 <Updates batchId="180" batchesLeft="1595" dumpComplete="false">
253 <entities>
254 <EventUpdate1 id="129270956309926400" version="0" eventId="113639288013475840" eventPartId="1" participantId="120828565095763968" participantRoleId="3" parentParticipantId="1439"/>
255 <EventUpdate1 id="124710927690944512" version="0" eventId="121544264018612224" eventPartId="1" participantId="94921131137433600" participantRoleId="4"/>
256 <EventUpdate1 id="133982001146417152" version="0" eventId="133941089128206336" eventPartId="1" participantId="6850527117574144" participantRoleId="3" parentParticipantId="570"/>
257 <SomeAnotherUpdate1 id="133982001146417152" version="0" eventId="133941089128206336" eventPartId="1" participantId="6850527117574144" participantRoleId="3" parentParticipantId="570"/>
258 </entities>
259 </Updates>
260 </sdql>');