Créer un fichier XML avec FOR XML EXPLICIT

**Oluha** · 25/11/2013, 11h18

Bonjour,

J'essaye de créer un fichier XML dans SQL Server avec la clause FOR XML EXPLICIT.

Le fichier final doit ressembler à ça :

Code XML :

Sélectionner tout - Visualiser dans une fenêtre à part

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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd">
<CstmrDrctDbtInitn>
	<GrpHdr>
		<MsgId>PREL/2012-10-15T18:37:39/1</MsgId>
		<CreDtTm>2012-10-15T18:37:39</CreDtTm>
		<NbOfTxs>19</NbOfTxs>
		<CtrlSum>33980.35</CtrlSum>
		<InitgPty>
			<Nm>SOCIETE TEST VIRCOM</Nm>
		</InitgPty>
	</GrpHdr>
 
	<PmtInf>
		<PmtInfId>REMISE 50/100</PmtInfId>
		<PmtMtd>DD</PmtMtd>
		<BtchBookg>true</BtchBookg>
		<NbOfTxs>2</NbOfTxs>
		<CtrlSum>4380.05</CtrlSum>
		<PmtTpInf>
			<SvcLvl>
				<Cd>SEPA</Cd>
			</SvcLvl>
			<LclInstrm>
				<Cd>CORE</Cd>
			</LclInstrm>
			<SeqTp>OOFF</SeqTp>
		</PmtTpInf>
		<ReqdColltnDt>2012-10-22</ReqdColltnDt>
		<Cdtr>
			<Nm>SOCIETE TEST VIRCOM</Nm>
		</Cdtr>
		<CdtrAcct>
			<Id>
				<IBAN>FR7630087331820000000000113</IBAN>
			</Id>
		</CdtrAcct>
		<CdtrAgt>
			<FinInstnId>
				<BIC>CMCIFRPA</BIC>
			</FinInstnId>
		</CdtrAgt>
		<ChrgBr>SLEV</ChrgBr>
		<CdtrSchmeId>
			<Id>
				<PrvtId>
					<Othr>
						<Id>FR34ZZZZZZZZZ</Id>
						<SchmeNm>
							<Prtry>SEPA</Prtry>
						</SchmeNm>
					</Othr>
				</PrvtId>
			</Id>
		</CdtrSchmeId>
 
		<DrctDbtTxInf>
			<PmtId>
				<InstrId>PRELSDD/2012-10-15/1/1</InstrId>
				<EndToEndId>REF OPE 5635</EndToEndId>
			</PmtId>
			<InstdAmt Ccy="EUR">1100.05</InstdAmt>
			<DrctDbtTx>
				<MndtRltdInf>
					<MndtId>++MANDAT 145/10</MndtId>
					<DtOfSgntr>2012-03-20</DtOfSgntr>
					<AmdmntInd>false</AmdmntInd>
				</MndtRltdInf>
			</DrctDbtTx>
			<DbtrAgt>
				<FinInstnId>
					<BIC>CCBPFRPPDJN</BIC>
				</FinInstnId>
			</DbtrAgt>
			<Dbtr>
				<Nm>CABINET AAAA</Nm>
			</Dbtr>
			<DbtrAcct>
				<Id>
					<IBAN>FR7610807000030000000001148</IBAN>
				</Id>
			</DbtrAcct>
			<RmtInf>
				<Ustrd>REF E2E 555</Ustrd>
			</RmtInf>
		</DrctDbtTxInf>
 
		<DrctDbtTxInf>
			<PmtId>
				<InstrId>PRELSDD/2012-10-15/1/2</InstrId>
				<EndToEndId>REF OPE 5636</EndToEndId>
			</PmtId>
			<InstdAmt Ccy="EUR">3280.00</InstdAmt>
			<DrctDbtTx>
				<MndtRltdInf>
					<MndtId>++MANDAT 145/11</MndtId>
					<DtOfSgntr>2009-05-20</DtOfSgntr>
					<AmdmntInd>false</AmdmntInd>
				</MndtRltdInf>
			</DrctDbtTx>
			<DbtrAgt>
				<FinInstnId>
					<BIC>CCBPFRPPDJN</BIC>
				</FinInstnId>
			</DbtrAgt>
			<Dbtr>
				<Nm>CABINET BBBB</Nm>
			</Dbtr>
			<DbtrAcct>
				<Id>
					<IBAN>FR7610807000440000000001115</IBAN>
				</Id>
			</DbtrAcct>
			<RmtInf>
				<Ustrd>REF E2E557</Ustrd>
			</RmtInf>
		</DrctDbtTxInf>
 
	</PmtInf>
 
	<PmtInf>
		<PmtInfId>REMISE 50/100</PmtInfId>
		<PmtMtd>DD</PmtMtd>
		<BtchBookg>true</BtchBookg>
		<NbOfTxs>3</NbOfTxs>
		<CtrlSum>5480.05</CtrlSum>
		<PmtTpInf>
			<SvcLvl>
				<Cd>SEPA</Cd>
			</SvcLvl>
			<LclInstrm>
				<Cd>B2B</Cd>
			</LclInstrm>
			<SeqTp>OOFF</SeqTp>
		</PmtTpInf>
		<ReqdColltnDt>2012-10-17</ReqdColltnDt>
		<Cdtr>
			<Nm>SOCIETE TEST VIRCOM</Nm>
		</Cdtr>
		<CdtrAcct>
			<Id>
				<IBAN>FR7630087331820000000000113</IBAN>
			</Id>
		</CdtrAcct>
		<CdtrAgt>
			<FinInstnId>
				<BIC>CMCIFRPA</BIC>
			</FinInstnId>
		</CdtrAgt>
		<ChrgBr>SLEV</ChrgBr>
		<CdtrSchmeId>
			<Id>
				<PrvtId>
					<Othr>
						<Id>FR34ZZZZZZZZZ</Id>
						<SchmeNm>
							<Prtry>SEPA</Prtry>
						</SchmeNm>
					</Othr>
				</PrvtId>
			</Id>
		</CdtrSchmeId>
 
		<DrctDbtTxInf>
			<PmtId>
				<InstrId>PRELSDD/2012-10-15/1/1</InstrId>
				<EndToEndId>REF OPE 5638</EndToEndId>
			</PmtId>
			<InstdAmt Ccy="EUR">1100.00</InstdAmt>
			<DrctDbtTx>
				<MndtRltdInf>
					<MndtId>++MANDAT 145/12</MndtId>
					<DtOfSgntr>2009-05-20</DtOfSgntr>
					<AmdmntInd>false</AmdmntInd>
				</MndtRltdInf>
			</DrctDbtTx>
			<DbtrAgt>
				<FinInstnId>
					<BIC>CMCIFR2A</BIC>
				</FinInstnId>
			</DbtrAgt>
			<Dbtr>
				<Nm>CABINET CCCC</Nm>
			</Dbtr>
			<DbtrAcct>
				<Id>
					<IBAN>FR7610278070610000000001141</IBAN>
				</Id>
			</DbtrAcct>
			<RmtInf>
				<Ustrd>REF E2E 558</Ustrd>
			</RmtInf>
		</DrctDbtTxInf>
 
		<DrctDbtTxInf>
			<PmtId>
				<InstrId>PRELSDD/2012-10-15/1/2</InstrId>
				<EndToEndId>REF OPE 5639</EndToEndId>
			</PmtId>
			<InstdAmt Ccy="EUR">1100.05</InstdAmt>
			<DrctDbtTx>
				<MndtRltdInf>
					<MndtId>++MANDAT 145/13</MndtId>
					<DtOfSgntr>2009-05-20</DtOfSgntr>
					<AmdmntInd>false</AmdmntInd>
				</MndtRltdInf>
			</DrctDbtTx>
			<DbtrAgt>
				<FinInstnId>
					<BIC>CCBPFRPPDJN</BIC>
				</FinInstnId>
			</DbtrAgt>
			<Dbtr>
				<Nm>CABINET DDDD</Nm>
			</Dbtr>
			<DbtrAcct>
				<Id>
					<IBAN>FR7610807000120000000001110</IBAN>
				</Id>
			</DbtrAcct>
			<RmtInf>
				<Ustrd>REF E2E 559</Ustrd>
			</RmtInf>
		</DrctDbtTxInf>
 
		<DrctDbtTxInf>
			<PmtId>
				<InstrId>PRELSDD/2012-10-15/1/3</InstrId>
				<EndToEndId>REF OPE 5640</EndToEndId>
			</PmtId>
			<InstdAmt Ccy="EUR">3280.00</InstdAmt>
			<DrctDbtTx>
				<MndtRltdInf>
					<MndtId>++MANDAT 145/14</MndtId>
					<DtOfSgntr>2009-05-20</DtOfSgntr>
					<AmdmntInd>false</AmdmntInd>
				</MndtRltdInf>
			</DrctDbtTx>
			<DbtrAgt>
				<FinInstnId>
					<BIC>CCBPFRPPDJN</BIC>
				</FinInstnId>
			</DbtrAgt>
			<Dbtr>
				<Nm>CABINET EEEE</Nm>
			</Dbtr>
			<DbtrAcct>
				<Id>
					<IBAN>FR7610807000450000000001197</IBAN>
				</Id>
			</DbtrAcct>
			<RmtInf>
				<Ustrd>REF E2E560</Ustrd>
			</RmtInf>
		</DrctDbtTxInf>
 
	</PmtInf>
 
</CstmrDrctDbtInitn>
</Document>

En gros j'ai au début du document une balise "GrpHdr", puis X balises "PmtInf" à l'intérieur desquelles se trouvent X balises "DrctDbtTxInf".

Pour la balise "GrpHdr", pas de soucis, j'arrive à la générer.
Ça commence à se gâter dans la balise "PmtInf". J'arrive bien à la générer X fois grâce à ma requête mais à l'intérieur, les balises que je lui indique ne s'affichent que dans la dernière occurrence et je ne trouve pas la solution

Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
133
134
135
136
137
138
139
140
select	1 as Tag,
		null as Parent,
 
		'urn:iso:std:iso:20022:tech:xsd:pain.008.001.02' as [Document!1!xmlns],
		'http://www.w3.org/2001/XMLSchema-instance'	as [Document!1!xmlns:xsi],
		'urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd' as [Document!1!xsi:schemaLocation],
		null as [CstmrDrctDbtInitn!2!hhhh],
		null as [GrpHdr!21!MsgId!ELEMENT],
		null as [GrpHdr!21!CreDtTm!ELEMENT],
		null as [GrpHdr!21!NbOfTxs!ELEMENT],
		null as [GrpHdr!21!CtrlSum!ELEMENT],
		null as [InitgPty!211!Nm!ELEMENT],
		null as [PmtInf!22!PmfInfd!ELEMENT],
		null as [PmtInf!22!PmtMtd!ELEMENT],
		null as [PmtInf!22!NbOfTxs!ELEMENT],
		null as [PmtInf!22!CtrlSum!ELEMENT],
		null as [PmtTpInf!221!hhhh]
 
 
union all
select	2 as Tag,
		1 as Parent,
 
		null	, -- as [Document!1!xmlns]
		null	, -- as [Document!1!xmlns:xsi]
		null	, -- as [Document!1!xsi:schemaLocation]
		null	, -- as [CstmrDrctDbtInitn!2!hhhh]
		null	, -- as [GrpHdr!21!MsgId!ELEMENT]
		null	, -- as [GrpHdr!21!CreDtTm!ELEMENT]
		null	, -- as [GrpHdr!21!NbOfTxs!ELEMENT]
		null	, -- as [GrpHdr!21!CtrlSum!ELEMENT]
		null	, -- as [InitgPty!211!Nm!ELEMENT]
		null	, -- as [PmtInf!22!PmfInfd!ELEMENT]
		null	, -- as [PmtInf!22!PmtMtd!ELEMENT]
		null	, -- as [PmtInf!22!NbOfTxs!ELEMENT]
		null	, -- as [PmtInf!22!CtrlSum!ELEMENT]
		null	 -- as [PmtTpInf!221!hhhh]
 
 
union all
select	21 as Tag,
		2 as Parent,
 
		null	, -- as [Document!1!xmlns]
		null	, -- as [Document!1!xmlns:xsi]
		null	, -- as [Document!1!xsi:schemaLocation]
		null	, -- as [CstmrDrctDbtInitn!2!hhhh]
		'PREL/'+replace(convert(varchar(50),getdate(),120),' ','T'), -- as [GrpHdr!21!MsgId!ELEMENT]
		replace(convert(varchar(50),getdate(),120),' ','T'), -- as [GrpHdr!21!CreDtTm!ELEMENT]
		count(*), -- as [GrpHdr!21!NbOfTxs!ELEMENT]
		sum(TARIF.TARIF_MONTANT*REGLEMENT.REGLE_EXAMPLAIRE),  -- as [GrpHdr!21!CtrlSum!ELEMENT]
		null	, -- as [InitgPty!211!Nm!ELEMENT]
		null	, -- as [PmtInf!22!PmfInfd!ELEMENT]
		null	, -- as [PmtInf!22!PmtMtd!ELEMENT]
		null	, -- as [PmtInf!22!NbOfTxs!ELEMENT]
		null	, -- as [PmtInf!22!CtrlSum!ELEMENT]
		null	 -- as [PmtTpInf!221!hhhh]
 
 
from RIB 
INNER JOIN REGLEMENT ON RIB.NUM_REGLEMENT = REGLEMENT.REGLE_NUM 
INNER JOIN ABONNEMENT ON REGLEMENT.ABO_NUM = ABONNEMENT.ABO_NUM 
INNER JOIN TARIF ON REGLEMENT.REGLE_TARIF = TARIF.TARIF_NUM AND RIB.COMPTEUR < TARIF.TARIF_NB_PRELEVEMENT 
INNER JOIN STATUS ON REGLEMENT.REGLE_STATUS = STATUS.STATUS_NUM
WHERE (ABONNEMENT.PUBLI_NUM = 42) 
AND (STATUS.STATUS_ACTION = 1 OR STATUS.STATUS_ACTION = 2) 
AND (REGLEMENT.REGLE_PRELEVE = 0) AND (RIB.SUSPENDU = 0)
 
union all
select	211 as Tag,
		21 as Parent,
 
		null	, -- as [Document!1!xmlns]
		null	, -- as [Document!1!xmlns:xsi]
		null	, -- as [Document!1!xsi:schemaLocation]
		null	, -- as [CstmrDrctDbtInitn!2!hhhh]
		null	, -- as [GrpHdr!21!MsgId!ELEMENT]
		null	, -- as [GrpHdr!21!CreDtTm!ELEMENT]
		null	, -- as [GrpHdr!21!NbOfTxs!ELEMENT]
		null	, -- as [GrpHdr!21!CtrlSum!ELEMENT]
		'MAGNIFICAT', -- as [InitgPty!211!Nm!ELEMENT]
		null	, -- as [PmtInf!22!PmfInfd!ELEMENT]
		null	, -- as [PmtInf!22!PmtMtd!ELEMENT]
		null	, -- as [PmtInf!22!NbOfTxs!ELEMENT]
		null	, -- as [PmtInf!22!CtrlSum!ELEMENT]	
		null	 -- as [PmtTpInf!221!hhhh]
 
 
 
union all
select	22 as Tag,
		2 as Parent,
 
		null	, -- as [Document!1!xmlns]
		null	, -- as [Document!1!xmlns:xsi]
		null	, -- as [Document!1!xsi:schemaLocation]
		null	, -- as [CstmrDrctDbtInitn!2!hhhh]
		null	, -- as [GrpHdr!21!MsgId!ELEMENT]
		null	, -- as [GrpHdr!21!CreDtTm!ELEMENT]
		null	, -- as [GrpHdr!21!NbOfTxs!ELEMENT]
		null	, -- as [GrpHdr!21!CtrlSum!ELEMENT]
		null	, -- as [InitgPty!211!Nm!ELEMENT]
		'REMISE '+convert(varchar(10),'55'+right('00'+day(getdate()),2)+right('00'+month(getdate()),2)+right('0000'+year(getdate()),4))	, -- as [PmtInf!22!PmfInfd!ELEMENT]
		'DD'	, -- as [PmtInf!22!PmtMtd!ELEMENT]
		count(*), -- as [PmtInf!22!NbOfTxs!ELEMENT]
		sum(TARIF.TARIF_MONTANT*REGLEMENT.REGLE_EXAMPLAIRE), -- as [PmtInf!22!CtrlSum!ELEMENT]	
		null	 -- as [PmtTpInf!221!hhhh]
 
 
FROM RIB 
INNER JOIN REGLEMENT ON RIB.NUM_REGLEMENT = REGLEMENT.REGLE_NUM 
INNER JOIN ABONNEMENT ON REGLEMENT.ABO_NUM = ABONNEMENT.ABO_NUM 
INNER JOIN TARIF ON REGLEMENT.REGLE_TARIF = TARIF.TARIF_NUM AND RIB.COMPTEUR < TARIF.TARIF_NB_PRELEVEMENT 
INNER JOIN STATUS ON REGLEMENT.REGLE_STATUS = STATUS.STATUS_NUM
WHERE (ABONNEMENT.PUBLI_NUM = 42) 
AND (STATUS.STATUS_ACTION = 1 OR STATUS.STATUS_ACTION = 2) 
AND (REGLEMENT.REGLE_PRELEVE = 0) AND (RIB.SUSPENDU = 0)
group by case when COMPTEUR = 0 then convert(varchar(10),'FRST') else 'RCUR' end
 
union all
select	221 as Tag,
		22 as Parent,
 
		null	, -- as [Document!1!xmlns]
		null	, -- as [Document!1!xmlns:xsi]
		null	, -- as [Document!1!xsi:schemaLocation]
		null	, -- as [CstmrDrctDbtInitn!2!hhhh]
		null	, -- as [GrpHdr!21!MsgId!ELEMENT]
		null	, -- as [GrpHdr!21!CreDtTm!ELEMENT]
		null	, -- as [GrpHdr!21!NbOfTxs!ELEMENT]
		null	, -- as [GrpHdr!21!CtrlSum!ELEMENT]
		null	, -- as [InitgPty!211!Nm!ELEMENT]
		null	, -- as [PmtInf!22!PmfInfd!ELEMENT]
		null	, -- as [PmtInf!22!PmtMtd!ELEMENT]
		null	, -- as [PmtInf!22!NbOfTxs!ELEMENT]
		null	, -- as [PmtInf!22!CtrlSum!ELEMENT]	
		null	 -- as [PmtTpInf!221!hhhh]
 
 
FOR XML EXPLICIT

Il faudrait que "PmtTpInf" se trouve dans chaque balise "PmtInf".
Mais voici ce que j'obtiens (la balise n'apparait que dans la 2eme occurrence):

Code XML :

Sélectionner tout - Visualiser dans une fenêtre à part

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
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd">
  <CstmrDrctDbtInitn>
    <GrpHdr>
      <MsgId>PREL/2013-11-25T10:58:26</MsgId>
      <CreDtTm>2013-11-25T10:58:26</CreDtTm>
      <NbOfTxs>331</NbOfTxs>
      <CtrlSum>8132.00</CtrlSum>
      <InitgPty>
        <Nm>MAGNIFICAT</Nm>
      </InitgPty>
    </GrpHdr>
    <PmtInf>
      <PmfInfd>REMISE 5525112013</PmfInfd>
      <PmtMtd>DD</PmtMtd>
      <NbOfTxs>12</NbOfTxs>
      <CtrlSum>304.00</CtrlSum>
    </PmtInf>
    <PmtInf>
      <PmfInfd>REMISE 5525112013</PmfInfd>
      <PmtMtd>DD</PmtMtd>
      <NbOfTxs>319</NbOfTxs>
      <CtrlSum>7828.00</CtrlSum>
      <PmtTpInf />
    </PmtInf>
  </CstmrDrctDbtInitn>
</Document>

J'ai essayé de répéter ma requête qui me génère le "PmtInf" quand le select du "PmtTpInf" mais du coup ça m'affiche 2 fois la balise dans la dernière occurrence.

J'ai aussi essayé d'imbriquer un XML secondaire dans mon principal mais j'ai un plantage et j'avoue que je patauge complètement, c'est la 1ere fois que je fais du XML et c'est le flou total

Y aurait-il une âme charitable pour m'aider ?

Merci !

**rudib** · 25/11/2013, 21h16

Hello,

je sais que ce n'est pas vraiment une réponse précise à ta question, mais quelle est ta version de SQL Server ? A partir de SQL Server 2005, je te conseille fortement d'oublier FOR XML EXPLICIT et d'utiliser FOR XML PATH à la place, c'est beaucoup moins prise de tête et tu peux faire la même chose.

**Oluha** · 26/11/2013, 10h16

Finalement j'ai résolu mon problème, il fallait mettre un id pour faire le lien entre les blocs et surtout un order by à la fin.

En fait, ça aide vachement pour le tri de voir la requête sous forme de tableau en enlevant le "for xml".

Sinon j'utilise SQL Server 2005 mais c'est mon chef qui m'a dit d'utiliser cette méthode

Créer un fichier XML avec FOR XML EXPLICIT

MS SQL Server

Discussions similaires

Partager

Partager