다음을 통해 공유

XML 문서 대량 가져오기 및 내보내기 예(SQL Server)

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Database Azure SQL Managed Instance

XML 문서를 SQL Server 데이터베이스로 대량으로 가져오거나 SQL Server 데이터베이스에서 대량으로 내보낼 수 있습니다. 이 문서에서는 두 가지 방식의 예를 제공합니다.

데이터 파일에서 SQL Server 테이블 또는 분할되지 않은 뷰로 데이터를 대량으로 가져오려면 다음 옵션을 사용할 수 있습니다.

  • bcp 유틸리티

    또한 bcp 유틸리티를 사용해서도 분할된 뷰를 포함하여 SELECT 문이 작동하는 SQL Server 데이터베이스의 어디에서나 데이터를 내보낼 수 있습니다.

  • BULK INSERT

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

자세한 내용은 다음 문서를 참조하세요.

예제

XML 데이터를 이진 바이트 스트림으로 대량 가져오기

적용할 인코딩 선언이 있는 파일에서 XML 데이터를 대량으로 가져오는 경우 OPENROWSET(BULK...) 절에 SINGLE_BLOB 옵션을 지정합니다. SINGLE_BLOB 옵션은 SQL Server의 XML 파서가 XML 선언에 지정된 인코딩 체계에 따라 데이터를 가져오도록 합니다.

샘플 테이블

예제 A를 테스트하려면 T 샘플 테이블을 만듭니다.

USE tempdb;
GO

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

샘플 데이터 파일

예제 A를 실행하려면 먼저C:\SampleFolder\SampleData3.txt인코딩 체계를 지정하는 다음 예제 인스턴스가 포함된 UTF-8 인코딩 파일( UTF-8 )을 만들어야 합니다.

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

예 1

다음은 INSERT ... SELECT * FROM OPENROWSET(BULK...) 문에 있는 SINGLE_BLOB 옵션을 사용하여 SampleData3.txt 이름이 지정된 파일에서 데이터를 가져오고 단일 열 테이블인 T 샘플 테이블에 XML 인스턴스를 삽입하는 예제입니다.

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

열 이름을 다음과 같이 명시적으로 지정할 수도 있습니다.

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

설명

이 경우 SINGLE_BLOB을 사용하여 XML 문서의 인코딩(XML 인코딩 선언에서 지정)과 서버에서 암시된 문자열 코드 페이지 간의 불일치를 방지할 수 있습니다.

NCLOB 또는 CLOB 데이터 형식을 사용하고 코드 페이지 또는 인코딩 충돌이 발생하는 경우 다음 중 하나를 수행해야 합니다.

  • XML 선언을 제거하여 XML 데이터 파일의 내용을 성공적으로 가져옵니다.

  • XML 선언에 사용되는 인코딩 체계와 일치하는 쿼리의 CODEPAGE 옵션에 코드 페이지를 지정합니다.

  • 데이터베이스 데이터 정렬 설정을 비유니코드 XML 인코딩 체계에 맞추거나 불일치를 해결합니다.

[위쪽]

기존 행에 XML 데이터 대량 가져오기

이 예제에서는 OPENROWSET 대량 행 집합 공급자를 사용하여 T 샘플 테이블 의 기존 행 또는 행에 XML 인스턴스를 추가합니다.

참고 항목

이 예를 실행하려면 예 1에서 제공한 테스트 스크립트를 먼저 완료해야 합니다. 해당 예는 tempdb.dbo.T 테이블을 만들고 SampleData3.txt에서 데이터를 대량으로 가져옵니다.

샘플 데이터 파일

예제 B는 이전 예제에서 수정된 SampleData3.txt 샘플 데이터 파일 버전을 사용합니다. 이 예제를 실행하려면 파일의 콘텐츠를 다음과 같이 수정합니다.

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

예 2

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

[위쪽]

DTD가 포함된 파일에서 XML 데이터 대량 가져오기

Important

XML 환경에서 필요하지 않은 경우 DTD(문서 종류 정의)를 지원하지 않는 것이 좋습니다. DTD 지원을 활성화하면 서버의 공격 표면이 증가하고 서비스 거부 공격에 노출될 수 있습니다. DTD 지원을 설정해야 할 경우에는 신뢰할 수 있는 XML 문서만 처리하여 이러한 보안 위험을 줄일 수 있습니다.

bcp를 사용하여 DTD가 포함된 파일에서 XML 데이터를 가져오는 경우 다음 오류가 발생할 수 있습니다.

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

이 문제를 해결하려면 OPENROWSET(BULK...) 함수를 사용한 다음, 명령의 CONVERT 절에 SELECT 옵션을 지정하여 DTD가 포함된 데이터 파일에서 XML 데이터를 가져올 수 있습니다. 명령의 기본 구문은 다음과 같습니다.

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

샘플 데이터 파일

이 대량 가져오기 예제를 테스트하려면 먼저 다음 샘플 인스턴스가 포함된 파일(C:\SampleFolder\Dtdfile.xml)을 만듭니다.

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

샘플 테이블

예 3에서는 다음 T1 문에 의해 생성된 CREATE TABLE 예제 테이블을 사용합니다.

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

예 3

이 예제에서는 OPENROWSET(BULK...)을 사용하고, XML 데이터를 Dtdfile.xml에서 T1 샘플 테이블로 가져오는 SELECT 절의 CONVERT 옵션을 지정합니다.

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

INSERT 문을 실행하면 XML에서 DTD가 잘리고 T1 테이블에 저장됩니다.

[위쪽]

서식 파일을 사용하여 명시적으로 필드 종결자 지정

다음 예제에서는 XML 문서(Xmltable.dat)를 대량으로 가져오는 방법을 보여줍니다.

샘플 데이터 파일

Xmltable.dat의 문서는 각 행에 하나씩 두 개의 XML 값이 포함되어 있습니다. 첫 번째 XML 값은 UTF-16으로 인코딩되고 두 번째 값은 UTF-8로 인코딩됩니다.

이 데이터 파일의 내용은 다음 16진수 덤프에 표시됩니다.

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*

샘플 테이블

XML 문서를 대량으로 가져오거나 내보낼 때는 문서에 나타날 가능성이 없는 필드 종결자를 사용해야 합니다. 예를 들어 다음과 같이 z 문자 앞에 일련의 Null(\0) 4개를 사용할 수 있습니다(\0\0\0\0z).

이 예제에서는 xTable 샘플 테이블에 이 필드 종결자를 사용하는 방법을 보여줍니다. 이 예제 테이블을 만들려면 다음 CREATE TABLE 문을 사용합니다.

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

샘플 서식 파일

필드 종결자는 서식 파일에 지정해야 합니다. 예제 D는 다음 출력을 포함하며 Xmltable.fmt 이름이 지정된 비 XML 서식 파일을 사용합니다.

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

이 서식 파일로 bcp 명령이나 BULK INSERT 또는 INSERT ... SELECT * FROM OPENROWSET(BULK...) 문을 사용하여 XML 문서를 xTable 테이블로 대량으로 가져올 수 있습니다.

예제 D

이 예제에서는 BULK INSERT 문의 Xmltable.fmt 서식 파일을 사용하여 Xmltable.dat 이름이 지정된 XML 데이터 파일의 내용을 가져옵니다.

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

[위쪽]

XML 데이터 대량 내보내기

다음 예제에서는 bcp를 사용하여 이전 예제에서 동일한 XML 서식 파일로 만든 테이블에서 XML 데이터를 대량으로 내보냅니다. 다음 bcp 명령에서 <server_name> 및 <instance_name>은 적절한 값으로 바꿔야 하는 자리 표시자를 나타냅니다.

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

참고 항목

SQL Server에서는 XML 데이터가 데이터베이스에 유지되면 XML 인코딩을 저장하지 않습니다. 따라서 XML 데이터를 내보낼 때는 XML 필드의 원래 인코딩을 사용할 수 없습니다. SQL Server는 XML 데이터를 내보낼 때 UTF-16 인코딩을 사용합니다.

참고 항목