insérer un fichier XML dans une table SQL Server 2005 sans SSIS

[sql_bin]

Bonjour,
je cherche depuis ce matin un moyen pour insérer un fichier XML dans une table SQL Server, sans passer par SSIS... rien

le but de la manœuvre est d'enrichir ce fichier avec en rajoutant quelques informations depuis une autre Table (mais cela sera plus facile une fois le XML dans une table Tempo).

voila un bout de mon fichier:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<Fichier ANNEE="2010" NUMERO="0177" DATE="16/04/10">
        <Demande ANNEE="2009" NUMERO="21444" DATE_DEMANDE="08/04/10">
                <Demandeur TITRE="Monsieur" NOM="******" PRENOM="**********" ADRESSE1="*****" ADRESSE2="" CP="86110" VILLE="MIREBEAU"/>
                <Demandeur TITRE="Monsieur" NOM="******" PRENOM="*******" ADRESSE1="*****" ADRESSE2="" CP="86170" VILLE="MASSOGNES"/>
                <Representant TITRE="Maître" NOM="****" PRENOM="*******" ADRESSE1="******" ADRESSE2="BP 14" CP="86110" VILLE="MIREBEAU"/>
                <Decede TITRE="Madame" NOM="*********" PRENOM="*****" ADRESSE1="*****" ADRESSE2="*****" CP="86" VILLE="MIREBEAU" DATE_NAISSANCE="21/08/13" CP_NAISSANCE="86" VILLE_NAISSANCE="CUHON" DATE_DECES="27/09/09" CP_DECES="86" VILLE_DECES="MIREBEAU"/>
                <Documents>
                        <Document NUMERO="1" DATE_AGIRA="16/12/09 12:10:05" FICHIER="2009_21444_1.TIF"/>
                        <Document NUMERO="2" DATE_AGIRA="15/04/10 15:22:04" FICHIER="2009_21444_2.TIF"/>
                </Documents>
        </Demande>
<Demande ANNEE="2010" NUMERO="7344" DATE_DEMANDE="07/04/10">
                <Demandeur TITRE="Monsieur" NOM="*****" PRENOM="*********" ADRESSE1="******" ADRESSE2="*******" CP="07200" VILLE="ST ETIENNE DE FONTBELLON"/>
                <Decede TITRE="Monsieur" NOM="*****" PRENOM="*******" ADRESSE1="******" ADRESSE2="" CP="13006" VILLE="MARSEILLE" DATE_NAISSANCE="12/11/23" CP_NAISSANCE="13" VILLE_NAISSANCE="MARSEILLE" DATE_DECES="03/03/10" CP_DECES="13006" VILLE_DECES="MARSEILLE"/>
                <Documents>
                        <Document NUMERO="1" DATE_AGIRA="16/04/10 11:15:37" FICHIER="2010_7344_1.TIF"/>
                </Documents>
        </Demande>
</Fichier>

La table Tempo sera de la sorte:
Fichier|F_ANNEE| F_NUMERO|F_DATE|Demande|D_ANNEE|D_NUMERO|DATE_DEMANDE|Info_Demandeur|Info_Representant|Info_Decede|Doc1|Doc2

Le schéma de la table est a titre indicatif

Merci d'avance

[elsuket]

Bonjour,

SQL Server 2005 a introduit le type de données XML.
Donc vous pouvez tout à fait créer une colonne ayant une colonne de ce type (ce qui impose au moins que tout document XML stocké dans une telle colonne soit correctement formé).
Notez au passage que vous pouvez également vérifier votre document XML en enregistrant un schéma (CREATE XML SCHEMA COLLECTION), pour typer plus fortement votre colonne.

Dans votre cas vous pouvez par exemple écrire une procédure stockée qui prenne en paramètre un document XML, et dépouiller celui-ci à l'aide de XQuery .
Un exemple pour débuter :

Code :

1
2
3
4
5
6
7
8
SELECT		fichier.value('(/Fichier/@ANNEE)[1]', 'int') AS annee_fichier
		, fichier.value('(/Fichier/@NUMERO)[1]', 'int') AS numero_fichier
		, CONVERT(datetime, fichier.value('(/Fichier/@DATE)[1]', 'varchar(8)'), 3) AS date_fichier
		, demande.value('(@ANNEE)[1]', 'int') AS annee_demande
		, demande.value('(@NUMERO)[1]', 'int') AS numero_demande
		, CONVERT(datetime, demande.value('(@DATE_DEMANDE)[1]', 'varchar(8)'), 3) AS date_demande
FROM		@toto.nodes('/Fichier') AS F(fichier)
CROSS APPLY	@toto.nodes('/Fichier/Demande') AS D(demande)

@++

[SQLpro]

Pour insérer directement un fichier XML dans une table :

Code :

1
2
3
INSERT INTO MaTable (MaColonneXML)
SELECT * 
FROM   OPENROWSET(BULK 'c:\Monfichier.xml', SINGLE_BLOB) AS T

A +

[sql_bin]

@elsuket:
merci pour Votre aide cela marche MAIS ()

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
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
Declare @MonDoc XML
SELECT @MonDoc = BulkColumn
FROM Openrowset(Bulk 'C:\Ass_Vie.xml',SINGLE_BLOB)AS T
--insert into dbo.Temp_xml_titi 
--([ANNEE_Fichier] ,
--[NUMERO_Fichier] ,
--[DATE_Fichier] ,
--[ANNEE_DEMANDE] ,
--[NUMERO_DEMANDE] ,
--[DATE_DEMANDE] ,
--[TITRE_Demandeur1] ,
--[NOM_Demandeur1] ,
--[PRENOM_Demandeur1] ,
--[ADRESSE1_Demandeur1] ,
--[ADRESSE2_Demandeur1] ,
--[CP_Demandeur1] ,
--[VILLE_Demandeur1] ,
--[TITRE_Demandeur2] ,
--[NOM_Demandeur2] ,
--[PRENOM_Demandeur2] ,
--[ADRESSE1_Demandeur2] ,
--[ADRESSE2_Demandeur2] ,
--[CP_Demandeur2] ,
--[VILLE_Demandeur2] ,
--[TITRE_Demandeur3] ,
--[NOM_Demandeur3] ,
--[PRENOM_Demandeur3] ,
--[ADRESSE1_Demandeur3] ,
--[ADRESSE2_Demandeur3] ,
--[CP_Demandeur3] ,
--[VILLE_Demandeur3] ,
--[TITRE_Demandeur4] ,
--[NOM_Demandeur4] ,
--[PRENOM_Demandeur4] ,
--[ADRESSE1_Demandeur4] ,
--[ADRESSE2_Demandeur4] ,
--[CP_Demandeur4] ,
--[VILLE_Demandeur4] ,
--[TITRE_Representant] ,
--[NOM_Representant] ,
--[PRENOM_Representant] ,
--[ADRESSE1_Representant] ,
--[ADRESSE2_Representant] ,
--[CP_Representant] ,
--[VILLE_Representant] ,
--[TITRE_Decede] ,
--[NOM_Decede] ,
--[PRENOM_Decede] ,
--[ADRESSE1_Decede] ,
--[ADRESSE2_Decede] ,
--[CP_Decede] ,
--[VILLE_Decede] ,
--[DATE_NAISSANCE_Decede] ,
--[CP_NAISSANCE_Decede] ,
--[VILLE_NAISSANCE_Decede] ,
--[DATE_DECES_Decede] ,
--[CP_DECES_Decede] ,
--[VILLE_DECES_Decede] ,
--[Document1] ,
--[Document2])
SELECT Fichier.value('(./@ANNEE)[1]','varchar(4)') AS ANNEE_Fichier,
    Fichier.value('(./@NUMERO)[1]','varchar(4)') AS NUMERO_Fichier,
    Fichier.value('(./@DATE)[1]','varchar(8)') AS DATE_Fichier,
    Demande.value('(./@ANNEE)[1]','varchar(4)') AS ANNEE_DEMANDE,
    Demande.value('(./@NUMERO)[1]','varchar(5)') AS NUMERO_DEMANDE,
    Demande.value('(./@DATE_DEMANDE)[1]','varchar(8)') AS DATE_DEMANDE,
    Demandeur.value('(./@TITRE)[1]','varchar(12)') AS TITRE_Demandeur,
    Demandeur.value('(./@NOM)[1]','varchar(25)') AS NOM_Demandeur,
    Demandeur.value('(./@PRENOM)[1]','varchar(25)') AS PRENOM_Demandeur,
    Demandeur.value('(./@ADRESSE1)[1]','varchar(38)') AS ADRESSE1_Demandeur,
    Demandeur.value('(./@ADRESSE2)[1]','varchar(38)') AS ADRESSE2_Demandeur,
    Demandeur.value('(./@CP)[1]','varchar(5)') AS CP_Demandeur,
    Demandeur.value('(./@VILLE)[1]','varchar(38)') AS VILLE_Demandeur,
    Representant.value('(./@TITRE)[1]','varchar(12)') AS TITRE_Representant,
    Representant.value('(./@NOM)[1]','varchar(25)') AS NOM_Representant,
    Representant.value('(./@PRENOM)[1]','varchar(25)') AS PRENOM_Representant,
    Representant.value('(./@ADRESSE1)[1]','varchar(38)') AS ADRESSE1_Representant,
    Representant.value('(./@ADRESSE2)[1]','varchar(38)') AS ADRESSE2_Representant,
    Representant.value('(./@CP)[1]','varchar(5)') AS CP_Representant,
    Representant.value('(./@VILLE)[1]','varchar(38)') AS VILLE_Representant,
    Decede.value('(./@TITRE)[1]','varchar(38)') AS TITRE_Decede,
    Decede.value('(./@NOM)[1]','varchar(38)') AS NOM_Decede,
    Decede.value('(./@PRENOM)[1]','varchar(38)') AS PRENOM_Decede,
    Decede.value('(./@ADRESSE1)[1]','varchar(38)') AS ADRESSE1_Decede,
    Decede.value('(./@ADRESSE2)[1]','varchar(38)') AS ADRESSE2_Decede,
    Decede.value('(./@CP)[1]','varchar(38)') AS CP_Decede,
    Decede.value('(./@VILLE)[1]','varchar(38)') AS VILLE_Decede,
    Decede.value('(./@DATE_NAISSANCE)[1]','varchar(38)') AS DATE_NAISSANCE_Decede,
    Decede.value('(./@CP_NAISSANCE)[1]','varchar(38)') AS CP_NAISSANCE_Decede,
    Decede.value('(./@VILLE_NAISSANCE)[1]','varchar(38)') AS VILLE_NAISSANCE_Decede,
    Decede.value('(./@DATE_DECES)[1]','varchar(38)') AS DATE_DECES_Decede,
    Decede.value('(./@CP_DECES)[1]','varchar(38)') AS CP_DECES_Decede,
    Decede.value('(./@VILLE_DECES)[1]','varchar(38)') AS VILLE_DECES_Decede,
    Documents.value('(./@NUMERO)[1]','varchar(38)') AS NUMERO_Document,
    Documents.value('(./@DATE_AGIRA)[1]','varchar(38)') AS DATE_AGIRA_Document,
    Documents.value('(./@FICHIER)[1]','varchar(38)') AS FICHIER_Document
 
FROM @MonDoc.nodes('/Fichier') AS f (Fichier)
CROSS apply @MonDoc.nodes('/Fichier/Demande') AS d (Demande)
CROSS apply @MonDoc.nodes('/Fichier/Demande/Demandeur') AS dem (Demandeur) 
CROSS apply @MonDoc.nodes('/Fichier/Demande/Representant') AS rep (Representant) 
CROSS apply @MonDoc.nodes('/Fichier/Demande/Decede') AS dece (Decede)
CROSS apply @MonDoc.nodes('/Fichier/Demande/Documents/Document') AS doc (Documents)

1/comment mettre ces donnée dans ma Table Temp_xml_titi

2/j'ai des doublons dans le (je sais pas comment on appel ça... le truc en bas.. il ressemble a une table) .... et le num demande ne correspond pas aux demandeurs (nœud parent nœud fils).... le num fichier (un seul est le même selon mon exemple) pour toutes les lignes mais pas le num demande (je dois avoir deux selon mon exemple.)!!!

3/est ce que je peux faire une boucle sur un dossier pour lire tout les fichiers XML et à chaque fois l'insérer dans ma table
4/ une petit explication du chiffre qui est entre crochées [] sera vraiment suuuper



@SQLpro: j'ai essayé mais j'ai eu un message d'erreur

Code :

1
2
Msg 213, Level 16, State 1, Line 1
INSERT Error: COLUMN name OR number of supplied VALUES does NOT match TABLE definition.

[SQLpro]

Le message est clair : vous n'avez pas le même nombre de colonne dans la table cible et dans la table source.

A +

[sql_bin]

Merci SQL PRO,
j'ai fini par créer 4 tables pour chaque noeud enfants (Demandeur, Representant, Decede et Documents) du noeud demande .
et donc mon fichier XML me charge maintenant 4 Tables.

j'ai juste klk questions:

1/ Comment rendre dynamique le traitement sur tout un dossier qui contient plusieurs fichier XML.
2/ Est ce que je peux, dans la même requête charger mes 4 tables ou je dois le faire une par une.
merci et bonne journée