Select FOR XML

Clausula FOR XML.

A partir de la version 2000 SQL Server incluye la clausula FOR XML para la consultas. Sin embargo, es a partir de la versión 2005 cuando se integra XML como tipo de dato nativo.

Cuando especificamos la clausula FOR XML el resultado de la consulta es devuelto en formato XML.

La clausula FOR XML admite los siguientes modos que representan el formato en el que el XML es devuelto:

XML AUTO, el modo AUTO emplea los campos en la declaración SELECT para formar una jerarquía simple XML.
XML RAW, el modo RAW genera elementos únicos, los cuales se denominan row, por cada fila retornada.
EXPLICIT, el modo EXPLICIT requiere un formato específico que puede ser mapeado en casi cualquier forma XML, y al mismo tiempo ser formulado por una sola consulta SQL.

Adicionalmente, disponemos de dos opciones más TYPE y ELEMENTS que determinan el formato del XML resultante. Los vemos con ejemplos.

Un ejemplo de XML AUTO.

SELECT CO_FAMILIA, FAMILIA
FROM FAMILIAS
ORDER BY FAMILIA
FOR XML AUTO, TYPE

Obtendremos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

SELECT CO_FAMILIA, FAMILIA
FROM FAMILIAS
FOR XML AUTO, ELEMENTS

Obtendremos el siguiente resultado:

<FAMILIAS>
<CO_FAMILIA>1</CO_FAMILIA>
<FAMILIA>FAMILIA 1</FAMILIA>
</FAMILIAS>
<FAMILIAS>
<CO_FAMILIA>2</CO_FAMILIA>
<FAMILIA>FAMILIA 2</FAMILIA>
</FAMILIAS>
<FAMILIAS>
<CO_FAMILIA>3</CO_FAMILIA>
<FAMILIA>FAMILIA 3</FAMILIA>
</FAMILIAS>
<FAMILIAS>
<CO_FAMILIA>4</CO_FAMILIA>
<FAMILIA>FAMILIA 4</FAMILIA>
</FAMILIAS>

Ahora un ejemplo de XML RAW:

SELECT CO_FAMILIA, FAMILIA
FROM FAMILIAS
ORDER BY FAMILIA
FOR XML RAW , TYPE

    Obtenemos el siguiente resultado:

<row CO_FAMILIA="1" FAMILIA="FAMILIA 1" />
<row CO_FAMILIA="2" FAMILIA="FAMILIA 2" />
<row CO_FAMILIA="3" FAMILIA="FAMILIA 3" />
<row CO_FAMILIA="4" FAMILIA="FAMILIA 4" />

    Podemos obtener el resultado como elementos de la siguiente forma:

<row>
<CO_FAMILIA>1</CO_FAMILIA>
<FAMILIA>FAMILIA 1</FAMILIA>
</row>
<row>
<CO_FAMILIA>2</CO_FAMILIA>
<FAMILIA>FAMILIA 2</FAMILIA>
</row>
<row>
<CO_FAMILIA>3</CO_FAMILIA>
<FAMILIA>FAMILIA 3</FAMILIA>
</row>
<row>
<CO_FAMILIA>4</CO_FAMILIA>
<FAMILIA>FAMILIA 4</FAMILIA>
</row>

    También es posible especificar el nodo que queremos que muestre:

SELECT CO_FAMILIA, FAMILIA
FROM FAMILIAS
ORDER BY FAMILIA
FOR XML RAW ('FamiliasDeProductos') , TYPE

    Devuelve el siguiente resultado:

<FamiliasDeProductos CO_FAMILIA="1" FAMILIA="FAMILIA 1" />
<FamiliasDeProductos CO_FAMILIA="2" FAMILIA="FAMILIA 2" />
<FamiliasDeProductos CO_FAMILIA="3" FAMILIA="FAMILIA 3" />
<FamiliasDeProductos CO_FAMILIA="4" FAMILIA="FAMILIA 4" />

    Del mismo modo con la opción ELEMENTS:

SELECT CO_FAMILIA, FAMILIA
FROM FAMILIAS
ORDER BY FAMILIA
FOR XML RAW ('FamiliasDeProductos') , ELEMENTS

    Obtendremos el siguiente resultado:

<FamiliasDeProductos>
<CO_FAMILIA>1</CO_FAMILIA>
<FAMILIA>FAMILIA 1</FAMILIA>
</FamiliasDeProductos>
<FamiliasDeProductos>
<CO_FAMILIA>2</CO_FAMILIA>
<FAMILIA>FAMILIA 2</FAMILIA>
</FamiliasDeProductos>
<FamiliasDeProductos>
<CO_FAMILIA>3</CO_FAMILIA>
<FAMILIA>FAMILIA 3</FAMILIA>
</FamiliasDeProductos>
<FamiliasDeProductos>
<CO_FAMILIA>4</CO_FAMILIA>
<FAMILIA>FAMILIA 4</FAMILIA>
</FamiliasDeProductos>

   Ahora un ejemplo con el formato XML EXPLICIT.

SELECT
1 AS TAG, -- La primera columna debe tener el alias TAG
NULL AS PARENT, -- La segunda columna debe tener el alias PARENT
-- El resto de columnas deben tener el alias en el formato:
-- <NombreNodo>!<nodo>!<atributo>
CO_FAMILIA as "FamiliaDeProductos!1!CODIGO_FAMILIA",
FAMILIA as "FamiliaDeProductos!1!DESCRIPCION"
FROM FAMILIAS
ORDER BY FAMILIA
FOR XML EXPLICIT

    Obtenemos el siguiente resultado:

<FamiliaDeProductos CODIGO_FAMILIA="1" DESCRIPCION="FAMILIA 1" />
<FamiliaDeProductos CODIGO_FAMILIA="2" DESCRIPCION="FAMILIA 2" />
<FamiliaDeProductos CODIGO_FAMILIA="3" DESCRIPCION="FAMILIA 3" />
<FamiliaDeProductos CODIGO_FAMILIA="4" DESCRIPCION="FAMILIA 4" />

Campos y variables XML.

    Dado que XML es un tipo nativo de XML podemos definir tablas con campos de tipo XML, variables ...

    El siguiente ejemplo muestra como trabajar con campos y variables XML.

-- Primero creamos una tabla con un campo XML
CREATE TABLE tablaXML
(
ID int not null identity,
DOC xml null,
constraint PK_tablaXML PRIMARY KEY (ID)
)

GO

DECLARE @xml xml -- Variable de tipo XML

-- Leemos los datos de la tabla FAMILIAS
SET @xml = (SELECT CO_FAMILIA, FAMILIA
FROM FAMILIAS FOR XML AUTO)

-- y los guardamos en nuestra tabla
INSERT INTO tablaXML
(DOC) VALUES (@xml)

-- Hacemos lo mismo con los productos
SET @xml = (SELECT *
FROM PRODUCTOS FOR XML AUTO)

INSERT INTO tablaXML
(DOC) VALUES (@xml)

-- Consultamos la tabla y vemos el resultado
SELECT * FROM tablaXML

    Cuando consultemos la tabla tendremos la siguiente información (en mi caso claro!):


<FAMILIAS CO_FAMILIA="1" FAMILIA="FAMILIA 1" />
<FAMILIAS CO_FAMILIA="2" FAMILIA="FAMILIA 2" />
<FAMILIAS CO_FAMILIA="3" FAMILIA="FAMILIA 3" />
<FAMILIAS CO_FAMILIA="4" FAMILIA="FAMILIA 4" />

<PRODUCTOS CO_PRODUCTO="1" CO_CATEGORIA="1" PRODUCTO="PRODUCTO 1" />
<PRODUCTOS CO_PRODUCTO="2" CO_CATEGORIA="1" PRODUCTO="PRODUCTO 2" />
<PRODUCTOS CO_PRODUCTO="3" CO_CATEGORIA="2" PRODUCTO="PRODUCTO 3" />

Inicio | Tutorial de Transact SQL

Versión para imprimir

Foros de consulta

muy interesante por Aldeamedia
Respuesta recibida el [05/07/2007 10:22:47]

Muy interesante, no te olvides de incluir en el próximo articulo la sentencia FOR XML PATH nueva en 2005 y que sustituye a EXPLICIT

Personalmente yo añadiría que FOR XML TYPE se usa para devolver un tipo de dato SQL-XML en vez de un varchar que me parece que  se te ha escapado :)

Pablo ... entra al panel de co... por Anónimo
Respuesta recibida el [05/07/2007 11:43:31]

Pablo ... entra al panel de control y colabora en el puto tutorial!!! ya se que faltan historias (buscar con expresiones XPATH por ejemplo), pero ni el tutorial esta acabado ni tengo tiempo!!!
Lo que mas mira la gente son los ejemplos, así que me he centrado en ello y he pasado un poco de la teria.
Por cierto, a ver si coges el telefono!

Liado por Aldeamedia
Respuesta recibida el [05/07/2007 01:57:59]

Ya sabes que mis horarios son bastante chungos también.
No pensaba escribir en el tutorial por que pensé que querias hacerlo tú si saco un cable a ver si te puedo echar una mando aunuqe ya sabes que yo soy más de teoría y respuestas en los foros que de practicas en los contenidos (aparte de mantener el servidor engrasado :))

		Explicit por eider mauricio aristizabal erazo Respuesta recibida el [11/09/2007 09:14:59]
		En la opcion Explicit el primer campo debe ser un numero entero mayor que 1. Aunque olvidaste comentar el PATH me parecen muy claros tus ejemplos. Gracias!

		Explicit por eider mauricio aristizabal erazo Respuesta recibida el [11/09/2007 09:17:44]
		Correccion: En la opcion Explicit el primer campo debe ser un numero entero mayor que 0. Aunque olvidaste comentar el PATH me parecen muy claros tus ejemplos. Gracias!

		.... por Yo Respuesta recibida el [27/11/2007 07:19:39]
		xD!!!

		Pregunta por Armando Gomez Respuesta recibida el [21/03/2008 12:20:56]
		Cordial saludo, realizo la consulta en sql con "sql for" como dice el articulo , pero el xml resultado sale con errores, NO cierra (/>) los elementos. Que me pasa? gracias

		...... por Yo Respuesta recibida el [28/04/2008 03:37:10]
		...

		kkkkkkkkkkkkk por Mr.K Respuesta recibida el [14/08/2008 02:54:01]
		comprate un perro... demasiado resumido :P

		excelente por nemon, experto en bases de datos Respuesta recibida el [03/11/2008 11:56:47]
		Me parese una seccion excelente no necesita explicacion, y lo del path, complementa esa excelencia

		Muy bueno por Excelente!! Respuesta recibida el [28/05/2009 12:34:44]
		Me parece Una excelente aportación ademas de que muy bien explicado.. Gracias por compartir!

		DUDAS CON MODO TYPE por OSKR Respuesta recibida el [14/07/2009 08:57:10]
		HOLA AMIGO TENGO UN PROBLEMA RELACIONADO CON TYPE EN FOR XML ME DICE QUE ERROR RELACIONADO CON TYPE.........EN SQL2000

		gracias... por Anónimo Respuesta recibida el [31/07/2009 03:49:03]
		gracias

		buenisimo por Anónimo Respuesta recibida el [20/08/2009 01:08:27]
		Muy buenos ejemplos, la verdad e sque uno siempre busca algo como esto. Personas que se solidaricen mostrando álgo más que pura teoría, pues no todos tienen tiempo para leerse todo :(.

		Una dudita por Lara Respuesta recibida el [05/09/2009 12:43:37]
		El código que viene en el apartado Campos y variables XML dónde hay que ponerlo, yo quiero crear un documento XML con todos los datos de todas las tablas de mi BD.

		MUY BUENO... por jAMEX Respuesta recibida el [07/10/2009 11:07:34]
		MUY BUENO

		Muy buen articulo por dani3lla Respuesta recibida el [05/02/2010 02:37:24]
		Felicitaciones los ejemplos son lo suficientemente claros, y como que dan el chispazo para seguir investigando a cerca del tema. Muchas gracias pues a mi me fue muy util