Charger une table SQL Server avec un fichier XML

Version imprimable

28/08/2017, 19h17
lucasgensac

Charger une table SQL Server avec un fichier XML

Bonjour,

Comme l'indique le titre je cherche à faire un programme pour remplir une table (déjà créée) d'une base de données à partir d'un fichier XML. Cela ne me semble pas compliqué dans l'absolu mais je suis débutant autant en .NET qu'en traitement de fichiers XML.

La structure du fichier XML est la suivante :
<Element>

<NumeroElement>476</NumeroElement>
<Info1>358</Info1>
<Info2>4012</Info2>
<Code1>
<Code>OK</Code>
<Date1>2017.06.30 16:45</Date1>
</Code1>
<Code2>
<Code>OK</Code>
<Date1>2017.06.30 12:40</Date1>
</Code2>
<Code3>
<STATUT>XX</STATUT>
<Date2>2017.06.22 17:12</Date2>
<Date3>2017.06.30 12:40</Date3>
<LOT>Lot1</LOT>
</Code3>
</Element>

Sachant qu'il peut y avoir plusieurs éléments.

Les colonnes de la table sont les suivantes :
NumeroElement
Info1
Info2
Code_1
Date1_1
Code_2
Date1_2
Statut_3
Date2_3
Date3_3
Lot_3

J'ai déjà cherché un peu sur MSDN mais il y a apparemment plusieurs façons différentes de lire les fichiers XML et je n'ai pas su déterminer laquelle était la plus adaptée, ni comment préparer puis insérer les données dans la table.

Merci pour votre aide.
29/08/2017, 16h01
popo

Pour lire le XML, le plus simple dans ton cas serait d'utiliser la sérialisation.
Ainsi, tu pourra réutiliser la classe Element pour la suite.
Regarde du coté de XmlSerializer et de sa méthode Deserialize()

Pour l'insertion en base de données, cela dépends du type de base de données.
Il a falloir préciser (SQL Server, Oracle, Access, MySQL, ...).
29/08/2017, 17h37
lucasgensac

Merci pour ta réponse, j'ai en effet oublié de préciser que la base de donnée est sur SQL Server.

J'ai essayé Deserialize et pour l'instant ça me donne "Il existe une erreur dans le document XML (0, 0)."
Je me dis qu'il est possible que je n'aie pas bien déclaré la classe par rapport à la structure du fichier XML, mais comment faire pour les noeuds du "niveau 2" (Code1/Code ou Code2/Code par exemple) puisque les noms des champs peuvent être les même ? A savoir, le but est de ne pas modifier le fichier XML.
29/08/2017, 23h01
François DORIN
Bonjour,

Plusieurs possibilités.

La première est effectivement d'utiliser la désérialisation. C'est a priori la voie que tu as choisi. Dans ce cas, montre nous comment tu déclares ta classe.

Une deuxième possibilité, c'est d'utiliser une procédure stockée du côté de SQL Server. SQL Server permet de manipuler facilement du XML, et insérer directement un enregistrement dans une table :
Code:

1 2 3 INSERT INTO table(col_1, col2) SELECT @Xml.value('(/value1/text())[1]', 'VARCHAR(50)'), @Xml.value('(/value2/text())[1]', 'INT'),
où @Xml est une variable de type XML.
30/08/2017, 15h41
lucasgensac

J'ai trouvé mon problème : pour la structure que j'ai donnée plus haut j'arrive à déclarer la classe et faire la désérialisation, le problème arrive quand il y a plusieurs éléments :

<Elements>

<Element>
...
</Element>
<Element>
...
</Element>
</Elements>

J'ai lu que la technique la plus simple à utiliser dans ce cas est d'utiliser une List<>, le problème est que je travaille sur la version 1.1 du Framework .Net, qui ne dispose pas des List<>. Vous avez une autre solution ?

Merci d'avance.
31/08/2017, 16h32
popo

Dans le Framework 1.1, il doit exister la ArrayList.
Tu devrait pouvoir déclarer un propriété Elements de type ArrayList.

Je crois bien que ceci pourrait t'intéresser :
https://www.codeproject.com/Articles...-serialization
31/08/2017, 16h47
François DORIN

Dans ce cas, je te conseille la méthode N°2 de mon précédent message.

Non seulement cela sera plus simple, mais cela sera également plus rapide en terme de temps d'exécution. Bien plus rapide...
31/08/2017, 17h21
lucasgensac

@Popo : Merci j'ai réussi, il reste maintenant à enregistrer les données de l'objet dans la table ... une idée pour comment faire ça en 1.1 ?

@François Dorin : pour l'instant j'ai tenté la désérialisation car ça ressemble à d'autres programmes que j'ai en exemple mais ta technique m'intéresse, pourrais-tu développer un peu plus stp ? par exemple sur les 3 lignes que tu as écrites, j'imagine que dans mon cas la première serait :

Code:

INSERT INTO table(NumeroElement, Info1)

par exemple, mais pour le reste j'ai du mal à voir : à quoi correspond le @xml ? et les différentes parties de "(/value1/text())[1]" ? Où renseigne-t-on le chemin et le nom du fichier ? Comment fait-on pour les sous-éléments comme Code1/Code et Code2/Code ?

Merci pour vos réponses.

Quelque chose comme ça :

Code:

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
DECLARE @Xml XML = '<Elements>
<Element>
<NumeroElement>476</NumeroElement>
<Info1>358</Info1>
<Info2>4012</Info2>
<Code1>
<Code>OK</Code>
<Date1>2017.06.30 16:45</Date1>
</Code1>
<Code2>
<Code>OK</Code>
<Date1>2017.06.30 12:40</Date1>
</Code2>
<Code3>
<STATUT>XX</STATUT>
<Date2>2017.06.22 17:12</Date2>
<Date3>2017.06.30 12:40</Date3>
<LOT>Lot1</LOT>
</Code3>
</Element>
 
<Element>
<NumeroElement>542</NumeroElement>
<Info1>87</Info1>
<Info2>885</Info2>
<Code1>
<Code>OK</Code>
<Date1>2017.06.31 10:40</Date1>
</Code1>
<Code2>
<Code>OK</Code>
<Date1>2017.06.30 12:40</Date1>
</Code2>
<Code3>
<STATUT>YY</STATUT>
<Date2>2017.06.22 17:12</Date2>
<Date3>2017.06.30 12:40</Date3>
<LOT>Lot2</LOT>
</Code3>
</Element>
 
</Elements>'
 
INSERT INTO table_element(NumeroElement, Info1, Info2, Code_1, Date1_1, Code_2, Date1_2, Statut_3, Date2_3, Date3_3, Lot_3)
SELECT
   X.t.value('(NumeroElement/text())[1]', 'INT') AS numero_element,
   X.t.value('(Info1/text())[1]', 'INT') AS info1,
   X.t.value('(Info2/text())[1]', 'INT') AS info2,
   X.t.value('(Code1/Code/text())[1]', 'VARCHAR(50)') AS code_1,
   X.t.value('(Code1/Date1/text())[1]', 'VARCHAR(50)') AS date1_1,
   X.t.value('(Code2/Code/text())[1]', 'VARCHAR(50)') AS code_2,
   X.t.value('(Code2/Date1/text())[1]', 'VARCHAR(50)') AS date1_2,
   X.t.value('(Code3/STATUT/text())[1]', 'VARCHAR(50)') AS statut_3,
   X.t.value('(Code3/Date2/text())[1]', 'VARCHAR(50)') AS date2_3,
   X.t.value('(Code3/Date3/text())[1]', 'VARCHAR(50)') AS date3_3,
   X.t.value('(Code3/LOT/text())[1]', 'VARCHAR(50)') AS lot_3
 
 
FROM @Xml.nodes('Elements/Element') AS X(t)

Ici, @Xml est une variable que j'ai déclaré de manière locale afin d'avoir un exemple fonctionnel. Mais cela peut très bien être le paramètre d'une procédure stockée.

La méthode François sera certainement plus rapide à l'exécution.

Mais si tu souhaite continuer dans cette voie, ceci devrait t'aider pour l'insertion en base
Il s'agit d'un bout de code que j'ai récupéré et adapté à partir de l'un de mes vieux projets.
Ce projet était écrit en Framework 4.0 mais les objets utilisés devrait déjà exister en 1.1

Code:

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
using (SqlConnection connection = new SqlConnection(connectionString))
{
    String query = "SELECT NumeroElement, Info1, Info2 ... FROM Element";
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        using (SqlCommandBuilder builder = new SqlCommandBuilder())
        {
            builder.DataAdapter = adapter;
            using (DataTable table = new DataTable())
            {
                /* CA1306 (Globalization) 
                 * Set Local for data type */
                table.Locale = CultureInfo.InvariantCulture;
 
                adapter.Fill(table);
 
                DataRow row = table.NewRow();
                row[0] = Element.Numero;
                row[1] = Element.Info1;
                row[2] = Element.Info2;
                ...
 
                table.Rows.Add(row);
                adapter.Update(table);
            }
        }
    }
}

19/09/2017, 17h19
lucasgensac

Bonjour,

J'ai avancé sur mon programme : j'ai réussi la désérialisation, qui dans tous les cas m'aide pour une partie du programme, mais je vais sans doute devoir aussi utiliser la gestion XML de SQL server par procédure stockée. Du coup François, si j'ai bien compris ce que tu me conseillais, il faut que j'appelle la procédure stockée avec comme paramètre le contenu du XML sous forme de string (du côté C#) pour le "convertir" en XML côté SQL. Ma question est la suivante : y'a-t-il une limite de taille à cette string ? Dans les mauvais jours mon fichier XML peut peser jusqu'à 30 Mo, j'imagine que ce serait risqué de vouloir tout passer d'un coup, du coup quelle serait la meilleure option ?

J'imagine qu'il faut trouver un compromis entre envoyer une requête à la procédure stockée à chaque nouvel élément d'une part, et envoyer tout le fichier d'un coup d'autre part ; est-ce que vous auriez une idée de vers où se trouve le compromis ou alors est-ce que c'est le genre de trucs qu'on ne peut trouver qu'empiriquement ?

Merci.
19/09/2017, 17h23
François DORIN

La taille du XML côté SQL server est limité à 2Go. Je pense donc que tu as de la marge ;)

30Mo, cela passe sans soucis. C'est à peu près la taille du fichier que j'importe quotidiennement dans un de mes projets, et le parsing + l'import me prend une 40aine de secondes (tout en sachant que je fais des traitements style conversion, recherche de doublons, etc...). Donc franchement non, aucun soucis à avoir de ce côté là.
19/09/2017, 17h29
lucasgensac

Ah oui d'accord, en effet y'a de la marge :)

Donc si je récapitule, du côté C# je rentre tout le fichier XML dans une string que je passe en paramètre à la procédure stockée, qui elle s'occupe de le "convertir" à nouveau en XML pour le rentrer dans une table, c'est bien ça ?
19/09/2017, 17h31
François DORIN

C'est ça. De mémoire, on peut lui passer aussi directement une variable de type XElement me semble-t-il. Je n'ai pas tester avec des XmlDocument et autre.