FOR XML PATH question d'ajustement

**GeekAlf** · 08/07/2020, 19h41

Bonjour,

Je dois constituer une requête pour générer un fichier XML SEPA.
j'utilise le FOR XML PATH
j'ai 3 questions :

1-Est-il possible d'insérer "<?xml version="1.0" encoding="UTF-8" standalone="no" ?> dans le code ? j'ai lu que non

2- la "balise source" est gérée par la requête via le "WITH XMLNAMESPACES" et Root, pas de souci, en revanche je dois packager toutes mes données avec une balise <CstmrCdtTrfInitn> et </CstmrCdtTrfInitn> qui ne se répète pas mais n'est présente qu'une fois après la balise Document et bien sur avant la fermeture, donc au niveau de Root, mais je ne peux pas mettre

Code :

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

for xml path(''), root('Document/CstmrCdtTrfInitn');

3-Enfin dans un noeud, j'ai 2 balises avec le même nom et 2 données différentes, le souci est qu'à la Génération, le xml "compacte" les deux lignes pour n'avoir qu'une balise
mon code

Code :

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

1
2
3
4
5
6
7
8
 
Select
[...]
,'15 RUE Bibi' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777 LA VILLE' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
[...]
From blabla
for xml path(''), root('Document');

je souhaiterais avoir

Code :

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

1
2
3
 
<AdrLine>15 RUE bibi</AdrLine>
<AdrLine>77777 LA VILLE</AdrLine>

mais ça me renvoie

Code :

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

<AdrLine>15 RUE bibi77777 LA VILLE</AdrLine>

Comment faire pour garder ce saut de ligne ?

Merci de votre aide

**SQLpro** · 08/07/2020, 23h44

UTF8 qui est une des pires merde qui pouvait nous arriver, n'est géré qu'a partir de la version 2019.

Le résultat d'une requête for XML peut être mis dans une variable XML. À vous d'y ajouter ce que vous voulez.

Exemple :

Code :

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

1
2
3
4
DECLARE @XML XML;
SET @XML = (SELECT *
            FROM   sys.objects 
            FOR XML AUTO, ELEMENTS, ROOT('racine'));

Pour le 3, lire :
https://docs.microsoft.com/fr-fr/sql...sing-path-mode

A +

**GeekAlf** · 09/07/2020, 10h20

Bonjour @Frédéric,

merci pour ton aide je regarde ça tout de suite !

quand tu dis

UTF8 qui est une des pires merde qui pouvait nous arriver, n'est géré qu'a partir de la version 2019.

en 2 mots en quoi c'est l'enfer ?

merci encore

**GeekAlf** · 09/07/2020, 12h45

J'avoue que je galère un peu.

j'ai tenté bourrinement de mettre ma requête dans le

Code :

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

SET @XML

Mais j'ai cette erreur

Code :

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

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

mon code global est celui-là

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
USE [Dedo]
GO
/****** Object:  Generation de fichier XML    Script Date: 02/07/2020 10:41:01 ******/
 
 
DECLARE @XML XML;
WITH XMLNAMESPACES (default 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03')
SET @XML = (
 
SELECT 
'VIREMENT AVOIR' "GrpHdr/MsgId"
,'2020-06-04T10:15:00' "GrpHdr/CreDtTm"
,(select COUNT([Numéro de prise en compte]) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "GrpHdr/NbOfTxs"
,(select SUM(InstdAmt) FROM [Dedo].[dbo].[v_comptants]  where [Numéro de prise en compte] in ('444444')) as "GrpHdr/CtrlSum"
,'TTTT' as "GrpHdr/InitgPty/Nm"
,'FR' as "GrpHdr/InitgPty/PstlAdr/Ctry"
,'15 RUE BIBI' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777 La Ville' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777777777777' as "GrpHdr/InitgPty/Id/OrgId/Othr/Id"
,'FR' as "GrpHdr/InitgPty/CtryOfRes"
--
,null "PmtInf/Id"
,null "PmtInf/PmtMtd"
,null "PmtInf/BtchBookg"
,null as "PmtInf/NbOfTxs"
,null as "PmtInf/CtrlSum"
,null "PmtInf/PmtTpInf/SvcLvl/Cd"
,null as "PmtInf/ReqdExctnDt" -- /!\ a modifier à chaque fois /!\
,Null "PmtInf/Dbtr/Nm"
,null "PmtInf/DbtrAcct/Id/IBAN"
,null "PmtInf/DbtrAgt/FinInstnId/BIC"
,null as "PmtInf/CdtTrfTxInf/PmtId"
,null as "PmtInf/CdtTrfTxInf/PmtId/EndToEndId"
,null as "PmtInf/CdtTrfTxInf/Amt/InstdAmt/@Ccy"
,null as "PmtInf/CdtTrfTxInf/Amt/InstdAmt"
,null as "PmtInf/CdtTrfTxInf/Cdtr/nm"
,null as "PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN"
,null as "PmtInf/CdtTrfTxInf/RmtInf/Ustrd"
union all
select
null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,PmtInfId "PmtInf/Id"
,PmtMtd "PmtInf/PmtMtd"
,BtchBookg "PmtInf/BtchBookg"
,(select COUNT([Numéro de prise en compte]) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "PmtInf/NbOfTxs"
,(select SUM(InstdAmt) FROM [Dedo].[dbo].[v_comptants]  where [Numéro de prise en compte] in ('444444')) as "PmtInf/CtrlSum"
,Cd "PmtInf/PmtTpInf/SvcLvl/Cd"
,'2020-06-09' as "PmtInf/ReqdExctnDt" -- /!\ a modifier à chaque fois /!\
,NmTcar "PmtInf/Dbtr/Nm"
,IBANTcar "PmtInf/DbtrAcct/Id/IBAN"
,BIC "PmtInf/DbtrAgt/FinInstnId/BIC"
,InstrId "PmtInf/CdtTrfTxInf/PmtId/InstrId"
,EndToEndId "PmtInf/CdtTrfTxInf/PmtId/EndToEndId"
,'Eur' as "PmtInf/CdtTrfTxInf/Amt/InstdAmt/@Ccy"
,Cast ( InstdAmt as decimal(18,2) )  as [text()] 
,dbo.RemoveNonASCII(nm) as "PmtInf/CdtTrfTxInf/Cdtr/nm"
,upper(IBAN) as "PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN"
,Ustrd "PmtInf/CdtTrfTxInf/RmtInf/Ustrd"
FROM [Dedo].[dbo].[v_comptants]
  where [Numéro de prise en compte] in ('444444')
for xml path(''), root('Document')
 
);

**GeekAlf** · 09/07/2020, 18h30

Je voulais éviter le for xml explicit

mais je crois que je vais y être contraint.
le T-SQL je ne connais pas trop....

**SQLpro** · 09/07/2020, 18h58

La ,déclaration d'espace de nom XML fait partie de la requête SELECT. Elle doit être située juste avant le mot clef SELECT...

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
DECLARE @XML XML;
 
SET @XML = ( 
WITH XMLNAMESPACES (default 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03')
SELECT 
'VIREMENT AVOIR' "GrpHdr/MsgId"
,'2020-06-04T10:15:00' "GrpHdr/CreDtTm"
,(select COUNT([Numéro de prise en compte]) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "GrpHdr/NbOfTxs"
,(select SUM(InstdAmt) FROM [Dedo].[dbo].[v_comptants]  where [Numéro de prise en compte] in ('444444')) as "GrpHdr/CtrlSum"
,'TTTT' as "GrpHdr/InitgPty/Nm"
,'FR' as "GrpHdr/InitgPty/PstlAdr/Ctry"
,'15 RUE BIBI' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777 La Ville' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777777777777' as "GrpHdr/InitgPty/Id/OrgId/Othr/Id"
,'FR' as "GrpHdr/InitgPty/CtryOfRes"
--
,null "PmtInf/Id"
,null "PmtInf/PmtMtd"
,null "PmtInf/BtchBookg"
,null as "PmtInf/NbOfTxs"
,null as "PmtInf/CtrlSum"
,null "PmtInf/PmtTpInf/SvcLvl/Cd"
,null as "PmtInf/ReqdExctnDt" -- /!\ a modifier à chaque fois /!\
,Null "PmtInf/Dbtr/Nm"
,null "PmtInf/DbtrAcct/Id/IBAN"
,null "PmtInf/DbtrAgt/FinInstnId/BIC"
,null as "PmtInf/CdtTrfTxInf/PmtId"
,null as "PmtInf/CdtTrfTxInf/PmtId/EndToEndId"
,null as "PmtInf/CdtTrfTxInf/Amt/InstdAmt/@Ccy"
,null as "PmtInf/CdtTrfTxInf/Amt/InstdAmt"
,null as "PmtInf/CdtTrfTxInf/Cdtr/nm"
,null as "PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN"
,null as "PmtInf/CdtTrfTxInf/RmtInf/Ustrd"
union all
select
null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,PmtInfId "PmtInf/Id"
,PmtMtd "PmtInf/PmtMtd"
,BtchBookg "PmtInf/BtchBookg"
,(select COUNT([Numéro de prise en compte]) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "PmtInf/NbOfTxs"
,(select SUM(InstdAmt) FROM [Dedo].[dbo].[v_comptants]  where [Numéro de prise en compte] in ('444444')) as "PmtInf/CtrlSum"
,Cd "PmtInf/PmtTpInf/SvcLvl/Cd"
,'2020-06-09' as "PmtInf/ReqdExctnDt" -- /!\ a modifier à chaque fois /!\
,NmTcar "PmtInf/Dbtr/Nm"
,IBANTcar "PmtInf/DbtrAcct/Id/IBAN"
,BIC "PmtInf/DbtrAgt/FinInstnId/BIC"
,InstrId "PmtInf/CdtTrfTxInf/PmtId/InstrId"
,EndToEndId "PmtInf/CdtTrfTxInf/PmtId/EndToEndId"
,'Eur' as "PmtInf/CdtTrfTxInf/Amt/InstdAmt/@Ccy"
,Cast ( InstdAmt as decimal(18,2) )  as [text()] 
,dbo.RemoveNonASCII(nm) as "PmtInf/CdtTrfTxInf/Cdtr/nm"
,upper(IBAN) as "PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN"
,Ustrd "PmtInf/CdtTrfTxInf/RmtInf/Ustrd"
FROM [Dedo].[dbo].[v_comptants]
  where [Numéro de prise en compte] in ('444444')
for xml path(''), root('Document')
)

**SQLpro** · 09/07/2020, 19h06

Envoyé par GeekAlf

Bonjour @Frédéric,

merci pour ton aide je regarde ça tout de suite !

quand tu dis

en 2 mots en quoi c'est l'enfer ?

merci encore

UTF8 c'est un encodage des caractères a longueur variable comme suit :
Les 128 premiers caractères sont sur 1 octets, les 32*640 suivant sur 2 octets, les 2*147*450*880 suivants sur 3 octets...

Dans les 128 on a les caractères majuscules et minuscules latins sans accents
Les caractères latins accentués, le grec et le cyrillique figurent sur 2 octets.
Les caractères des langues non "latines" ou assimilées (grec, cyrillique) sont sur 3 octets (hébreu, arabe, chinois...)

En dehors de l'anglais, toutes les langues étrangères sont donc pénalisées....
Alors qu'avec les pages de code, toutes les langues étrangères "latines" sont encodées avec 1 octets et les langues non latines sur 2...
Ceci permet d'assurer la suprématie de la langue anglais en terme de performance.....
Et le stockage en UTF8 est impossible à gérer directement pour les bases de données... Les octets n'étant pas alignés, l'indexation, comme le groupage sont obliger de transcoder préalablement pour effectuer un alignement !

A +

**GeekAlf** · 09/07/2020, 19h27

J'ai pas de chance

j'ai plusieurs erreurs :

Code :

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

1
2
3
4
5
6
Msg*156, Niveau*15, État*1, Ligne*6
Incorrect syntax near the keyword 'WITH'.
Msg*319, Niveau*15, État*1, Ligne*6
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg*102, Niveau*15, État*1, Ligne*69
Incorrect syntax near ')'.

**GeekAlf** · 10/07/2020, 13h18

Quelle galère !

je vais essayer de formuler autrement.

Voici l'arborescence souhaitée
les données en vert sont du texte et non présents dans une table, elles ne se répètent pas et ne sont présentes qu'une seule fois en début de fichier
les données en orange sont du texte et non présents dans une table, en revanche elles doivent se répéter à chaque ligne
les données en bleu sont stockées dans une table je les attaque via une vue (est-ce là le pb ?) et bien évidemment constitue le détail des données.

Nom : 2020-07-10 13_13_42-Arborescence finale XML.xlsx - Excel.png
Affichages : 753
Taille : 54,5 Ko

Nom : 2020-07-10 13_13_42-Arborescence finale XML.xlsx - Excel.png
Affichages : 753
Taille : 54,5 Ko

je dois mal appréhender l'arborescence xml car soit la balise principale se répète alors que je ne veux pas (problème avec CstmrCdtTrfInitn ou PmtInf) soit elle ne se répète pas alors que je le voudrais...
allez, la semaine prochaine je me mets sur du for xml explicit....

priez pour moi

FOR XML PATH question d'ajustement

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager