XML, archivos de formato (SQL Server)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

SQL Server proporciona un esquema XML que define la sintaxis para escribir archivos de formato XML que se usarán para la importación en bloque de datos en una tabla de SQL Server. Los archivos de formato XML deben adherirse a este esquema, que se define en el lenguaje de definición de esquemas XML (XSDL). Los archivos con formato XML solamente se admiten cuando se instalan herramientas de SQL Server con SQL Server Native Client.

Puede usar un archivo de formato XML con un comando bcp, una instrucción BULK INSERT o una instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...). El comando bcp permite generar automáticamente un archivo de formato XML para una tabla; para más información, consulte bcp Utility.

Ventajas de los archivos de formato XML

• Los archivos de formato XML son autodescriptivos, lo que facilita su lectura, creación y ampliación. Los usuarios pueden leerlos, lo que facilita la comprensión del modo en que se interpretan los datos durante las operaciones masivas.

• Los archivos de formato XML contienen los tipos de datos de las columnas de destino. La codificación XML describe claramente los tipos de datos y elementos de datos del archivo de datos, así como la asignación entre los elementos de datos y las columnas de las tablas.

  Esta característica habilita la separación entre la representación de los datos en el archivo de datos y el tipo de datos asociado a cada campo del archivo. Por ejemplo, si un archivo de datos contiene una representación de caracteres de los datos, se perderá el tipo de columna SQL correspondiente.

• Un archivo de formato XML permite cargar un campo que contenga un único tipo de datos de objeto grande (LOB) desde un archivo de datos.

• Un archivo de formato XML puede mejorarse manteniendo su compatibilidad con sus versiones anteriores. Además, la claridad de la codificación XML facilita la creación de varios archivos de formato para un determinado archivo de datos. Esto es útil si tiene que asignar todos o algunos campos de datos a columnas de diferentes tablas o vistas.

• La sintaxis XML es independiente de la dirección de la operación; es decir, la sintaxis es la misma para exportaciones e importaciones masivas.

• Puede usar los archivos de formato XML para importar los datos de forma masiva en tablas o vistas sin particiones y para exportar los datos de forma masiva.

• Para OPENROWSET(BULK…) la función que especifica una tabla de destino es opcional. Esto se debe a que la función se basa en el archivo de formato XML para leer datos de un archivo de datos.

  Nota:

  Es necesaria una tabla de destino con el comando bcp y la instrucción BULK INSERT, que usa las columnas de la tabla de destino para realizar la conversión de tipos.

Estructura de los archivos de formato XML

Al igual que un archivo de formato no XML, un archivo de formato XML define el formato y la estructura de los campos de datos de un archivo de datos y asigna dichos campos a columnas de una sola tabla de destino.

Un archivo de formato XML posee dos componentes principales, <RECORD> y <ROW>:

• <RECORD> describe los datos tal como se almacenan en el archivo de datos.

  Cada elemento <RECORD> contiene un conjunto de uno o varios elementos <FIELD>. Dichos elementos corresponden a los campos del archivo de datos. La sintaxis básica es la siguiente:

  <RECORD>

  <FIELD .../> [ ...n ]

  </RECORD>

  Cada elemento <FIELD> describe el contenido de un campo de datos determinado. Un campo solo puede asignarse a una columna de la tabla. No es necesario asignar todos los campos a columnas.

  Un campo de un archivo de datos puede tener una longitud fija o variable, o bien terminar mediante un carácter. Un valor de campo puede representarse como: un carácter (mediante una representación de un solo byte), un carácter ancho (mediante la representación Unicode de dos bytes), un formato de base de datos nativo o un nombre de archivo. Si un valor de campo se representa como un nombre de archivo, éste apunta al archivo que contiene el valor de una columna BLOB en la tabla de destino.

• <ROW> describe el modo de crear filas de datos a partir de un archivo de datos cuando los datos del archivo se importan en una tabla de SQL Server.

  Un elemento <ROW> contiene un conjunto de elementos <COLUMN>. Estos elementos corresponden a las columnas de la tabla. La sintaxis básica es la siguiente:

  <ROW>

  <COLUMN .../> [ ...n ]

  </ROW>

  Cada elemento <COLUMN> puede asignarse únicamente a un campo del archivo de datos. El orden de los elementos <COLUMN> del elemento <ROW> define el orden en el que la operación masiva los devuelve. El archivo de formato XML asigna a cada elemento <COLUMN> un nombre local que no tiene ninguna relación con la columna de la tabla de destino de una operación de importación masiva.

Sintaxis de esquema para archivos de formato XML

Esta sección contiene un resumen de los elementos y atributos del esquema XML para archivos de formato XML. La sintaxis de un archivo de formato es independiente de la dirección de la operación; es decir, la sintaxis es la misma para exportaciones e importaciones masivas. En esta sección también se trata cómo la importación en bloque usa los elementos <ROW> y <COLUMN> y cómo colocar el valor xsi:type de un elemento en un conjunto de datos.

Para ver la sintaxis en archivos de formato XML reales, consulte Archivos de formato XML de ejemplo.

Sintaxis básica del esquema XML

Las instrucciones de esta sintaxis muestran solo los elementos (<BCPFORMAT>, <RECORD>, <FIELD>, <ROW> y <COLUMN>) y sus atributos básicos.

Elementos de esquema

En esta sección se resume la finalidad de cada elemento que define el esquema XML para los archivos de formato XML. Los atributos se describen más adelante, en otras secciones de este tema.

<BCPFORMAT>
Es el elemento de archivo de formato que define la estructura de los registros de un determinado archivo de datos y su correspondencia con las columnas de una fila de tabla en la tabla.

<RECORD .../>
Define un elemento complejo que contiene uno o varios elementos <FIELD>. El orden en que se declaran los campos en el archivo de formato es el orden en que estos campos aparecen en el archivo de datos.

<FIELD .../>
Define un campo del archivo de datos que contiene datos.

Los atributos de este elemento se tratan en la sección Atributos del elemento <FIELD> más adelante en este tema.

<ROW .../>
Define un elemento complejo que contiene uno o varios elementos <COLUMN>. El orden de los elementos <COLUMN> es independiente del orden de los elementos <FIELD> de una definición RECORD. Más bien, el orden de los elementos <COLUMN> de un archivo de formato determina el orden de las columnas del conjunto de filas resultante. Los campos de datos se cargan en el orden en que los elementos <COLUMN> correspondientes se declaran en el elemento <COLUMN>.

Para obtener más información, consulte la sección Cómo usa la importación en bloque el elemento <ROW> más adelante en este tema.

<COLUMN>
Define una columna como elemento (<COLUMN>). Cada elemento <COLUMN> corresponde a un elemento <FIELD> (cuyo identificador se especifica en el atributo SOURCE del elemento <COLUMN>).

Los atributos de este elemento se tratan en la sección Atributos del elemento <COLUMN> más adelante en este tema. Consulte también Cómo usa la importación en bloque el elemento <COLUMN> más adelante en este tema.

</BCPFORMAT>
Obligatorio para finalizar el archivo de formato.

Atributos del elemento <FIELD>

En esta sección se describen los atributos del elemento <FIELD>, que se resumen en la sintaxis de esquema siguiente:

<FIELD
ID ="fieldID"
xsi:type ="fieldType"
[ LENGTH ="n" ]
[ PREFIX_LENGTH ="p" ]
[ MAX_LENGTH ="m" ]
[ COLLATION ="collationName" ]
[ TERMINATOR ="terminator" ]
/>

Cada elemento <FIELD> es independiente de los demás. Un campo se describe según los atributos siguientes:

Valores Xsi:type del elemento <FIELD>

El valor xsi:type es una construcción XML (usada como atributo) que identifica el tipo de datos de una instancia de un elemento. Para obtener más información acerca de su uso, vea "Colocar el valor xsi:type en un conjunto de datos", más adelante en este tema.

El valor xsi:type del elemento <FIELD> admite los siguientes tipos de datos.

Para obtener información sobre los tipos de datos de Microsoft SQL Server, consulte Tipos de datos (Transact-SQL).

Atributos del elemento <COLUMN>

En esta sección se describen los atributos del elemento <COLUMN>, que se resumen en la sintaxis de esquema siguiente:

<COLUMN
SOURCE = "fieldID"
NAME = "columnName"
xsi:type = "columnType"
[ LENGTH = "n" ]
[ PRECISION = "n" ]
[ SCALE = "value" ]
[ NULLABLE = { "YES"
"NO" } ]
/>

Un campo se asigna a una columna de la tabla de destino mediante los atributos siguientes:

Valores xsi:type del elemento <COLUMN>

El valor xsi:type es una construcción XML (usada como atributo) que identifica el tipo de datos de una instancia de un elemento. Para obtener más información acerca de su uso, vea "Colocar el valor xsi:type en un conjunto de datos", más adelante en este tema.

El elemento <COLUMN> admite tipos de datos SQL nativos, de la forma siguiente:

Para obtener información sobre los tipos de datos de SQL Server, consulte Tipos de datos (Transact-SQL).

Uso del elemento <ROW> en la importación en bloque

El elemento <ROW> se omite en algunos contextos. El hecho de que un elemento <ROW> afecte a una operación de importación en bloque depende de cómo se realice la operación:

• Comando bcp

  Al cargar datos en una tabla de destino, bcp omite al componente <ROW>. En su lugar, bcp carga los datos en función de los tipos de columnas de la tabla de destino.

• Instrucciones Transact-SQL (proveedor de conjuntos de filas BULK de BULK INSERT y OPENROWSET)

  Al realizar una importación en bloque de datos en una tabla, las instrucciones Transact-SQL usan el componente <ROW> para generar el conjunto de filas de entrada. Además, las instrucciones Transact-SQL realizan las conversiones de tipos adecuadas en función de los tipos de columna especificados en <ROW> y de la columna correspondiente en la tabla de destino. Si los tipos de columna especificados en el archivo de formato y la tabla de destino no coinciden, se realiza una conversión de tipo adicional. Esta conversión de tipo adicional puede llevar a discrepancias (es decir, a una pérdida de precisión) en cuanto al comportamiento en el proveedor de conjuntos de filas BULK de OPENROWSET o BULK INSERT en comparación con bcp.

  La información del elemento <ROW> permite construir una fila sin necesidad de información adicional. Por este motivo, puede generar un conjunto de filas mediante una instrucción SELECT (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile).

  Nota:

  La cláusula OPENROWSET BULK requiere un archivo de formato (tenga en cuenta que solo se puede convertir desde el tipo de datos del campo al tipo de datos de una columna con un archivo de formato XML).

Uso del elemento <COLUMN> en la importación en bloque

Para realizar una importación en bloque de datos en una tabla, los elementos <COLUMN> de un archivo de formato asignan un campo de archivo de datos a columnas de tabla al especificar:

• La posición de cada campo dentro de una fila del archivo de datos.

• El tipo de columna, que se utiliza para convertir el tipo de datos de campo al tipo de datos de columna deseado.

Si un campo no tiene asignada ninguna columna, el campo no se copia en las filas generadas. Este comportamiento permite a un archivo de datos generar filas con distintas columnas (en tablas diferentes).

De forma similar, para exportar de forma masiva datos de una tabla, cada elemento <COLUMN> del archivo de formato asigna la columna de la fila de la tabla de entrada a su campo correspondiente en el archivo de datos de salida.

Poner el valor xsi:type en un conjunto de datos

Si un documento XML se valida con el lenguaje de definición de esquema XML (XSD), el valor xsi:type no se coloca en el conjunto de datos. No obstante, puede colocar la información de xsi:type en el conjunto de datos si carga el archivo de formato XML en un documento XML (por ejemplo, myDoc), tal como ilustra el siguiente fragmento de código:

...;  
myDoc.LoadXml(xmlFormat);  
XmlNodeList ColumnList = myDoc.GetElementsByTagName("COLUMN");  
for(int i=0;i<ColumnList.Count;i++)  
{  
   Console.Write("COLUMN: xsi:type=" +ColumnList[i].Attributes["type",  
      "http://www.w3.org/2001/XMLSchema-instance"].Value+"\n");  
}

Archivos de formato XML de ejemplo

Esta sección contiene información sobre el uso de archivos de formato XML en diversos casos, incluido un ejemplo de Adventure Works.

Los ejemplos muestran los aspectos clave del uso de archivos de formato XML, del siguiente modo:

• Ordenar campos de datos de caracteres igual que columnas de tabla

• Ordenar campos de datos y columnas de tabla de forma diferente

• Omitir un campo de datos

• Asignar tipos diferentes de campos a columnas

• Asignar datos XML a una tabla

• Importar campos de longitud fija o de ancho fijo

• Ejemplo adicional

A Ordenar campos de datos de caracteres como columnas de tabla

En el ejemplo siguiente se muestra un archivo de formato XML que describe un archivo de datos que contiene tres campos de datos de caracteres. El archivo de formato asigna el archivo de datos a una tabla que contiene tres columnas. Los campos de datos se corresponden uno a uno con las columnas de la tabla.

Tabla (fila): Person (Age int, FirstName varchar(20), LastName varchar(30))

Archivo de datos (registro): Age<tab>Firstname<tab>Lastname<return>

El siguiente archivo de formato XML lee del archivo de datos a la tabla.

En el elemento <RECORD> , el archivo de formato representa los valores de datos de los tres campos como datos de caracteres. Para cada campo, el atributo TERMINATOR indica el terminador que sigue al valor de datos.

Los campos de datos se corresponden uno a uno con las columnas de la tabla. En el elemento <ROW> , el archivo de formato asigna la columna Age al primer campo, la columna FirstName al segundo campo y la columna LastName al tercer campo.

<?xml version="1.0"?>  
<BCPFORMAT  
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <RECORD>  
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"  
      MAX_LENGTH="12"/>  
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"  
      MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"  
      MAX_LENGTH="30"  
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  </RECORD>  
  <ROW>  
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>  
    <COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>  
    <COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>  
  </ROW>  
</BCPFORMAT>

B. Ordenar campos de datos y columnas de tabla de forma diferente

En el ejemplo siguiente se muestra un archivo de formato XML que describe un archivo de datos que contiene tres campos de datos de caracteres. El archivo de formato asigna el archivo de datos a una tabla que contiene tres columnas que están ordenadas de forma diferente a los campos del archivo de datos.

Tabla (fila): Person (Age int, FirstName varchar(20), LastName varchar(30))

Archivo de datos (registro): Age<tab>Lastname<tab>Firstname<return>

En el elemento <RECORD> , el archivo de formato representa los valores de datos de los tres campos como datos de caracteres.

En el elemento <ROW> , el archivo de formato asigna la columna Age al primer campo, la columna FirstName al tercer campo y la columna LastName al segundo campo.

<?xml version="1.0"?>  
<BCPFORMAT  
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <RECORD>  
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"  
      MAX_LENGTH="12"/>  
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"  
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"  
      MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  </RECORD>  
  <ROW>  
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>  
    <COLUMN SOURCE="3" NAME="firstname" xsi:type="SQLVARYCHAR"/>  
    <COLUMN SOURCE="2" NAME="lastname" xsi:type="SQLVARYCHAR"/>  
  </ROW>  
</BCPFORMAT>

C. Omitir un campo de datos

En el ejemplo siguiente se muestra un archivo de formato XML que describe un archivo de datos que contiene cuatro campos de datos de caracteres. El archivo de formato asigna el archivo de datos a una tabla que contiene tres columnas. El segundo campo de datos no se corresponde con ninguna columna de la tabla.

Tabla (fila): Person (Age int, FirstName varchar(20), LastName varchar(30))

Archivo de datos (registro): Age<tab>employeeID<tab>Firstname<tab>Lastname<return>

En el elemento <RECORD> , el archivo de formato representa los valores de datos de los cuatro campos como datos de caracteres. Para cada campo, el atributo TERMINATOR indica el terminador que sigue al valor de datos.

En el elemento <ROW> , el archivo de formato asigna la columna Age al primer campo, la columna FirstName al tercer campo y la columna LastName al cuarto campo.

<?xml version = "1.0"?>  
<BCPFORMAT  
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <RECORD>  
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"  
      MAX_LENGTH="12"/>  
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"  
      MAX_LENGTH="10"  
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t"  
      MAX_LENGTH="20"  
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"  
      MAX_LENGTH="30"  
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  </RECORD>  
  <ROW>  
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>  
    <COLUMN SOURCE="3" NAME="firstname" xsi:type="SQLVARYCHAR"/>  
    <COLUMN SOURCE="4" NAME="lastname" xsi:type="SQLVARYCHAR"/>  
  </ROW>  
</BCPFORMAT>

D. Asignar <FIELD> xsi:type a <COLUMN> xsi:type

En el ejemplo siguiente se muestran tipos de campos diferentes y sus asignaciones a columnas.

<?xml version = "1.0"?>  
<BCPFORMAT  
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharTerm" ID="C1" TERMINATOR="\t"  
            MAX_LENGTH="4"/>  
      <FIELD xsi:type="CharFixed" ID="C2" LENGTH="10"  
         COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>  
      <FIELD xsi:type="CharPrefix" ID="C3" PREFIX_LENGTH="2"  
         MAX_LENGTH="32" COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>  
      <FIELD xsi:type="NCharTerm" ID="C4" TERMINATOR="\t"  
         MAX_LENGTH="4"/>  
      <FIELD xsi:type="NCharFixed" ID="C5" LENGTH="10"  
         COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>  
      <FIELD xsi:type="NCharPrefix" ID="C6" PREFIX_LENGTH="2"  
         MAX_LENGTH="32" COLLATION="SQL_LATIN1_GENERAL_CP1_CI_AS"/>  
      <FIELD xsi:type="NativeFixed" ID="C7" LENGTH="4"/>  
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="C1" NAME="Age" xsi:type="SQLTINYINT"/>  
      <COLUMN SOURCE="C2" NAME="FirstName" xsi:type="SQLVARYCHAR"  
      LENGTH="16" NULLABLE="NO"/>  
      <COLUMN SOURCE="C3" NAME="LastName" />  
      <COLUMN SOURCE="C4" NAME="Salary" xsi:type="SQLMONEY"/>  
      <COLUMN SOURCE="C5" NAME="Picture" xsi:type="SQLIMAGE"/>  
      <COLUMN SOURCE="C6" NAME="Bio" xsi:type="SQLTEXT"/>  
      <COLUMN SOURCE="C7" NAME="Interest"xsi:type="SQLDECIMAL"  
      PRECISION="5" SCALE="3"/>  
   </ROW>  
</BCPFORMAT>

E. Asignar datos XML a una tabla

En el ejemplo siguiente se crea una tabla vacía de dos columnas (t_xml), en la que la primera columna se asigna al tipo de datos int y la segunda columna se asigna al tipo de datos xml .

CREATE TABLE t_xml (c1 int, c2 xml)

El siguiente archivo de formato XML carga un archivo de datos en una tabla t_xml.

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
<RECORD>  
  <FIELD ID="1" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>  
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="8"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLINT"/>  
  <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNCHAR"/>  
</ROW>  
</BCPFORMAT>

F. Importar campos de longitud fija o de ancho fijo

En el siguiente ejemplo se describen campos fijos de 10 o 6 caracteres cada uno. El archivo de formato representa estas longitudes y anchos de campo como LENGTH="10" y LENGTH="6", respectivamente. Cada una de las filas de los archivos de datos termina con una combinación de retorno de carro y avance de línea, {CR}{LF}, que el archivo de formato representa como TERMINATOR="\r\n".

<?xml version="1.0"?>  
<BCPFORMAT  
       xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <RECORD>  
    <FIELD ID="1" xsi:type="CharFixed" LENGTH="10"/>  
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>  
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>  
  </RECORD>  
  <ROW>  
    <COLUMN SOURCE="1" NAME="C1" xsi:type="SQLINT" />  
    <COLUMN SOURCE="2" NAME="C2" xsi:type="SQLINT" />  
  </ROW>  
</BCPFORMAT>

Ejemplos adicionales

Para obtener más ejemplos tanto de archivos de formato XML como de formato no XML, vea los siguientes temas:

• Usar un archivo de formato para omitir una columna de tabla (SQL Server)

• Uso de un archivo de formato para omitir un campo de datos (SQL Server)

• Uso de un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

Tareas relacionadas

• Crear un archivo de formato (SQL Server)

• Uso de un archivo de formato para la importación en bloque de datos (SQL Server)

• Usar un archivo de formato para omitir una columna de tabla (SQL Server)

• Uso de un archivo de formato para omitir un campo de datos (SQL Server)

• Uso de un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

Pasos siguientes

• Importar y exportar datos de forma masiva (SQL Server)
• Tipos de datos (Transact-SQL)
• Archivos de formato no XML (SQL Server)
• Archivos de formato para importar o exportar datos (SQL Server)