Sdílet prostřednictvím

Příklady hromadného importu a exportu dokumentů XML (SQL Server)

  • Článek

platí pro: SQL Server 2016 (13.x) a novější azure SQL Databaseazure SQL Managed Instance

Dokumenty XML můžete hromadně importovat do databáze SQL Serveru nebo je hromadně exportovat z databáze SQL Serveru. Tento článek obsahuje příklady obou.

Pokud chcete hromadně importovat data z datového souboru do tabulky SQL Serveru nebo zobrazení bez oddílů, můžete použít následující možnosti:

  • nástroj bcp

    Můžete také použít nástroj bcp k exportu dat z libovolného místa v databázi SQL Serveru, kterou funguje příkaz SELECT, včetně rozdělených zobrazení.

  • BULK INSERT

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

Další informace najdete v následujících článcích:

Příklady

Hromadný import dat XML jako binárního bajtového datového proudu

Při hromadném importu dat XML ze souboru, který obsahuje deklaraci kódování, kterou chcete použít, zadejte SINGLE_BLOB možnost v klauzuli OPENROWSET(BULK...). Možnost SINGLE_BLOB zajišťuje, že analyzátor XML v SQL Serveru importuje data podle schématu kódování zadaného v deklaraci XML.

Ukázková tabulka

Pokud chcete otestovat příklad A, vytvořte ukázkovou tabulku T.

USE tempdb;
GO

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

Ukázkový datový soubor

Než spustíte příklad A, musíte vytvořit soubor s kódováním UTF-8 (C:\SampleFolder\SampleData3.txt), který obsahuje následující ukázkovou instanci, která určuje schéma kódování UTF-8.

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

Příklad A

Tento příklad používá možnost SINGLE_BLOB v příkazu INSERT ... SELECT * FROM OPENROWSET(BULK...) k importu dat ze souboru s názvem SampleData3.txt a vložení instance XML do tabulky s jedním sloupcem, ukázková tabulka T.

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

Názvy sloupců můžete také explicitně zadat takto:

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

Poznámky

Pomocí SINGLE_BLOB v tomto případě se můžete vyhnout neshodě mezi kódováním dokumentu XML (jak je určeno deklarací kódování XML) a znakovou stránkou řetězce odvozenou serverem.

Pokud používáte datové typy NCLOB nebo CLOB a dojde ke konfliktu kódové stránky nebo kódování, musíte udělat jednu z těchto věcí:

  • Odstraňte deklaraci XML pro úspěšné importování obsahu datového souboru XML.

  • Zadejte znakovou stránku v CODEPAGE možnosti dotazu, který odpovídá schématu kódování použitému v deklaraci XML.

  • Spárujte nebo vyřešte nastavení kolace databáze se schématem kódování XML bez použití Unicode.

[top]

Hromadný import dat XML v existujícím řádku

Tento příklad používá poskytovatele hromadného řádkového seznamu OPENROWSET k přidání XML instance do existujícího řádku nebo řádků v ukázkové tabulce T.

Poznámka

Pokud chcete tento příklad spustit, musíte nejprve dokončit testovací skript uvedený v příkladu A. Tento příklad vytvoří tabulku tempdb.dbo.T a hromadně importuje data z SampleData3.txt.

Ukázkový datový soubor

Příklad B používá upravenou verzi ukázkového datového souboru SampleData3.txt z předchozího příkladu. Pokud chcete tento příklad spustit, upravte obsah tohoto souboru následujícím způsobem:

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

Příklad B

-- 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

[Horní]

Hromadný import dat XML ze souboru, který obsahuje DTD

Důležitý

Doporučujeme nepovolovat podporu definic typů dokumentů (DTD), pokud to ve vašem prostředí XML nevyžaduje. Zapnutí podpory DTD zvyšuje prostor pro útok na váš server a může ho vystavit útoku na dostupnost služby. Pokud je nutné povolit podporu DTD, můžete toto bezpečnostní riziko snížit zpracováním pouze důvěryhodných dokumentů XML.

Při použití bcp k importu dat XML ze souboru, který obsahuje DTD, se může zobrazit následující chyba:

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

Chcete-li tento problém obejít, můžete importovat data XML z datového souboru, který obsahuje DTD pomocí funkce OPENROWSET(BULK...) a pak zadat CONVERT možnost v klauzuli SELECT příkazu. Základní syntaxe příkazu je:

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

Ukázkový datový soubor

Před testováním tohoto příkladu hromadného importu vytvořte soubor (C:\SampleFolder\Dtdfile.xml), který obsahuje následující ukázkovou instanci:

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

Ukázková tabulka

Příklad jazyka C používá ukázkovou tabulku T1 vytvořenou následujícím příkazem CREATE TABLE:

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

Příklad C

Tento příklad používá OPENROWSET(BULK...) a určuje možnost CONVERT v klauzuli SELECT k importu dat XML z Dtdfile.xml do ukázkové tabulky T1.

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

Po provedení příkazu INSERT se DTD odstraní z XML a uloží se do tabulky T1.

[horní]

Explicitní zadání ukončovače pole pomocí formátového souboru

Následující příklad ukazuje, jak hromadně importovat následující dokument XML, Xmltable.dat.

Ukázkový datový soubor

Dokument v Xmltable.dat obsahuje dvě hodnoty XML, jednu pro každý řádek. První hodnota XML je kódována pomocí UTF-16 a druhá hodnota je kódována pomocí UTF-8.

Obsah tohoto datového souboru je zobrazen v následujícím hexadecimálním výpisu:

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*

Ukázková tabulka

Při hromadném importu nebo exportu dokumentu XML byste měli použít ukončovací znak pole , který se jistě nemůže objevit v žádném z dokumentů; například řada čtyř hodnot null (\0) následovaná písmenem z: \0\0\0\0z.

Tento příklad ukazuje, jak použít tento oddělovač polí pro ukázkovou tabulku xTable. Pokud chcete vytvořit tuto ukázkovou tabulku, použijte následující příkaz CREATE TABLE:

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

Ukázkový formát souboru

Ukončovací znak pole musí být zadán ve formátu souboru. Příklad D používá soubor formátu jiného formátu než XML s názvem Xmltable.fmt, který obsahuje následující výstup:

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

Tento formátový soubor můžete použít k hromadnému importu dokumentů XML do tabulky xTable pomocí příkazu bcp nebo příkazu BULK INSERT nebo INSERT ... SELECT * FROM OPENROWSET(BULK...).

Příklad D

Tento příklad používá soubor formátu Xmltable.fmt v příkazu BULK INSERT k importu obsahu datového souboru XML s názvem Xmltable.dat.

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

[horní]

Hromadný export dat XML

Následující příklad používá bcp k hromadnému exportu dat XML z tabulky vytvořené v předchozím příkladu pomocí stejného souboru formátu XML. V následujícím příkazu bcp představují <server_name> a <instance_name> zástupné symboly, které musí být nahrazeny příslušnými hodnotami:

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

Poznámka

SQL Server neukládá kódování XML při zachování dat XML v databázi. Proto původní kódování polí XML není k dispozici při exportu dat XML. SQL Server při exportu dat XML používá kódování UTF-16.

Viz také