Import fichier XML dans table Transact SQL

Version imprimable

16/06/2009, 11h36
chicken92000

Import fichier XML dans table Transact SQL
Bonjour,

Je suis à la recherche de code qui va permette d'importer un fichier dans une table.

Je connais le fonction bulk insert pour les fichiers texte; mais j'essaye maintenant de faire la même chose avec un fichier XML; et cela ne fonctionne pas

J'ai commencé par le code ci-dessous
Code:

1 2 insert into test(col1 ,col2 ,col3 ,col4 ,col5 ,col5 ) select * from OPENROWSET(BULK 'C:\Projects\test.xml', SINGLE_BLOB) as X
Mais lorsque je lance la commande j'ai une erreur

Citation:

La liste de sélection de l'instruction INSERT contient moins d'éléments que la liste d'insertion. Le nombre de valeurs de SELECT doit être le même que le nombre de colonnes de INSERT.

Et dans mon fichier l'on a

Citation:

<?xml version="1.0" encoding="UTF-8" ?><DATAROOT><BANK><col1>Test1</col2><col2>Test2</col2><col3>Test3</col3><col4>Test4</col4><col5>TEst5</col5><col6>2008-01-28</col6></BANK></DATAROOT>

Merci par avance de votre aide.

David
16/06/2009, 12h00
elsuket

Bonjour,

Il y a 6 colonnes dans votre ordre INSERT, mais la commande OPENROWSET BULK ne renvoie toujours qu'une seule colonne, d'où votre erreur.

Vous devriez plutôt stocker votre document XML dans une variable de type XML, puis extraire les valeurs avec les méthodes XML .nodes(), .value() et .query().

Le paramètre qui convient alors pour OPENROWSET BULK devrait plutôt être SINGLE_CLOB

@++ ;)
16/06/2009, 13h14
chicken92000

Merci pour ta réponse mais aurais tu un exemple car j'ai du mal à voir comment le mettre en place.

Merci

David

Par exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @monDocument XML
SELECT @monDocument = BulkColumn
FROM OPENROWSET(BULK 'C:\Projects\test.xml', SINGLE_CLOB) AS X
 
INSERT INTO dbo.test
(
	col1,
	col2,
	col3,
	col4,
	col5,
	col6
)
SELECT colonne.value(val1),
		colonne.value('./val2[1]', 'TypeDeDonnes'),
		colonne.value('./val3[1]', 'TypeDeDonnes'),
		colonne.value('./val4[1]', 'TypeDeDonnes'),
		colonne.value('./val5[1]', 'TypeDeDonnes'),
		colonne.value('./val6[1]', 'TypeDeDonnes')
FROM @monDocument.nodes('/cheminDesValeurs') TMP_XML (colonne)

Regardez d'abord ce que vous ramène @monDocument.nodes('/cheminDesValeurs').

Utilisez XMLNotePad pour vous aider ;)

@++ ;)

Merci beaucoup pour ton exemple

J'ai essayé de l'adapter mais j'ai encore des petits soucis par rapport à mon exemple
Code:

1 2 3 4 5 6 7 8 9 10 DECLARE @monDocument XML SELECT @monDocument = BulkColumn FROM OPENROWSET(BULK ''C:\Projects\test.xml'', SINGLE_CLOB) AS X SELECT colonne.value('./col1[1]', 'varchar'), colonne.value('./col2[1]', 'varchar'), colonne.value('./col3[1]', 'varchar'), colonne.value('./col4[1]', 'varchar'), colonne.value('./col5[1]', 'varchar'), colonne.value('./col6[1]', 'varchar') FROM @monDocument.nodes('/DATAROOT/BANK') TMP_XML (colonne)
Mais cela me retourne

Citation:

Msg 9448, Level 16, State 1, Line 2
Analyse XML*: ligne 1, caractère 762, contrôle correct*: entité non déclarée

David

J'ai pu corrigé quelques erreurs.
Code:

1 2 3 4 5 6 7 8 9 10 11 DECLARE @monDocument XML; SELECT @monDocument = BulkColumn FROM OPENROWSET(BULK 'C:\test.xml', SINGLE_CLOB) AS X --INSERT INTO dbo.EPC(Country ,BankName ,Address ,City ,BIC ,ReadinessDate ) SELECT colonne.value('./Col1[1]', 'varchar'), colonne.value('./Col2[1]', 'varchar'), colonne.value('./Col3[1]', 'varchar'), colonne.value('./Col4[1]', 'varchar'), colonne.value('./Col5[1]', 'varchar'), colonne.value('./Col6[1]', 'varchar') FROM @monDocument.nodes('/DATAROOT/BANK') TMP_XML (colonne)
Mais j'ai deux problème:

- Pour chaque champs il me retourne que la premiere lettre
- Si dans mon fichier j'ai plus qu'un groupe bank (voir exemple ci-dessous) j'ai l'erreur ci-dessous

Citation:

<?xml version="1.0" encoding="UTF-8" ?><DATAROOT><BANK><col1>Test1</col1><col2>Test2</col2><col3>Test3</col3><col4>Test4</col4><col5>TEst5</col5><col6>2008-01-28</col6></BANK><BANK><col1>Test11</col1><col2>Test12</col2><col3>Test13</col3><col4>Test14</col4><col5>TEst15</col5><col16>2008-01-28</col6></BANK></DATAROOT>

Citation:

Msg 9448, Level 16, State 1, Line 2
Analyse XML*: ligne 1, caractère 762, contrôle correct*: entité non déclarée

16/06/2009, 18h23
SQLpro

1) VARCHAR n'est pas un type SQL. Sans la dimension VARCHAR vaut 1. Vous devez préciser la dimension du VARCHAR. Par exemple VARCHAR(64).

2) dans ce cas il faut mettre [1] je crois en fin du path

3) vous gagnerez en perf en utilisant sp_xml_preparedocument.

A +
17/06/2009, 09h30
chicken92000

Merci pour la réponse pour les varchar et cela a résolu mon premier problème.

Mais j'ai toujours un problème si mon fichier contient plus d'un enregistrement bank (correspondant à une ligne dans ma table) alors j'ai une erreur (voir ci-dessous)

Citation:

Msg 9448, Level 16, State 1, Line 2
Analyse XML*: ligne 1, caractère 762, contrôle correct*: entité non déclarée

Mais si j'ai uniquement un seul enregistrement tout se passe bien.

Dois je faire une boucle pour traiter tout le fichier?

Merci

David
17/06/2009, 09h41
chicken92000

Merci a tous j'ai trouvé mon erreur dans le fichier d'import j'avais des erreurs.

David