SQL BULK

4 Nov

En el proyecto que estoy desarrollando he tenido que emplear BULK para el registro de las operaciones realizadas a la base de datos, así que voy a intentar explicar cómo utilizar BULK.

Por lo que he podido ver hasta ahora, nadie sabe definir ¿qué es BULK?. Sólo se afirma que BULK se utiliza para la carga masiva de datos. Los datos pueden estar en ficheros CSV separados por comas(,) o en ficheros XML.

Para realizar este ejemplo he creado un procedimiento almacenado para olvidarnos del lenguaje en que estemos desarrollando y he utilizado un xml almacenado en un campo de la base de datos. El fichero puede estar almacenado en un disco duro o en un campo de la base de datos, al gusto o necesidad de cada desarrollo.

Estructura del xml:

<mensajes>
<E2ETraceEvent>
<System>
<EventID>1</EventID>
<Type>3</Type>
<SubType Name=”Information”>0</SubType>
<Level>8</Level>
<TimeCreated SystemTime=”2015-11-02T13:38:22.3745988Z” />
</System>
<ApplicationData>
<Auditoria>
<IdAuditoria>0</IdAuditoria>
<IdAplicacion>2</IdAplicacion>
<FechaCreacion>2015-11-02T14:38:22.2785988+01:00</FechaCreacion>
<Usuario>Anonymous</Usuario>
<Perfil>AdminExtTelefonos</Perfil>
<Operacion>
<TipoOperacion>1</TipoOperacion>
<Parametros>
<Parametro>
<Nombre>1</Nombre>
<Valor>valor emisor certificado</Valor>
</Parametro>
<Parametro>
<Nombre>2</Nombre>
<Valor>valor IDP</Valor>
</Parametro>
</Parametros>
<TipoAutenticacion>5</TipoAutenticacion>
</Operacion>
<IdEstadoOperacion>3</IdEstadoOperacion>
</Auditoria>
</ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent>
<System>
<EventID>1</EventID>
<Type>3</Type>
<SubType Name=”Information”>0</SubType>
<Level>8</Level>
<TimeCreated SystemTime=”2015-11-02T13:38:35.8865988Z” />
</System>
<ApplicationData>
<Auditoria>
<IdAuditoria>0</IdAuditoria>
<IdAplicacion>2</IdAplicacion>
<FechaCreacion>2015-11-02T14:38:35.8865988+01:00</FechaCreacion>
<Usuario>Anonymous</Usuario>
<Perfil>AdminExtTelefonos</Perfil>
<Operacion>
<TipoOperacion>1</TipoOperacion>
<TipoAutenticacion>5</TipoAutenticacion>
</Operacion>
<IdEstadoOperacion>3</IdEstadoOperacion>
</Auditoria>
</ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent>
<System>
<EventID>1</EventID>
<Type>3</Type>
<SubType Name=”Information”>0</SubType>
<Level>8</Level>
<TimeCreated SystemTime=”2015-11-02T13:38:52.8465988Z” />
</System>
<ApplicationData>
<Auditoria>
<IdAuditoria>0</IdAuditoria>
<IdAplicacion>2</IdAplicacion>
<FechaCreacion>2015-11-02T14:38:52.8465988+01:00</FechaCreacion>
<Usuario>Anonymous</Usuario>
<Perfil>AdminExtTelefonos</Perfil>
<Operacion>
<TipoOperacion>1</TipoOperacion>
<TipoAutenticacion>5</TipoAutenticacion>
</Operacion>
<IdEstadoOperacion>3</IdEstadoOperacion>
</Auditoria>
</ApplicationData>
</E2ETraceEvent>
</mensajes>

Trabajaremos con un xml parecido al que generan las trazas en Visual Studio. Este xml tiene un nodo principal “mensajes”, que tendrá varios nodos “E2ETraceEvent”, cada uno de ellos es un bloque de información básico.

Dentro del Procedimiento almacenado creado para tratar el xml tenemos que preparar el xml para poder trabajar con él como si de una tabla se tratase, para ello se utiliza:

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlTratar

Lo que hace esta instrucción es cargar en una variable int el contenido del xml que vamos a tratar.

Y una vez acabado de trabajar con el xml lo eliminamos de la memoria:
EXEC sp_xml_removedocument @hDoc

Dónde @hDoc y @xmlTratar habrán sido declarados como:

DECLARE @hDoc AS INT

DECLARE @xmlTratar xml

Si no sabes crear un procedimiento almacenado puedes revisarlo en un artículo anterior.

Dentro de la sección del procedimiento en la que tengas que insertar los datos…

DECLARE @hDoc AS INT

DECLARE @xmlTratar xml
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlTratar

INSERT INTO [nombreTabla]
(
[FechaCreacion]
,[SubTipo]
,[Usuario]
,[Perfil]
,[IdTipoOperacion]
,[IdEstadoOperacion])
SELECT
convert(datetime, replace(substring(Auditoria.col.value(‘FechaCreacion[1]’, ‘nvarchar(23)’),0,23), ‘T’, ‘ ‘), 21) FechaCreacion
,Tipo.col.value(‘@Name’, ‘nvarchar(50)’) SubTipo
,Auditoria.col.value(‘Usuario[1]’, ‘nvarchar(50)’) Usuario
,Auditoria.col.value(‘Perfil[1]’, ‘nvarchar(50)’) Perfil
,Operacion.col.value(‘TipoOperacion[1]’, ‘nvarchar(50)’) IdTipoOperacion
,Auditoria.col.value(‘IdEstadoOperacion[1]’, ‘nvarchar(50)’) IdEstadoOperacion
FROM @xmlTratar.nodes(‘/mensajes’) doc(col)
cross apply doc.col.nodes(‘E2ETraceEvent’) as Traza(col)
cross apply Traza.col.nodes(‘System’) as Sistema(col)
cross apply Sistema.col.nodes(‘SubType’) as Tipo(col)
cross apply Traza.col.nodes(‘ApplicationData’) as ApliData(col)
cross apply ApliData.col.nodes(‘Auditoria’) as Auditoria(col)
cross apply Auditoria.col.nodes(‘Operacion’) as Operacion(col)

EXEC sp_xml_removedocument @hDoc

Este sería el cuerpo básico para realizar la inserción en nuestra tabla. Ahora unas aclaraciones…

1º Recordar que es necesario el uso de sp_xml_preparedocument antes de tratar el xml y sp_xml_removedocument al finalizar.

2º La tabla que utilizamos:

FROM @xmlTratar.nodes(‘/mensajes’) doc(col)

Indicamos que queremos el nodo “mensajes” del fichero/campo @xmlTratar y asignamos un alias, en este caso le he llamado “doc”… obligatorio indicar (col).

3º Para poder recuperar varias “columnas” utilizaremos “cross apply”, necesitamos indicar el nodo padre del que cuelgan, nuestro alias.

cross apply doc.col.nodes(‘E2ETraceEvent’) as Traza(col)

doc es el nodo padre, el alias Traza y obligatorio (col)

4º En mi caso he ido recuperando nodo por nodo, ya que quiero información de todos ellos, pero ¿Qué pasa si nos queremos saltar un nodo por qué no nos interesa su información o por qué solo nos interesa algún nodo hijo?

En nuestra select tenemos las siguientes líneas para obtener datos, primero creamos el nodo Traza y luego ApliData

cross apply doc.col.nodes(‘E2ETraceEvent’) as Traza(col)

cross apply Traza.col.nodes(‘ApplicationData’) as ApliData(col)

Podía hacerse en un solo cruze

cross apply Traza.col.nodes(‘E2ETraceEvent/ApplicationData’) as ApliData(col)

Y recuperaríamos los valores de ApliData igual.

5º Podemos recuperar los valores del nodo o los valores de un atributo del nodo.

Para recuperar el valor de un nodo:

Auditoria.col.value(‘Usuario[1]’, ‘nvarchar(50)’) Usuario

Es necesario incluir [1], ya que necesita un singleton (o secuencia vacía).

Es necesario definir qué tipo de dato va a ser, en nuestro caso son nvarchar(50) ya que en la conversión de los datos esperados, todos entran en esta definición, salvo fecha.

Para recuperar el valor de un atributo:

Tipo.col.value(‘@Name’, ‘nvarchar(50)’) SubTipo

Es igual, salvo que se añade la @ y se elimina el [1].

Espero que esto nos sirva para utilizar BULK en un futuro.

Enjoy dev.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

La que has liado

Fotografiar con palabras, nuestro momento, nuestra época. Del 1.1 al 2.0

TERZERO

Pio Baroja

El árbol inútil

Blog de cuentos, libros y mala poesía

tu.deinflexión

somos dueños de nuestro destino, inventores de nuestro futuro y nuestra felicidad...

Frikstation

Pasión por el mundo de la televisión, el cine, los videojuegos y su merchandising.

A %d blogueros les gusta esto: