Ejemplos de importación y exportación de documentos XML en bloque (SQL Server)
Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance
Puede importar documentos XML de forma masiva en una base de datos SQL Server o exportarlos de forma masiva desde una base de datos SQL Server. En este artículo se proporcionan ejemplos de ambos.
Para importar datos de forma masiva desde un archivo de datos a una tabla de SQL Server o una vista sin particiones, puede usar las siguientes opciones:
Utilidadbcp
También puede usar la utilidad bcp para exportar datos desde cualquier lugar de una base de datos de SQL Server que funcione una
SELECT
instrucción, incluidas las vistas con particiones.BULK INSERT
INSERT ... SELECT * FROM OPENROWSET(BULK...)
Para más información, consulte los siguientes artículos.
- Importación y exportación de datos en bloque con BCP (SQL Server)
- Uso de BULK INSERT u OPENROWSET(BULK...) para importar datos a SQL Server
- Importación de XML en SQL Server con el componente carga masiva XML
- Colecciones de esquemas XML (SQL Server)
Ejemplos
- A. Importar de forma masiva datos XML como un flujo de bytes binario
- B. Importar de forma masiva datos XML en una fila existente
- C. Importar de forma masiva datos XML a partir de un archivo que contiene una DTD
- D. Especificar el terminador de campo explícitamente mediante el uso de un archivo de formato
- E. Exportar de forma masiva datos XML
Importar de forma masiva datos XML como un flujo de bytes binario
Al importar datos XML de forma masiva desde un archivo que contiene una declaración de codificación que desea aplicar, especifique la SINGLE_BLOB
opción en la OPENROWSET(BULK...)
cláusula . La SINGLE_BLOB
opción garantiza que el analizador XML de SQL Server importa los datos según el esquema de codificación especificado en la declaración XML.
Tabla de ejemplo
Para probar el ejemplo A, cree una tabla T
de ejemplo .
USE tempdb;
GO
CREATE TABLE T (
IntCol INT IDENTITY(1,1),
XmlCol XML
);
GO
Archivo de datos de ejemplo
Para poder ejecutar el ejemplo A, debe crear un archivo codificado con UTF-8 (C:\SampleFolder\SampleData3.txt
) que contenga el ejemplo de instancia siguiente que especifica el esquema de codificación UTF-8
.
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<ProductDescription ProductModelID="5">
<Summary>Some Text</Summary>
</ProductDescription>
</Root>
Ejemplo A
Este ejemplo utiliza la opción SINGLE_BLOB
en una instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...)
para importar datos de un archivo llamado SampleData3.txt
e insertar una instancia XML en la tabla de ejemplo de una sola columna T
.
INSERT INTO T (XmlCol)
SELECT *
FROM OPENROWSET(
BULK 'C:\SampleFolder\SampleData3.txt',
SINGLE_BLOB)
AS x;
También puede especificar explícitamente los nombres de columna de la siguiente manera:
INSERT INTO T (
XmlCol
)
SELECT
x.BulkColumn
FROM OPENROWSET(
BULK 'C:\SampleFolder\SampleData3.txt',
SINGLE_BLOB)
AS x;
Observaciones
Al usar SINGLE_BLOB
en este caso, puede evitar una discrepancia entre la codificación del documento XML (según lo especificado por la declaración de codificación XML) y la página de códigos de cadena implícita por el servidor.
Si utiliza tipos de datos NCLOB o CLOB y se produce un conflicto de página de códigos o de codificación, deberá realizar una de las siguientes acciones:
Elimine la declaración XML para poder importar correctamente el contenido del archivo de datos XML.
Especifique una página de códigos en la
CODEPAGE
opción de la consulta que coincida con el esquema de codificación que se usa en la declaración XML.Compare o resuelva los valores de intercalación de base de datos con un esquema de codificación XML distinto de Unicode.
Importar de forma masiva datos XML en una fila existente
Este ejemplo utiliza el el proveedor de conjuntos de filas BULK OPENROWSET
para agregar una instancia XML a una fila o filas existentes en la tabla de ejemplo T
.
Nota
Para ejecutar este ejemplo, es necesario que complete primero el script de prueba del ejemplo A. Ese ejemplo crea la tabla tempdb.dbo.T
e importa los datos de forma masiva de SampleData3.txt
.
Archivo de datos de ejemplo
El ejemplo B utiliza una versión modificada del archivo de datos de ejemplo SampleData3.txt
del ejemplo anterior. Para ejecutar este ejemplo, modifique el contenido de este archivo de la siguiente manera:
<Root>
<ProductDescription ProductModelID="10">
<Summary>Some New Text</Summary>
</ProductDescription>
</Root>
Ejemplo 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
Importar de forma masiva datos XML a partir de un archivo que contiene una DTD
Importante
Se recomienda no habilitar la compatibilidad con definiciones de tipo de documento (DTD) si no es necesario en el entorno XML. Si se activa la compatibilidad con DTD, se aumenta el área expuesta susceptible de ataques del servidor, que puede quedar expuesta a un ataque por denegación de servicio. En caso de que sea necesario habilitar la compatibilidad con DTD, este riesgo de seguridad puede reducirse procesando únicamente los documentos XML de confianza.
Puede obtener el siguiente error cuando se usa bcp para importar datos XML desde un archivo que contiene una DTD:
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
Para solucionar este problema, puede importar datos XML de un archivo de datos que contenga una DTD mediante la función OPENROWSET(BULK...)
y especificando posteriormente la opción CONVERT
en la cláusula SELECT
del comando. La sintaxis básica para el comando es la siguiente:
INSERT ... SELECT CONVERT(...) FROM OPENROWSET(BULK...)
Archivo de datos de ejemplo
Para probar este ejemplo de importación en bloque, cree un archivo (C:\SampleFolder\Dtdfile.xml
) que contenga la siguiente instancia de ejemplo:
<!DOCTYPE DOC [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1>January</elem1>
Tabla de ejemplo
El ejemplo C utiliza la tabla de ejemplo T1
que crea la instrucción CREATE TABLE
siguiente:
USE tempdb;
GO
CREATE TABLE T1(XmlCol XML);
GO
Ejemplo C
Este ejemplo utiliza OPENROWSET(BULK...)
y especifica la opción CONVERT
en la cláusula SELECT
para importar los datos XML desde Dtdfile.xml
al ejemplo de tabla T1
.
INSERT INTO T1
SELECT CONVERT(XML, BulkColumn, 2)
FROM OPENROWSET(
BULK 'C:\SampleFolder\Dtdfile.xml',
SINGLE_BLOB
) AS [rowsetresults];
Después de ejecutar la instrucción INSERT
, se quitará la DTD del XML y se almacenará en la tabla T1
.
Especificar el terminador de campo explícitamente mediante un archivo de formato
El ejemplo siguiente muestra cómo importar de forma masiva el siguiente documento XML, Xmltable.dat
.
Archivo de datos de ejemplo
El documento en Xmltable.dat
contiene dos valores XML, uno para cada fila. El primer valor XML está codificado con UTF-16 y el segundo con UTF-8.
El contenido de este archivo de datos se muestra en el siguiente volcado hexadecimal:
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*
Tabla de ejemplo
Al importar o exportar de forma masiva un documento XML, debe usar un terminador de campo que no pueda aparecer en ninguno de los documentos; por ejemplo, una serie de cuatro valores NULL (\0
) seguidos de la letra z
: \0\0\0\0z
.
Este ejemplo muestra cómo utilizar el terminador de campo para la tabla de ejemplo xTable
. Para crear esta vista de ejemplo, utilice la siguiente instrucción CREATE TABLE
:
USE tempdb;
GO
CREATE TABLE xTable (xCol XML);
GO
Archivo de formato de ejemplo
El terminador de campo se debe especificar en el archivo de formato. En el ejemplo D se usa un archivo de formato no XML denominado Xmltable.fmt
que contiene la siguiente salida:
9.0
1
1 SQLBINARY 0 0 "\0\0\0\0z" 1 xCol ""
Puede utilizar este archivo de formato para realizar importaciones masivas de documentos XML a la tabla xTable
mediante el comando bcp
o una instrucción BULK INSERT
o INSERT ... SELECT * FROM OPENROWSET(BULK...)
.
Ejemplo D
Este ejemplo utiliza el archivo de formato Xmltable.fmt
en una instrucción BULK INSERT
para importar el contenido de un archivo de datos XML denominado Xmltable.dat
.
BULK INSERT xTable
FROM 'C:\SampleFolder\Xmltable.dat'
WITH (FORMATFILE = 'C:\SampleFolder\Xmltable.fmt');
GO
Exportar de forma masiva datos XML
En el ejemplo siguiente se utiliza bcp para realizar la exportación masiva de datos XML a partir de la tabla creada en el ejemplo anterior con el mismo archivo de formato XML. En el siguiente comando bcp
, <server_name>
y <instance_name>
representan los marcadores de posición que deben ser reemplazados con los valores adecuados:
bcp bulktest..xTable out a-wn.out -N -T -S<server_name>\<instance_name>
Nota
SQL Server no guarda la codificación XML cuando se mantienen datos XML en la base de datos. Por lo tanto, la codificación original de los campos XML no estará disponible cuando se exporten datos XML. SQL Server usa la codificación UTF-16 al exportar datos XML.
Consulte también
Comentarios
https://aka.ms/ContentUserFeedback.
Proximamente: Ao longo de 2024, retiraremos gradualmente GitHub Issues como mecanismo de comentarios sobre o contido e substituirémolo por un novo sistema de comentarios. Para obter máis información, consulte:Enviar e ver os comentarios