Exemples d'importation et d'exportation en bloc de documents XML (SQL Server)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

Vous pouvez importer en bloc des documents XML vers une base de données SQL Server ou les exporter en bloc à partir d’une base de données SQL Server. Cet article fournit des exemples de ces deux.

Pour importer des données en bloc à partir d'un fichier de données dans une table ou une vue non partitionnée SQL Server, vous pouvez utiliser les méthodes suivantes :

• Utilitaire bcp

  Vous pouvez aussi faire appel à l’utilitaire bcp pour exporter des données à partir de n’importe quel emplacement d’une base de données SELECT contenant une instruction SELECT, vues partitionnées comprises.

• BULK INSERT

• INSERT ... SELECT * FROM OPENROWSET(BULK...)

Pour plus d’informations, consultez les articles suivants :

• Importer et exporter des données en bloc avec BCP (SQL Server)
• Utiliser BULK INSERT ou OPENROWSET(BULK...) pour importer des données dans SQL Server
• Comment importer XML dans SQL Server avec le composant de chargement en masse XML.
• Collections de schémas XML (SQL Server)

Exemples

• R. Importation en bloc de données XML sous forme de flux d'octets binaires
• B. Importation en bloc de données XML dans une ligne existante
• C. Importation en bloc de données XML à partir d’un fichier contenant une DTD
• D. Spécification explicite de la marque de fin de champ à l’aide d’un fichier de format
• E. Exportation en bloc de données XML

Importation en bloc de données XML sous forme de flux d'octets binaires

Si vous importez en bloc des données XML à partir d'un fichier contenant la déclaration d'encodage à appliquer, spécifiez l'option SINGLE_BLOB dans la clause OPENROWSET(BULK...). Cette option SINGLE_BLOB garantit que l'analyseur XML du serveur SQL importe les données conformément au schéma d'encodage spécifié dans la déclaration XML.

Exemple de table

Pour tester l’exemple A ci-dessous, créez l’exemple de table T.

SQL

USE tempdb;
GO

CREATE TABLE T (
    IntCol INT IDENTITY(1,1),
    XmlCol XML
    );
GO

Exemple de fichier de données

Avant de pouvoir exécuter l’exemple A, vous devez créer un fichier encodé en UTF-8 (C:\SampleFolder\SampleData3.txt) : ce dernier contient l’instance d’exemple suivante qui spécifie le schéma d’encodage UTF-8 .

XML

<?xml version="1.0" encoding="UTF-8"?>
<Root>
    <ProductDescription ProductModelID="5">
        <Summary>Some Text</Summary>
    </ProductDescription>
</Root>

Exemple A

Cet exemple utilise l'option SINGLE_BLOB dans une instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) pour importer des données à partir d'un fichier nommé SampleData3.txt et insérer une instance XML dans la table de colonne unique, la table d'exemple T.

SQL

INSERT INTO T (XmlCol)
SELECT *
FROM OPENROWSET(
    BULK 'C:\SampleFolder\SampleData3.txt',
    SINGLE_BLOB)
AS x;

Vous pouvez également spécifier explicitement les noms de colonnes comme suit :

SQL

INSERT INTO T (
    XmlCol
)
SELECT
    x.BulkColumn
FROM OPENROWSET(
    BULK 'C:\SampleFolder\SampleData3.txt',
    SINGLE_BLOB)
AS x;

Notes

En utilisant SINGLE_BLOB dans ce cas, vous évitez toute discordance entre l'encodage du document XML (tel qu'il est spécifié par la déclaration d'encodage XML) et la page de codes de type chaîne inhérente au serveur.

Si vous utilisez les types de données NCLOB ou CLOB et que vous rencontrez un conflit de pages de codes ou d'encodage, effectuez l'une des opérations suivantes :

• supprimer la déclaration XML pour réussir l'importation du contenu du fichier de données XML ;

• Spécifier une page de code dans l'option CODEPAGE de la requête qui correspond au schéma d'encodage utilisé dans la déclaration XML.

• faire coïncider, ou corriger, les paramètres de classement avec un schéma d'encodage XML non-Unicode.

[Haut]

Importation en bloc de données XML dans une ligne existante

Cet exemple utilise le fournisseur d'ensemble de lignes en bloc OPENROWSET pour ajouter une instance XML à une ligne ou des lignes existantes dans la table d'exemple T.

Notes

Pour exécuter cet exemple, vous devez d'abord terminer le script de test de l'exemple A. Cet exemple crée la table tempdb.dbo.T et importe des données en bloc à partir de SampleData3.txt.

Exemple de fichier de données

L'exemple B utilise une version modifiée du fichier de données d'exemple SampleData3.txt à partir de l'exemple précédent. Pour exécuter cet exemple, modifiez le contenu de ce fichier comme suit :

XML

<Root>
    <ProductDescription ProductModelID="10">
        <Summary>Some New Text</Summary>
    </ProductDescription>
</Root>

Exemple B

SQL

-- Query before update shows initial state of XmlCol values.
SELECT * FROM T;

UPDATE T
SET XmlCol = (
    SELECT *
    FROM OPENROWSET(
    BULK 'C:\SampleFolder\SampleData3.txt',
            SINGLE_BLOB
    ) AS x
)
WHERE IntCol = 1;
GO

[Haut]

Importation en bloc de données XML à partir d’un fichier contenant une DTD

Important

Il est recommandé de ne pas activer la prise en charge des définitions de type de document (DTD) si celle-ci n'est pas nécessaire à votre environnement XML. En effet, son activation augmente la zone de surface attaquable de votre serveur qui peut se retrouver exposé à une attaque de déni de service. Si vous devez activer la prise en charge DTD, vous pouvez réduire ce risque lié à la sécurité en traitant uniquement des documents XML approuvés.

Vous pouvez obtenir l’erreur suivante lorsque vous utilisez bcp pour importer des données XML à partir d’un fichier qui contient un DTD :

Output

SQLState = 42000, NativeError = 6359

Error = [Microsoft][SQL Server Native Client][SQL Server]Parsing XML with internal subset DTDs not allowed.
Use CONVERT with style option 2 to enable limited internal subset DTD support.

BCP copy %s failed

Pour résoudre ce problème, vous pouvez importer des données XML à partir d'un fichier de données qui contient une DTD à l'aide de la fonction OPENROWSET(BULK...) et en spécifiant l'option CONVERT dans la clause SELECT de la commande. La syntaxe de base pour la commande est la suivante :

INSERT ... SELECT CONVERT(...) FROM OPENROWSET(BULK...)

Exemple de fichier de données

Avant de pouvoir tester cet exemple d’importation en bloc, créez un fichier (C:\SampleFolder\Dtdfile.xml) qui contient l’instance d’exemple suivant :

XML

<!DOCTYPE DOC [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1>January</elem1>

Exemple de table

L'exemple C utilise la table d'exemple T1 créée par l'instruction CREATE TABLE suivante :

SQL

USE tempdb;
GO
CREATE TABLE T1(XmlCol XML);
GO

Exemple C

Cet exemple utilise OPENROWSET(BULK...) et spécifie l'option CONVERT dans la clause SELECT pour importer les données XML à partir du fichier Dtdfile.xml dans la table d'exemple T1.

SQL

INSERT INTO T1
SELECT CONVERT(XML, BulkColumn, 2)
FROM OPENROWSET(
    BULK 'C:\SampleFolder\Dtdfile.xml',
    SINGLE_BLOB
) AS [rowsetresults];

Une fois l'instruction INSERT exécutée, la DTD est supprimée du code XML, puis stockée dans la table T1 .

[Haut]

Spécification explicite de la marque de fin de champ à l’aide d’un fichier de format

L'exemple suivant montre l'importation en bloc du document XML suivant, Xmltable.dat.

Exemple de fichier de données

Le document dans le fichier Xmltable.dat contient deux valeurs XML, une pour chaque ligne. La première valeur XML est encodée en UTF-16, et la seconde en UTF-8.

Le contenu de ce fichier de données est présenté dans le vidage hexadécimal suivant :

Output

FF FE 3C 00 3F 00 78 00-6D 00 6C 00 20 00 76 00  *..\<.?.x.m.l. .v.*
65 00 72 00 73 00 69 00-6F 00 6E 00 3D 00 22 00  *e.r.s.i.o.n.=.".*
31 00 2E 00 30 00 22 00-20 00 65 00 6E 00 63 00  *1...0.". .e.n.c.*
6F 00 64 00 69 00 6E 00-67 00 3D 00 22 00 75 00  *o.d.i.n.g.=.".u.*
74 00 66 00 2D 00 31 00-36 00 22 00 3F 00 3E 00  *t.f.-.1.6.".?.>.*
3C 00 72 00 6F 00 6F 00-74 00 3E 00 A2 4F 9C 76  *\<.r.o.o.t.>..O.v*
0C FA 77 E4 80 00 89 00-00 06 90 06 91 2E 9B 2E  *..w.............*
99 34 A2 34 86 00 83 02-92 20 7F 02 4E C5 E4 A3  *.4.4..... ..N...*
34 B2 B7 B3 B7 FE F8 FF-F8 00 3C 00 2F 00 72 00  *4.........\<./.r.*
6F 00 6F 00 74 00 3E 00-00 00 00 00 7A EF BB BF  *o.o.t.>.....z...*
3C 3F 78 6D 6C 20 76 65-72 73 69 6F 6E 3D 22 31  *\<?xml version="1*
2E 30 22 20 65 6E 63 6F-64 69 6E 67 3D 22 75 74  *.0" encoding="ut*
66 2D 38 22 3F 3E 3C 72-6F 6F 74 3E E4 BE A2 E7  *f-8"?><root>....*
9A 9C EF A8 8C EE 91 B7-C2 80 C2 89 D8 80 DA 90  *................*
E2 BA 91 E2 BA 9B E3 92-99 E3 92 A2 C2 86 CA 83  *................*
E2 82 92 C9 BF EC 95 8E-EA 8F A4 EB 88 B4 EB 8E  *................*
B7 EF BA B7 EF BF B8 C3-B8 3C 2F 72 6F 6F 74 3E  *.........</root>*
00 00 00 00 7A                                   *....z*

Exemple de table

Durant une importation ou une exportation en bloc d’un document XML, vous devez utiliser une marque de fin de champ qu’il est impossible de trouver dans les documents ; par exemple, une série de quatre valeurs Null (\0) suivie de la lettre z: \0\0\0\0z.

Cet exemple illustre l'utilisation de cette marque de fin de champ pour la table d'exemple xTable . Pour créer cette table d'exemple, utilisez les instructions CREATE TABLE suivantes :

SQL

USE tempdb;
GO
CREATE TABLE xTable (xCol XML);
GO

Fichier de format d'exemple

Le délimiteur de fin de champ doit être spécifié dans le fichier de format. L'exemple D utilise un fichier de format non XML intitulé Xmltable.fmt qui contient le code suivant :

Output

9.0
1
1       SQLBINARY     0       0       "\0\0\0\0z"    1     xCol         ""

Vous pouvez utiliser ce fichier de format pour importer en bloc des documents XML dans la table xTable à l'aide d'une commande bcp ou d'une instruction BULK INSERT ou INSERT ... SELECT * FROM OPENROWSET(BULK...) .

Exemple D

Cet exemple utilise le fichier de format Xmltable.fmt dans une instruction BULK INSERT pour importer le contenu d'un fichier de données XML intitulé Xmltable.dat.

SQL

BULK INSERT xTable
FROM 'C:\SampleFolder\Xmltable.dat'
WITH (FORMATFILE = 'C:\SampleFolder\Xmltable.fmt');
GO

[Haut]

Exportation en bloc de données XML

L’exemple suivant utilise bcp pour exporter en bloc des données XML à partir de la table créée dans l’exemple précédent, à l’aide du même fichier de format XML. Dans la commande bcp suivante, <server_name> et <instance_name> représentent des espaces réservés qui doivent être remplacés par les valeurs appropriées :

Invite de commandes Windows

bcp bulktest..xTable out a-wn.out -N -T -S<server_name>\<instance_name>

Notes

SQL Server n'enregistre pas l'encodage XML lorsque les données XML sont conservées dans la base de données. Par conséquent, l'encodage original des champs XML n'est pas disponible lorsque les données XML sont exportées. SQL Server utilise l’encodage UTF-16 durant l’exportation de données XML.

Voir aussi

• INSERT (Transact-SQL)
• Clause SELECT (Transact-SQL)
• Utilitaire bcp
• Importation et exportation en bloc de données (SQL Server)
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)