Leer Campo XML en SQL Server

Como podemos leer campo XML y filtrar por campos que estan dentro del XML

tenemos esta tabla




como ejemplo tenemos el siguiente XML:




para leer el campo XML de la tabla TabXML , en el campo XMLData, se hace de la siguiente forma

SELECT
ID,
D.C.value('@Nombre', 'Varchar(30)') as Nombre,
D.C.value('@Apellido', 'Varchar(30)') as Apellido,
D.C.value('@telefono', 'Varchar(10)') as Telefono,
D.C.value('@Email', 'Varchar(100)') as Email
FROM TabXML
CROSS APPLY XMLData.nodes('ROOT/VALORES') D(c) -- recuerda que XMLData es el nombre del campo donde esta el XML
WHERE D.c.value('@Nombre', 'varchar(30)') = 'Sergio'

este query mostrara el resultado , todas los registros en donde el nombre sea igual a 'Sergio'

ID Nombre Apellido Fono Email
1 Sergio Valenzuela 555444 Sergio.Valenzuela@engendro.cl
1 Sergio Apell2 555444 Sergio.Valenzuela@engendro.cl
1 Sergio Apell3 555444 Sergio.Valenzuela@engendro.cl

tambien es posible agregar campos de la tabla donde esta el campo XML
si existienran mas registros donde el campo Nombre del XML del campo XMLData sean 'Sergio', entonces mostrara mas registros.

/*************************************************************************

ejemplo Actualizado..... me confundí


DECLARE @TabXML TABLE (
ID INT,
DocXMLData XML
)
DECLARE @XML XML
SELECT @XML = '<ROOT><VALORES Nombre="Sergio" Apellido="Valenzuela" Fono="555444" Email="Sergio.Valenzuela@engendro.cl">
</VALORES>
<VALORES Nombre="Sergio" Apellido="Apell2" Fono="555444" Email="Sergio.Valenzuela@engendro.cl">
</VALORES>
<VALORES Nombre="Sergio" Apellido="Apell3" Fono="555444" Email="Sergio.Valenzuela@engendro.cl">
</VALORES>
<VALORES Nombre="Carlos" Apellido="Apell3" Fono="555444" Email="Sergio.Valenzuela@engendro.cl">
</VALORES>
</ROOT>'

INSERT INTO @TabXML
(
ID,
DocXMLData
)
VALUES
(
1,
@XML
)

SELECT tx.DocXMLData FROM @TabXML tx

SELECT --D.C.value(*)
ID,
D.C.value('@Nombre', 'Varchar(30)') as Nombre,
D.C.value('@Apellido', 'Varchar(30)') as Apellido,
D.C.value('@Fono', 'Varchar(10)') as Telefono,
D.C.value('@Email', 'Varchar(29)') as Email
FROM @TabXML
CROSS APPLY DocXMLData.nodes('ROOT/VALORES') D(c) -- recuerda que XMLData es el nombre del campo donde esta el XML
WHERE D.c.value('@Nombre', 'varchar(30)') = 'Sergio'


*************************************************************************/

PIVOT Dinamico SQL SERVER

Como podemos hacer un PIVOT dinamico en SQL Server??

Lo que vamos a hacer es crear un string que contenga la consulta que vamos a crear con la cantidad de valores que nosotros queramos en forma dinamica

me refiero si tenemos esto


para pivotear esto la documentacion dice que se debe hacer de la siguiente forma
link :
--------------------------------------
ejemplo link:
DaysToManufacture----AverageCost
0-----------------------------5.0885
1-----------------------------223.88
2-----------------------------359.1082
4-----------------------------949.4105

para pivotear

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

el resultado será

Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105

-----------------------------------------------------------

pero si se fijan ahi se debe incluir todos los valores de los dias, es decir que si existen 1000 dias se deben listar de 1 a 1000 días ([0], [1], [2], [3], [4]....[1000]), para hacer esto dinamico y si vemos que no solo pueden ser dias, sino, tipos de documentos, fechas, nombre, cuentas, etc, etc, recordar los nombre y escriibirlos uno a uno no es muy agradable entonces podemos hacer lo siguiente para crear dinamicamente el select que nos mostrará nuestro valores pivoteados tal como queremos.

continuando con mi ejemplo

el query que creará nuestro query es el siguiente:

DECLARE @Tipo VARCHAR(10) -- el largo 10 corresponde con el largo maximo del valor tipo que se pivoteara, si es mayor se debe aumentar
DECLARE @PVT VARCHAR(MAX)
DECLARE @PVT1 VARCHAR(MAX)
DECLARE @PVT2 VARCHAR(MAX)

SELECT @Tipo = MIN(Tipo) FROM TipoValor WHERE Tipo>'' -- select al menor tipo

SET @PVT1 = '' --en esta variable guardaremos los tipos entre comillas ''
SET @PVT2 = '' --en esta variable guardaremos los tipos entre corchetes []

--crearemos un ciclo para recorrer todos los tipos
WHILE NOT(@Tipo IS NULL)-- cuando se acaben los tipos de fechas
BEGIN
IF @PVT1 <> ''
SET @PVT1 = @PVT1 + ', '
SET @PVT1 = @PVT1+''''+@Tipo+'''' --tipos entre comillas

IF @PVT2 <> ''
SET @PVT2 = @PVT2 + ', '
SET @PVT2 = @PVT2+'['+@Tipo+']' --tipos entre corchetes

SELECT @Tipo = MIN(Tipo) FROM TipoValor WHERE Tipo > @Tipo --buscamos el siguente tipo de fechas mayor al anterior
END -- fin del ciclo

-- en la variable @PVT guadaremos como string el query del pivot
SELECT @PVT = 'SELECT Nombre,
'+@PVT2+ --tipos con corchetes
' FROM (SELECT
Nombre
Tipo,
Valor FROM
TipoValor WHERE
Tipo IN
( '+@PVT1+' )) as FEOC --tipos con comillas
PIVOT (MAX(Valor) -- el MAX es por si existe mas de un Tipo, se puede Sumar o agrgar cualquier funcion de agregacion
FOR Tipo IN (
'+@PVT2+' )) as P
GROUP BY
Nombre, '+@PVT2

SELECT @PVT -- Vemos el string donde esta el query del PIVOT completo

EXEC(@PVT) -- con el comando EXEC podemos ejecutar el string, que nos retornara el resultado que queremos

Todo esto nos deberia retornar los siguiente:



asi logramos agrupar por nombre, y pivoteamos el valor y los tipos, donde hay que notar el tipo T3 y que se repite para los nombre nom1 y nom3, donde podemos ver facilmente cuales típos y que valor tiene cada nombre

esta es la mejor opcion de mostrar la informacion, porque la otra opcion y que no es buena de mostrar es la siguiente

Select Nombre, Tipo, Valor from TipoValor order by Nombre

que mostrara algo asi como que no es lo optimo.