Discussion :

[ni0urk]

Bonjour,

je dois récupérer des données au sein d'un fichier XML. Je cherche actuellement comment faire sur le net, et j'ai un peu de mal à m'en sortir en les méthodes SSIS ou encore BULK.

Si je ne me trompe pas, il existe une méthode VALUE() qui me permet de récupérer une valeur du code XML en en tant que type de données natif SQL Server. Le problème c'est que je ne sais où et comment renseigner le chemin d'accés à mon fichier XML dans ma procédure. Après, j'ai bien compris qu'il fallait fournir la représentation de l'arborescence de mon fichier XML pour pouvoir traiter le fichier ensuite.

Merci de votre aide éventuelle.

[StephIntra]

Si tu connais la structure de ton fichier XML, il existe 2 méthodes pour lire ou manipuler les données XML :

1) dans une procstock utilisateur, travailler en mémoire le fichier xml pour le charger en table:

1.1) appel de sp_xml_preparedocument pour avoir en mémoire une représentation de l'arborescence du fichier

1.2) utiliser le T-Sql OPENXML pour récupérer les données et les stocker dans les tables qui vont bien

1.3) libérer la mémoire avec sp_xml_removedocument

2) utiliser le nouveau type de données XML :

2.1) créer la table avec le type XML

2.2) charger la table avec le fichier

2.3) utiliser les méthodes colonne.query(), colonne.value() pour extraire et remplir d'autres tables

_

[Ptolémée]

1) dans une procstock utilisateur, travailler en mémoire le fichier xml pour le charger en table:

1.1) appel de sp_xml_preparedocument pour avoir en mémoire une représentation de l'arborescence du fichier

1.2) utiliser le T-Sql OPENXML pour récupérer les données et les stocker dans les tables qui vont bien

1.3) libérer la mémoire avec sp_xml_removedocument

_

J'ai essayé cette méthode. Quand on écrit le fichier XML dans une variable, il n'y a pas de pb. Mais mon fichier change à chaque fois, je ne peux pas l'écrire dans le code. Et je n'arrive pas à lier mon fichier à la procédure :

EXEC sp_xml_preparedocument @idoc OUTPUT, 'C:\fichier.xml'

ne fonctionne pas.
Comment faire ?

Merci

[SQLpro]

Il ne s'agit pas de mettre un nom de fichier dans le dernier paramètre, mais le FLUX xml directement....

Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
EXEC sp_xml_preparedocument @idoc OUTPUT, '<?xml version="1.0" encoding="UTF-8"?>
  <!-- '''Commentaire''' -->
  <élément-document xmlns="http://exemple.org/" xml:lang=";fr">
    <élément>Texte</élément>
    <élément>élément répété</élément>
    <élément>
      <élément>Hiérarchie récursive</élément>
    </élément>
    <élément>Texte avec<élément>élément</élément>mêlé</élément>
    <élément/><!-- élément vide -->
    <élément attribut="valeur"></élément>
  </élément-document>'

A +

[Ptolémée]

Merci de votre réponse.

Oui, j'avais fini par arriver à cette conclusion, à force de tester.

Mais quelle est la taille limite du flux.

Je devrais avoir un fichier de 80.000 lignes, ça va passer ?

Merci

[ylarvor]

"Mais quelle est la taille limite du flux."

sur sql serveur 2005, le stockage texte et xml peuvent monter jusqu'à 2 giga et la profondeur maximale est de 128 noeuds; OPENXML peut gérer ces formats en SQL Serveur 2005.

référence : Guide 70-431 Microsoft.

J'ai essayé cette méthode. Quand on écrit le fichier XML dans une variable, il n'y a pas de pb. Mais mon fichier change à chaque fois, je ne peux pas l'écrire dans le code. Et je n'arrive pas à lier mon fichier à la procédure :

EXEC sp_xml_preparedocument @idoc OUTPUT, 'C:\fichier.xml'

vous devez utiliser la fonction OPENROWSET pour lire un fichier XML.

si vous en avez plusieurs, mieux vaut programmer SSIS.

référence : Guide 70-431 Microsoft.

[Ptolémée]

Merci beaucoup ylarvor,

j'ai été voir dans la doc msdn. Cela semble correspondre à ce que j'attendais. Maintenant il me reste à l'appliquer. Pas si simple, mais il suffit de s'y mettre

Je viendrais ajouter un message ici quand j'aurais été au bout de la solution.

Merci à SQLpro et ylarvor de vos réponses rapides. Cela est une aide précieuse !

[Ptolémée]

Ca y est ! Je suis arrivé au bout.

Après avoir fait ma procédure avec sp_xml_preparedocument, je me trouvais bien embêté car il me fallait un flux en entrée.

J'ai résolu le problème en créant une table temporaire une ligne avec colonne de type XML :

Code :

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

CREATE TABLE tmpXml (XmlCol XML)

Puis je charge ma table avec mon fichier via OPENROWSET

Code :

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

1
2
INSERT INTO tmpXml(ColXml) 
SELECT * FROM OPENROWSET(BULK 'C:\fic.xml', SINGLE_BLOB) AS x

Ca y est ! j'ai mon fichier xml dans une colonne de ma base, je n'ai plus qu'à aller la chercher et la mettre en entrée de sp_xml_preparedocument :

Code :

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

select @doc = ColXml from tmpXml

Oups ! ça ne marche pas ! Il me faut une chaîne en entrée pas un type XML :

Code :

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

select @doc = convert(nvarchar(1000), ColXml) from tmpXml

Puis :

Code :

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

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Ne pas oublier de faire un curseur pour traiter tous les noeuds avec un SELECT OPENXML :

Code :

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

1
2
3
4
5
6
7
SELECT     code, nom, siret,  tel, groupe
 FROM     OPENXML (@idoc, '/ROOT/client',2) WITH
	(code NVARCHAR(50),
	nom NVARCHAR(20),
	siret NVARCHAR(20),
	tel NVARCHAR(20),
	groupe NVARCHAR(20))

Il me restait un problème : je voulais passer le chemin d'accès du fichier en paramètre de la procédure. Et si je fais :

Code :

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

1
2
INSERT INTO tmpXml(ColXml) 
SELECT * FROM OPENROWSET(BULK @ficPath, SINGLE_BLOB) AS x

Ca ne marche pas !

La solution est :

Code :

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

1
2
3
declare @vsql varchar(2000)
select @vsql = 'INSERT INTO tmpXml(ColXml) SELECT * FROM OPENROWSET(BULK ''' + @ficPath+ ''', SINGLE_BLOB) AS x'
exec   (@vsql)

Et maintenant tout est OK

Bon, pour beaucoup d'entre vous tout ça c'est évident, mais je vous assure que ça ne l'était pas pour moi.

à ceux qui m'ont aidé et à la communauté.

[Fleur-Anne.Blain]

penser au quand le problème est réglé.