SQL SERVER 2014: XML TO TABLE

lunes, 19 de octubre de 2015

CELDA A VARIAS FILAS

A continuación se presenta en código una forma de pasar "bloques" de una sola cadena separada por comas (u otro caracter) a un formato de filas(a una tabla).

Una imagen podría ayudar a entender mejor lo que se quiere hacer.

Una cadena de Instrumentos:

Una cadena a varias filas

para pasar una cadena a varias filas como primer paso la cadena debe ser convertida a un XML, Hagamos un ejemplo con una cadena en una variable...

DECLARE @STR_INSTRUMENTOS VARCHAR (500)

DECLARE @XML_INSTRUMENTOS XML

la primer variable tendrá el texto con información separada por comas
la segunda variable es un tipo de dato XML donde se vaciará el texto ya separado

SET @STR_INSTRUMENTOS = 'IPA BONOS IM, IPA BONOS IP, IPA BONOS IQ, IPA BONOS IS, IPA BONOS IT';

-- PARA COVERTIR A XML

SET @XML_INSTRUMENTOS = CAST(('<Instrumento>' + replace(@STR_INSTRUMENTOS,',','</Instrumento><Instrumento>')+'</Instrumento>') AS XML)

Al revisar la variable, vemos que ya contiene la separación en un formato XML

SELECT @XML_INSTRUMENTOS

Dentro del visor del XML se puede ver el formato del XML

Ahora lo único que se debe hacer es convertir el XML en Table,
es muy fácil, si quieres más detalle aquí te dejo un link XML to TABLE

SELECT A.value ('text()[1]','varchar(30)') as Instrumentos

FROM @XML_INSTRUMENTOS.nodes ('//Instrumento') as T(A)

EJEMPLO COMPLETO:

DECLARE @STR_INSTRUMENTOS VARCHAR (500)

DECLARE @XML_INSTRUMENTOS XML

SET @STR_INSTRUMENTOS = 'IPA BONOS IM, IPA BONOS IP, IPA BONOS IQ, IPA BONOS IS, IPA BONOS IT';

-- PARA COVERTIR A XML

SET @XML_INSTRUMENTOS = CAST(('<Instrumento>'+replace(@STR_INSTRUMENTOS,',','</Instrumento><Instrumento>')+'</Instrumento>') AS XML)

SELECT A.value ('.','varchar(30)') as Instrumentos

FROM @XML_INSTRUMENTOS.nodes ('Instrumento') as T1(A)

EJEMPLO 2

En éste ejemplo se van a separar en filas las columnas:
- ASOCIATE_INSTRUMENT
de la tabla que se muestra a continuación

Donde podemos ver que ya se tiene el XML a un lado de esa columa

ESTA ES LA CONSULTA DE LA TABLA SUPERIOR

SELECT [CLIENT_ID]

,[ASOCIATE_INSTRUMENT]

,INSTRUMENTS = cast(('<IdItem>' + replace([ASOCIATE_INSTRUMENT],',','</IdItem><IdItem>') + '</IdItem>') AS XML)

,[INITIAL_AMOUNT_A]

,[FINAL_AMOUNT_A]

,[RATE_A]

,[TOTAL_FEE]

INTO #TEMP

FROM [Avispa].[Avispa].[TARIFAS_COMISION_ACUMULADA]

LA CONSULTA SE METE EN UNA TABLA TEMPORAL, PARA POSTERIORMENTE EXTRAER UNA CONSULTA..-

El query anterior ya nos entrega los registros

SELECT T2.Instruments,T1.*

FROM #TEMP AS T1

CROSS APPLY (

SELECT A.value ('text()[1]','varchar(30)') as Instruments

FROM T1.INSTRUMENTS.nodes ('IdItem') as T(A)

) AS T2

jueves, 1 de octubre de 2015

XML A TABLA

XML to TABLE

A continuación te mostraré como leer una cadena XMLdesde SQL Server para convertirlo en un formato tipo tabla.
Iniciamos con un XML sencillo:

</cliente>

Para leer el xml que muestro en la parte superior se hace lo siguiente:

SELECT

t.c.value('text()[1]','int') as IdCliente

from @xmlCliente.nodes('//id') as t(c)

@xmlCliente es la variable que contiene la cadena del XML.

Observa que 'text()[1]' es utilizado para leer el valor de los elementos de este XML

En este ejemplo se está accediendo directamente a los nodos <id> => '//id'

Ejemplo completo:

El xml en cadena se asigna a una variable de tipo XML:

DECLARE @xmlCliente as xml

set @xmlcliente =

'<cliente>

</cliente>';

SELECT t.c.value('text()[1]','int') as IdCliente

from @xmlCliente.nodes('//id') as t(c)

= >

EJEMPLO 2

Ahora bien, si dentro de tu XML tienes atributos y deseas leerlos,

se hace de la siguiente manera:

1.- Cuando el Atributo esta en el elemento padre, entonces debemos subir al nodo un nivel,

ya que el ejemplo anterior se colocó directamente en el nodo <Id>.

</cliente>

Se selecciona el nodo del elemento principal <cliente>

con un alias nombre tabla y nombre columna

y de ese mismo nodo principal, ahora en lugar de seleccionar el contenido

del elemento (con 'text()') ahora seleccionamos el valor del atributo 'name'

colocando una @ para atributos.

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

from @xmlCliente.nodes('//cliente') as t(c)

cross apply t.c.nodes('id') as t2(c2)

Para acceder al valor de los elementos, se debe obtener del nodo principal

t.c.nodes, el cual realizamos un cross apply...

EJEMPLO COMPLETO:

DECLARE @xmlCliente as xml

SET @xmlcliente =

</cliente>

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

from @xmlCliente.nodes('//cliente') as t(c)

cross apply t.c.nodes('id') as t2(c2)

2.- Cuando los elementos secundarios contiene atributos:

</cliente>

Solo resta agregar la columna como atributo '@' ya que esos

elementos se tienen en t2(c2)

,t2.c2.value('@value[1]','varchar(1)') as value

EJEMPLO COMPLETO:

DECLARE @xmlCliente as xml

set @xmlcliente =

</cliente>

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

,t2.c2.value('@value[1]','varchar(1)') as value

from @xmlCliente.nodes('//cliente') as t(c)

cross apply t.c.nodes('id') as t2(c2)

Entonces concluimos que el contenido

de un elemento se obtiene con 'text()[1]'

y el valor de un atributo con el prefijo '@'

XML con namespaces

DECLARE @xmlCliente as xml

SET @xmlcliente =

<ns:cliente name="Juanito Perez" xmlns:ns="http://google.com" >

<ns:id>57</ns:id>

<ns:id>58</ns:id>

<ns:id>59</ns:id>

</ns:cliente>

WITH XMLNAMESPACES ( 'http://google.com' as "ns" )

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

from @xmlCliente.nodes('//ns:cliente') as t(c)

cross apply t.c.nodes('ns:id') as t2(c2)

;WITH XMLNAMESPACES
  (
'http://tempuri.org/basketDayArchives.xsd' AS ns
)
SELECT
y.value (N'@val [1]', N'varchar(100)')     AS Valor
,y.value (N'@data [1]', N'varchar(100)')     AS Fecha
,y.value (N'text() [1]', N'varchar(100)')     AS valorElemento

FROM @XML.nodes(N'//ns:Basket') t(c)
OUTER APPLY c.nodes ('ns:BasketList') as r(y)

select @XML