Génération d'un fichier XML depuis une requête

**Andry** · 11/11/2022, 09h29

Bonjour à tous,
Je dois transformer une requête SQL en XML suivant le modèle ci-après :

Code :

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

1
2
3
4
5
6
7
8
9
10
11

<Entete Agence="0010001" Identite="0000010" DateRelation="10062010">
  <Particulier>
    <Information Nom="DOE" Prenom="John" Genre="M" DatNaissance="11061985" />
    <PieceIdentite TypPiece="01" NumPiece="XXXXXXXXX" DatPiece="17032020" />
    <Adresse Description="15 Rue XXXX" Commune="C1012" CodPostal="888" />
    <Compte Status="00" Numero="XXXXXXXXXXX" CleRib="00" Type="01"></Compte>
    <Compte Status="00" Numero="YYYYYYYYYYY" CleRib="34" Type="02"></Compte>
  </Particulier>
</Entete>

Pour ce faire, j'ai tenté avec SQL Management Studio de reproduire ce format de sortie avec la requête suivante :

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
SELECT
     BRC.Code AS "@Agence"
    ,CUS.Code AS "@Identite"
    ,FORMAT(CUS.ContactDate, 'ddMMyyyy') AS "@DateRelation"
    ,CUS.LastName AS [Particulier/Information/@Nom]
    ,COALESCE (CUS.FirstName, 'N/A') AS [Particulier/Information/@Prenom]
    ,CUS.Gender AS [Particulier/Information/@Genre]
    ,FORMAT(CUS.BirthDate, 'ddMMyyyy') AS [Particulier/Information/@DateNaissance]
    ,CASE
       WHEN CUS.DocType = 1 THEN '01' 
       WHEN CUS.DocType = 2 THEN '03' 
     END AS [Particulier/PieceIdentite/@Type]
    ,CUS.DocNumber AS [Particulier/PieceIdentite/@Numero]
    ,FORMAT(CUS.DocDate, 'ddMMyyyy') AS [Particulier/PieceIdentite/@Date]
    ,CUS.Address AS [Particulier/Adresse/@Description]
    ,TOW.Code AS [Particulier/Adresse/@Commune]
    ,TOW.PostalCode AS [Particulier/Adresse/@CodePostal]
    ,ACC.Status AS [Particulier/Compte/@Status]
    ,ACC.Number AS [Particulier/Compte/@Numero]
    ,ACC.Rib AS [Particulier/Compte/@CleRib]
    ,ACC.Type AS [Particulier/Compte/@Type]
FROM MyCBS.dbo.Customer CUS 
INNER JOIN MyCBS.dbo.Branch BRC ON BRC.BranchID = CUS.BranchID
INNER JOIN MyCBS.dbo.Account ACC ON ACC.CustomerID = CUS.CustomerID
INNER JOIN MyCBS.dbo.Town TOW ON TOW.PlaceID  = CUS.DocPlaceID 
WHERE CUS.CustomerID = 545875 --- Customer has 2 accounts
ORDER BY CUS.Code
FOR XML PATH ('Entete');

Sauf que il me sort 2 lignes XML au lieu d'une avec les 2 lignes de comptes

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
<Entete Agence="0010001" Identite="0000010" DateRelation="10062010">
  <Particulier>
    <Information Nom="DOE" Prenom="John" Genre="M" DatNaissance="11061985" />
    <PieceIdentite TypPiece="01" NumPiece="XXXXXXXXX" DatPiece="17032020" />
    <Adresse Description="15 Rue XXXX" Commune="C1012" CodPostal="888" />
    <Compte Status="00" Numero="XXXXXXXXXXX" CleRib="00" Type="01"></Compte>
  </Particulier>
</Entete>
<Entete Agence="0010001" Identite="0000010" DateRelation="10062010">
  <Particulier>
    <Information Nom="DOE" Prenom="John" Genre="M" DatNaissance="11061985" />
    <PieceIdentite TypPiece="01" NumPiece="XXXXXXXXX" DatPiece="17032020" />
    <Adresse Description="15 Rue XXXX" Commune="C1012" CodPostal="888" />
    <Compte Status="00" Numero="YYYYYYYYYYY" CleRib="34" Type="02"></Compte>
  </Particulier>
</Entete>

J'ai tenté de faire avec le Nested Query mais le resultat n'est pas celui escompté.
Si quelqu'un peut m'aider à resoudre ce problème.
Andry

**SQLpro** · 18/11/2022, 11h31

Sans la description DDL de vos tables (CREATE TABLE) et un jeu d'essais impossible de vous aider.

A +

**Andry** · 18/11/2022, 13h53

Bonjour SQLpro,
je posterai les DDL et un jeux d'essais ce weekend afin que vous puissiez tester.
Merci d'avance.
Andry

**Andry** · 21/11/2022, 10h01

Bonjour
Ci-dessous les DDL pour les tables. Je les ai simplifier pour faire simple.

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
 
CREATE TABLE MyCBS.dbo.Branch (
    BranchID int NOT NULL, --
    Code nvarchar(20) NOT NULL,
    Name nvarchar(200) NOT NULL,
    Address nvarchar(200) NOT NULL,
    CONSTRAINT PK_Branch PRIMARY KEY (BranchID)
);
 
CREATE TABLE MyCBS.dbo.Town (
    PlaceID int NOT NULL, --
    Code nvarchar(20) NOT NULL,
    Name nvarchar(200) NOT NULL,
    PostalCode nvarchar(20) NOT NULL,
    CONSTRAINT PK_Town PRIMARY KEY (PlaceID)
);
 
CREATE TABLE MyCBS.dbo.Customer (
    CustomerID int NOT NULL, --
    Code nvarchar(50) NOT NULL,
    ContactDate date NOT NULL,
    LastName nvarchar(250) NOT NULL,
    FirstName nvarchar(250),
    Gender nvarchar(2) NOT NULL,
    BirthDate date NULL,
    DocType smallint,
    DocNumber nvarchar(100),
    Address nvarchar(200) NOT NULL,
    DocPlaceID int NOT NULL,
    BranchID int NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID)
);
 
 
-- MyCBS.dbo.Customer foreign keys
ALTER TABLE MyCBS.dbo.Customer ADD CONSTRAINT FK_Customer_BranchID FOREIGN KEY (BranchID) REFERENCES MyCBS.dbo.Branch(BranchID);
ALTER TABLE MyCBS.dbo.Customer ADD CONSTRAINT FK_Customer_DocPlaceID FOREIGN KEY (DocPlaceID) REFERENCES MyCBS.dbo.Town(PlaceID);
 
CREATE TABLE MyCBS.dbo.Account (
    AccountID int NOT NULL, --
    CustomerID int NOT NULL,
    Number nvarchar(100) NOT NULL,
    Rib nvarchar(2) NOT NULL,
    Type nvarchar(5) NOT NULL,
    OpeningDate date NOT NULL,
    ClosingDate date NULL,
    Status nvarchar(5) NOT NULL,
    CONSTRAINT PK_Account PRIMARY KEY (AccountID)
);
 
-- MyCBS.dbo.Account foreign keys
ALTER TABLE MyCBS.dbo.Account ADD CONSTRAINT FK_Account_CustomerID FOREIGN KEY (CustomerID) REFERENCES MyCBS.dbo.Customer(CustomerID);

Le jeu d'essais pour Branch

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
 
INSERT INTO Branch VALUES (1, '0001', 'BRANCH01');
INSERT INTO Branch VALUES (2, '0002', 'BRANCH02');
INSERT INTO Branch VALUES (3, '0003', 'BRANCH03');
INSERT INTO Branch VALUES (4, '0004', 'BRANCH04');
INSERT INTO Branch VALUES (5, '0005', 'BRANCH05');
INSERT INTO Branch VALUES (6, '0006', 'BRANCH06');
INSERT INTO Branch VALUES (7, '0007', 'BRANCH07');
INSERT INTO Branch VALUES (8, '0008', 'BRANCH08');
INSERT INTO Branch VALUES (9, '0009', 'BRANCH09');
INSERT INTO Branch VALUES (10, '0010', 'BRANCH10');
INSERT INTO Branch VALUES (11, '0011', 'BRANCH11');
INSERT INTO Branch VALUES (12, '0012', 'BRANCH12');
INSERT INTO Branch VALUES (13, '0013', 'BRANCH13');
INSERT INTO Branch VALUES (14, '0014', 'BRANCH14');
INSERT INTO Branch VALUES (15, '0015', 'BRANCH15');
INSERT INTO Branch VALUES (16, '0016', 'BRANCH16');
INSERT INTO Branch VALUES (17, '0017', 'BRANCH17');
INSERT INTO Branch VALUES (18, '0018', 'BRANCH18');
INSERT INTO Branch VALUES (19, '0019', 'BRANCH19');
INSERT INTO Branch VALUES (20, '0020', 'BRANCH20');

pour Town

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
 
INSERT INTO Town VALUES (1, '0001', 'Town01', '001');
INSERT INTO Town VALUES (2, '0002', 'Town02', '002');
INSERT INTO Town VALUES (3, '0003', 'Town03', '003');
INSERT INTO Town VALUES (4, '0004', 'Town04', '004');
INSERT INTO Town VALUES (5, '0005', 'Town05', '005');
INSERT INTO Town VALUES (6, '0006', 'Town06', '006');
INSERT INTO Town VALUES (7, '0007', 'Town07', '007');
INSERT INTO Town VALUES (8, '0008', 'Town08', '008');
INSERT INTO Town VALUES (9, '0009', 'Town09', '009');
INSERT INTO Town VALUES (10, '0010', 'Town10', '010');
INSERT INTO Town VALUES (11, '0011', 'Town11', '011');
INSERT INTO Town VALUES (12, '0012', 'Town12', '012');
INSERT INTO Town VALUES (13, '0013', 'Town13', '013');
INSERT INTO Town VALUES (14, '0014', 'Town14', '014');
INSERT INTO Town VALUES (15, '0015', 'Town15', '015');
INSERT INTO Town VALUES (16, '0016', 'Town16', '016');
INSERT INTO Town VALUES (17, '0017', 'Town17', '017');
INSERT INTO Town VALUES (18, '0018', 'Town18', '018');
INSERT INTO Town VALUES (19, '0019', 'Town19', '019');
INSERT INTO Town VALUES (20, '0020', 'Town20', '020');

Pour Customer

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
 
INSERT INTO Customer VALUES (1, '000000001', '2021-01-10', 'DUVERLIE', 'FRANCOISE', 'F', '2000-01-10', 1, 'XXXXXXXXX0001', 'ADRESSE 1', 1, 1);
INSERT INTO Customer VALUES (2, '000000002', '2021-01-11', 'LEMARCHAND', 'COLETTE', 'F', '1999-01-11', 1, 'XXXXXXXXX0002', 'ADRESSE 2', 1, 1);
INSERT INTO Customer VALUES (3, '000000003', '2021-01-13', 'ROCANCOURT', 'XAVIER', 'M', '2001-01-13', 1, 'XXXXXXXXX0003', 'ADRESSE 3', 1, 1);
INSERT INTO Customer VALUES (4, '000000004', '2021-02-11', 'BUISARD', 'GEORGES', 'M', '2000-02-11', 2, 'XXXXXXXXX0004', 'ADRESSE 4', 1, 1);
INSERT INTO Customer VALUES (5, '000000005', '2021-04-10', 'LAROSE', 'SOLANGE', 'F', '2002-04-10', 2, 'XXXXXXXXX0005', 'ADRESSE 5', 3, 3);
INSERT INTO Customer VALUES (6, '000000006', '2021-04-11', 'HOLLEY', 'JANINE', 'F', '1998-04-11', 2, 'XXXXXXXXX0006', 'ADRESSE 6', 5, 5);
INSERT INTO Customer VALUES (7, '000000007', '2021-04-13', 'RIVIERE', 'GERARD', 'M', '1997-04-13', 2, 'XXXXXXXXX0007', 'ADRESSE 7', 6, 6);
INSERT INTO Customer VALUES (8, '000000008', '2021-04-22', 'LEGRAND', 'JEAN LOUIS', 'M', '2005-04-22', 2, 'XXXXXXXXX0008', 'ADRESSE 8', 2, 2);
INSERT INTO Customer VALUES (9, '000000009', '2021-04-26', 'DUDOUET-MALASSIS', 'GISELE', 'F', '2002-04-26', 1, 'XXXXXXXXX0009', 'ADRESSE 9', 4, 4);
INSERT INTO Customer VALUES (10, '000000010', '2021-05-06', 'MORIN', 'GENEVIEVE', 'F', '2003-05-06', 1, 'XXXXXXXXX0010', 'ADRESSE 10', 1, 1);
INSERT INTO Customer VALUES (11, '000000011', '2021-05-07', 'LE NOEL', 'GUY', 'M', '1995-05-07', 1, 'XXXXXXXXX0011', 'ADRESSE 11', 2, 2);
INSERT INTO Customer VALUES (12, '000000012', '2021-05-09', 'LANGRAND', 'AUGUSTE', 'M', '1985-05-09', 2, 'XXXXXXXXX0012', 'ADRESSE 12', 3, 3);
INSERT INTO Customer VALUES (13, '000000013', '2021-06-01', 'ROBERT', 'PIERRE', 'M', '1970-06-01', 2, 'XXXXXXXXX0013', 'ADRESSE 13', 9, 9);
INSERT INTO Customer VALUES (14, '000000014', '2021-06-09', 'COISPEL', 'BERNARD', 'M', '1990-06-09', 2, 'XXXXXXXXX0014', 'ADRESSE 14', 19, 19);
INSERT INTO Customer VALUES (15, '000000015', '2021-06-19', 'LEBOSQUAIN', 'JEAN', 'M', '1977-06-19', 2, 'XXXXXXXXX0015', 'ADRESSE 15', 11, 11);
INSERT INTO Customer VALUES (16, '000000016', '2021-06-25', 'LEROY', 'NADINE', 'F', '2000-06-25', 2, 'XXXXXXXXX0016', 'ADRESSE 16', 14, 14);
INSERT INTO Customer VALUES (17, '000000017', '2021-07-07', 'LENORMAND', 'MICHEL', 'M', '2001-07-07', 1, 'XXXXXXXXX0017', 'ADRESSE 17', 6, 6);
INSERT INTO Customer VALUES (18, '000000018', '2021-07-09', 'LE COMTE', 'GERARD', 'M', '2002-07-09', 2, 'XXXXXXXXX0018', 'ADRESSE 18', 8, 8);
INSERT INTO Customer VALUES (19, '000000019', '2021-07-21', 'FERREIRA', 'JACK', 'M', '1980-07-21', 1, 'XXXXXXXXX0019', 'ADRESSE 19', 7, 7);
INSERT INTO Customer VALUES (20, '000000020', '2021-08-02', 'VALENTE', 'J GEORGES', 'M', '1981-08-02', 2, 'XXXXXXXXX0020', 'ADRESSE 20', 2, 2);

et pour Account

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
 
INSERT INTO Account VALUES (1, 1, 'XXXX-XXXX-X0001', '01', '00', '2021-01-10', Null, '01');
INSERT INTO Account VALUES (2, 2, 'XXXX-XXXX-X0002', '02', '00', '2021-01-11', Null, '01');
INSERT INTO Account VALUES (3, 3, 'XXXX-XXXX-X0003', '03', '00', '2021-01-13', Null, '01');
INSERT INTO Account VALUES (4, 4, 'XXXX-XXXX-X0004', '04', '00', '2021-02-11', Null, '01');
INSERT INTO Account VALUES (5, 2, 'XXXX-XXXX-X0005', '05', '01', '2021-04-10', Null, '01');
INSERT INTO Account VALUES (6, 2, 'XXXX-XXXX-X0006', '06', '02', '2021-04-11', Null, '01');
INSERT INTO Account VALUES (7, 1, 'XXXX-XXXX-X0007', '07', '01', '2021-04-13', Null, '01');
INSERT INTO Account VALUES (8, 3, 'XXXX-XXXX-X0008', '08', '01', '2021-04-22', Null, '01');
INSERT INTO Account VALUES (9, 4, 'XXXX-XXXX-X0009', '09', '01', '2021-04-26', Null, '01');
INSERT INTO Account VALUES (10, 5, 'XXXX-XXXX-X0010', '10', '00', '2021-05-06', Null, '01');
INSERT INTO Account VALUES (11, 6, 'XXXX-XXXX-X0011', '11', '00', '2021-05-07', Null, '01');
INSERT INTO Account VALUES (12, 7, 'XXXX-XXXX-X0012', '12', '00', '2021-05-09', '2022-04-05', '00');
INSERT INTO Account VALUES (13, 8, 'XXXX-XXXX-X0013', '13', '00', '2021-06-01', Null, '01');
INSERT INTO Account VALUES (14, 8, 'XXXX-XXXX-X0014', '14', '01', '2021-06-09', Null, '01');
INSERT INTO Account VALUES (15, 11, 'XXXX-XXXX-X0015', '15', '00', '2021-06-19', Null, '01');
INSERT INTO Account VALUES (16, 20, 'XXXX-XXXX-X0016', '16', '01', '2021-06-25', Null, '01');
INSERT INTO Account VALUES (17, 15, 'XXXX-XXXX-X0017', '17', '01', '2021-07-07', Null, '01');
INSERT INTO Account VALUES (18, 9, 'XXXX-XXXX-X0018', '18', '02', '2021-07-09', Null, '01');
INSERT INTO Account VALUES (19, 10, 'XXXX-XXXX-X0019', '19', '00', '2021-07-21', Null, '01');
INSERT INTO Account VALUES (20, 10, 'XXXX-XXXX-X0020', '20', '02', '2021-08-02', Null, '01');

J'espère que ceci sera suffisant.
Merci d'avance SQL Pro.

**SQLpro** · 24/11/2022, 23h05

Quelque chose comme ça :

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
SELECT
     BRC.Code AS "@Agence"
    ,CUS.Code AS "@Identite"
    ,FORMAT(CUS.ContactDate, 'ddMMyyyy') AS "@DateRelation"
    ,CUS.LastName AS [Particulier/Information/@Nom]
    ,COALESCE (CUS.FirstName, 'N/A') AS [Particulier/Information/@Prenom]
    ,CUS.Gender AS [Particulier/Information/@Genre]
    ,FORMAT(CUS.BirthDate, 'ddMMyyyy') AS [Particulier/Information/@DateNaissance]
    ,CASE
       WHEN CUS.DocType = 1 THEN '01' 
       WHEN CUS.DocType = 2 THEN '03' 
     END AS [Particulier/PieceIdentite/@Type]
    ,CUS.DocNumber AS [Particulier/PieceIdentite/@Numero]
 --   ,FORMAT(CUS.DocDate, 'ddMMyyyy') AS [Particulier/PieceIdentite/@Date]
    ,CUS.Address AS [Particulier/Adresse/@Description]
    ,TOW.Code AS [Particulier/Adresse/@Commune]
    ,TOW.PostalCode AS [Particulier/Adresse/@CodePostal]
    ,CAST((SELECT ACC.Status AS [Particulier/Compte/@Status]
                 ,ACC.Number AS [Particulier/Compte/@Numero]
                 ,ACC.Rib AS [Particulier/Compte/@CleRib]
                 ,ACC.Type AS [Particulier/Compte/@Type]
           FROM   dbo.Account AS ACC 
           WHERE  ACC.CustomerID = CUS.CustomerID
           FOR XML PATH) AS XML) AS ACCOUNT
FROM dbo.Customer CUS 
INNER JOIN dbo.Branch BRC ON BRC.BranchID = CUS.BranchID
-- INNER JOIN MyCBS.dbo.Account ACC ON ACC.CustomerID = CUS.CustomerID
INNER JOIN dbo.Town TOW ON TOW.PlaceID  = CUS.DocPlaceID 
WHERE CUS.CustomerID = 545875 --- Customer has 2 accounts
ORDER BY CUS.Code
FOR XML PATH ('Entete');

**Andry** · 25/11/2022, 09h30

Bonjour,
Après utilisation code de SQLpro, j'obtiens le Xml suivant :

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
 
<Entete Agence="0010001" Identite="0000010" DateRelation="10062010">
  <Particulier>
    <Information Nom="DOE" Prenom="John" Genre="M" DatNaissance="11061985" />
    <PieceIdentite TypPiece="01" NumPiece="XXXXXXXXX" DatPiece="17032020" />
    <Adresse Description="15 Rue XXXX" Commune="C1012" CodPostal="888" />
  </Particulier>
   <ACCOUNT>
    <row>
      <Particulier>
        <Compte Status="00" Numero="YYYYYYYYYYY" CleRib="34" Type="02" />
      </Particulier>
    </row>
    <row>
      <Particulier>
        <Compte Status="00" Numero="YYYYYYYYYYY" CleRib="34" Type="02" />
      </Particulier>
    </row>
  </ACCOUNT>
</Entete>

Comment eliminer les noeuds ACCOUNT, Row sachant qu'on aimerais avoir le rendu ci-après

Code :

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

1
2
3
4
5
6
7
8
9
10
 
<Entete Agence="0010001" Identite="0000010" DateRelation="10062010">
  <Particulier>
    <Information Nom="DOE" Prenom="John" Genre="M" DatNaissance="11061985" />
    <PieceIdentite TypPiece="01" NumPiece="XXXXXXXXX" DatPiece="17032020" />
    <Adresse Description="15 Rue XXXX" Commune="C1012" CodPostal="888" />
    <Compte Status="00" Numero="XXXXXXXXXXX" CleRib="00" Type="01"></Compte>
    <Compte Status="00" Numero="YYYYYYYYYYY" CleRib="34" Type="02"></Compte>
  </Particulier>
</Entete>

Merci pour le coup de main.

Génération d'un fichier XML depuis une requête

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager