BCP Export XML

 

L’utilitaire BCP (Bulk Copy Command) copie en bloc des données entre une instance Microsoft SQL Server et un fichier. Il permet d’importer ou d’exporter facilement vos données depuis une ligne de commande ou depuis une requête T-SQL.

Besoin client : Des transactions sont transmises via des fichiers XML d’une société externe à un Middle Office chez mon client. Les fichiers transitent dans un système de publication, d’intégration et de réconciliation. Des XSD valident à chaque étape l’architecture des fichiers.

Problème : L’équipe de recette a remonté un Defect dans TFS, après avoir modifié à la main des transactions l’erreur suivante bloque le processus d’import / export :

Le fichier XML n’est pas validé par le XSD en raison d’un caractère non valide : le « é » de « Intermarché » 🙁

 

Résolution

Après quelques tests je me suis rendu compte qu’il suffisait de convertir l’encodage du fichier à importer pour que celui-ci passe le contrôle XSD (de AINSI à UTF-8).

 

Les formats d’encodage :

• ASCII (American Standard Code for Information Interchange) : Les caractères sont codés sur 7 bits (0 ou 1). Il permet de représenter 128 caractères différents (1111111 en binaire) ce qui est suffisant pour mémoriser notre alphabet, les chiffres, les éléments de ponctuation, … mais pas suffisant pour stocker les caractères spéciaux (accents, cédilles, …)
• ISO 8859-1 (Latin-1) : Les caractères sont codés sur 8 bits. Il permet donc de représenter 256 caractères différents ce qui est suffisant pour mémoriser notre alphabet latin avec les caractères spéciaux
• ISO 8859-15 (Latin-9) : C’est une variante du Latin-1, il ajoute notamment le caractère euro (€) et les caractères œ et Œ à la place de certains caractères peu utilisés
• Le Windows-1252 (AINSI) : C’est un encodage propre aux ordinateurs Windows codé sur 8 bits mais certains caractères diffèrent du codage ISO
• Le MacRoman : C’est un encodage propre aux ordinateurs Apple Macintos codé sur 8 bits et certains caractères différents des autres !
• UTF-8 : C’est un encodage dit « Unicode ». L’Unicode vise à donner à tout caractère quelque soit son alphabet (latin, cyrillique, asiatique…) un code unique et compatible sur toutes les plateformes (Windows, Mac, Unix). L’UTF-8 est codé sur 8 bits
• UTF-16 : C’est un encodage dit « Unicode ». L’UTF-16 est codé sur 16 bits ce qui permet d’encoder un nombre de caractères quasi illimité
• …

 

Bulk Copy Command :

La commande BCP permet à l’aide du paramètre ‘out’ d’exporter toute une table et d’exporter le résultat d’une requête en spécifiant le paramètre ‘queryout’.

Exporter une table : bcp.exe mydatabase.dbo.myTable out export.bcp -f format.fmt -S server\instance -U account -P password

Exporter le résultat d’une requête : bcp.exe « SELECT * from mydatabase.dbo.myTable WHERE valTime >= ‘2013-07-01′ » queryout export.bcp -f format.fmt -S server\instance -U account -P password

Ainsi la requête suivante extrait de la table « myTable » les données de la colonne « myXMLColumn » :

DECLARE @RequestText as nvarchar(255);
DECLARE @SQLCmd as nvarchar(255);
DECLARE @GeneratedFile as nvarchar(255);

SET @GeneratedFile = 'C:\myExport.xml';

SET @RequestText = 'SELECT myXMLColumn FROM dbo.myTable';

SET @SQLCmd = 'bcp "' + @RequestText + '"' + 
                  ' queryout "' + @GeneratedFile + '" ' +
                  '-T -S ' + @@servername + ' ' +
                  '-d myDatabase -m 1000 -c -C ACP -r -t -x';

EXEC master..xp_cmdshell @SQLCmd;

Le paramètre “-C ACP” encode les données en AINSI pour garder les caractères accentués.

Voici les différentes options d’encodage des données :
• ACP : ANSI/Microsoft Windows (ISO 1252).
• OEM : Encodage Défaut utilisé par le client (Equivaut a ne pas utiliser le paramètre -C)
• RAW : Aucun encodage ne sera fait
• code_page : Permet de spécifier un numéro d’encodage

Remarques : La commande BCP est case sensitive. Vous devez activer la commande xp_cmdshell pour l’utiliser depuis SSMS.

 

PowerShell :

Windows PowerShell est le successeur des interfaces en ligne de commande DOS il permet la gestion et l’administration des systèmes d’exploitation, et sert aussi pour l’administration de certains produits tels que Microsoft Exchange ou encore Microsoft SQL Server.

Pour modifier depuis une procédure T-SQL l’encodage du fichier à exporter j’ai utilisé le script suivant :

DECLARE @PWSCommand as nvarchar(4000);
DECLARE @Result as nvarchar(4000);

SET  @PWSCommand = 'powershell -Command "Get-Content ''' + @GeneratedFile 
           + ''' | Out-File -Encoding UTF8 ''' + @GeneratedFile;

EXEC @Result = master..xp_cmdshell @PWSCommand;