Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I can't nested some node in XML output Expand / Collapse
Author
Message
Posted Thursday, November 28, 2013 2:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 1:59 AM
Points: 6, Visits: 42
Hi
I have this xml structure
<?xml version='1.0' encoding='iso-8859-1'?>
<Notificaciones>
<Pedidos>
<Pedido ID_PEDIDO="P500000999" COD_SAP="50000999" N_AMPL="001" NOM_NOMBRE="COMP.HOSTELEROS LAS BRUJAS,S.L." CANAL="G" NOM_PROVINCIA="Ciudad Real" FEC_ULT_ERROR="2012-08-09T04:22:10.960" DESC_ULT_ERROR="Error de SAP"/>
<Pedido ID_PEDIDO="P500501358" COD_SAP="50004253" N_AMPL="012" NOM_NOMBRE="COMP.HOSTELEROS LAS BRUJAS,S.L." CANAL="G" NOM_PROVINCIA="Ciudad Real" FEC_ULT_ERROR="2012-08-09T04:22:10.960" DESC_ULT_ERROR="Error de SAP" />
</Pedidos>
<Certificaciones>
<Certificacion ID_CERTF="C50000999" COD_SAP="50000999" N_AMPL="000" NOM_NOMBRE="COMP.HOSTELEROS LAS BRUJAS,S.L." CANAL="G" NOM_PROVINCIA="Ciudad Real" FEC_ULT_ERROR="2012-08-09T04:22:10.960" DESC_ULT_ERROR="Error de SAP" />
<Certificacion ID_CERTF="C0000992" COD_SAP="50000999" N_AMPL="001" NOM_NOMBRE="COMP.HOSTELEROS LAS BRUJAS,S.L." CANAL="G" NOM_PROVINCIA="Ciudad Real" FEC_ULT_ERROR="2012-08-09T04:22:10.960" DESC_ULT_ERROR="Error de SAP"/>
</Certificaciones>
</Notificaciones>

I'm trying to query for data in order to generate the xml acording to previous structure, in this way
/*Query to obtain "Pedidos" node
(SELECT
O.COD_SAP as '@COD_SAP',
O.N_AMPL as '@N_AMPL',
O.CANAL as '@CANAL',
O.NOM_NOMBRE as '@NOM_NOMBRE',
O.NOM_PROVINCIA as '@NOM_PROVINCIA',
S.FEC_ENVIO_SAP as '@FEC_ENVIO_SAP',
S.DESC_ERROR as '@DESC_ERROR'

FROM
(
SELECT
C.COD_SAP,
C.N_AMPL,
CASE WHEN LEFT(C.COD_ACTUACION,2) = '99' THEN 'RR' WHEN TIPO_SEG = '001' THEN 'PPM' ELSE 'MU' END as CANAL,
C.NOM_NOMBRE,
C.NOM_PROVINCIA,
C.COD_DT,
CASE WHEN LEFT(C.COD_ACTUACION,2) = '88' THEN (SELECT COD_ACTUACION_AF FROM GEOS_OBRA_NUEVA_SR WHERE COD_ACTUACION = C.COD_ACTUACION) ELSE C.COD_ACTUACION END AS COD_ACTUACION, --La de pedidos
COALESCE(S1.COD_INTERLOCUTOR_TCM, S2.COD_INTERLOCUTOR_TCM) AS TCM,
COALESCE(S1.COD_INTERLOCUTOR_SUP, S2.COD_INTERLOCUTOR_SUP) AS SUP
FROM GEOS_CONSEJO c
LEFT JOIN GEOS_SEGUIMIENTO_001 S1 on C.COD_SAP = S1.COD_SAP AND C.N_AMPL = S1.N_AMPL AND C.TIPO_SEG = '001'
LEFT JOIN GEOS_SEGUIMIENTO_002 S2 on C.COD_SAP = S2.COD_SAP AND C.N_AMPL = S2.N_AMPL AND C.TIPO_SEG = '002'
WHERE C.COD_ACTUACION IS NOT NULL
) O
INNER JOIN
(
SELECT MAX(ID_REGISTRO) ID_REG, COD_ACTUACION
FROM GEOS_SOLICITUD_PEDIDOS WHERE IND_ESTADO_SAP IN ('4','5')
GROUP BY COD_ACTUACION
) SP on O.COD_ACTUACION = SP.COD_ACTUACION
INNER JOIN GEOS_SOLICITUD_PEDIDOS S ON SP.ID_REG = S.ID_REGISTRO

)

UNION
--- GRANEL
SELECT
O.COD_SAP as '@COD_SAP',
O.N_AMPL as '@N_AMPL',
O.CANAL as '@CANAL',
O.NOM_NOMBRE as '@NOM_NOMBRE',
O.NOM_PROVINCIA as '@NOM_PROVINCIA',
S.FEC_ENVIO_SAP as '@FEC_ENVIO_SAP',
S.DESC_ERROR as '@DESC_ERROR'
FROM
(
SELECT
G.COD_SAP,
'' AS N_AMPL,
'G' AS CANAL,
G.NOM_INSTALACION AS NOM_NOMBRE,
I.DES_PROVINCIA AS NOM_PROVINCIA,
I.COD_DIRECCION_TERRITORIAL,
G.COD_SAP as COD_ACTUACION,
'' as TCM,
G.COD_SUPERVISOR as SUP
FROM GEOS_OBRAS_GRANEL G
INNER JOIN INSTALACIONES I ON G.COD_SAP = I.COD_INSTALACION
) O
INNER JOIN
(
SELECT MAX(ID_REGISTRO) ID_REG, RIGHT(ELEM_PEP,8) as COD_ACTUACION
FROM GEOS_SOLICITUD_PEDIDOS WHERE IND_ESTADO_SAP IN ('4','5') AND LEFT(ELEM_PEP,1) IN ('G','H') AND COD_INTERLOCUTOR LIKE '13000%'
GROUP BY RIGHT(ELEM_PEP,8)
) SP on O.COD_SAP = SP.COD_ACTUACION
INNER JOIN GEOS_SOLICITUD_PEDIDOS S ON SP.ID_REG = S.ID_REGISTRO
FOR XML PATH('Pedido'), ROOT ('Pedidos');


/* Query to obtain "Certificaciones" node

(SELECT
O.COD_SAP as '@COD_SAP',
O.N_AMPL as '@N_AMPL',
O.CANAL as '@CANAL',
O.NOM_NOMBRE as '@NOM_NOMBRE',
O.NOM_PROVINCIA as '@NOM_PROVINCIA',
S.FEC_ENVIO_SAP as '@FEC_ENVIO_SAP',
S.DESC_ERROR as '@DESC_ERROR'
FROM
(
SELECT
C.COD_SAP,
C.N_AMPL,
CASE WHEN LEFT(C.COD_ACTUACION,2) = '99' THEN 'RR' WHEN TIPO_SEG = '001' THEN 'PPM' ELSE 'MU' END as CANAL,
C.NOM_NOMBRE,
C.NOM_PROVINCIA,
C.COD_DT,
CASE WHEN LEFT(C.COD_ACTUACION,2) = '88' THEN (SELECT COD_ACTUACION_AF FROM GEOS_OBRA_NUEVA_SR WHERE COD_ACTUACION = C.COD_ACTUACION) ELSE C.COD_ACTUACION END AS COD_ACTUACION, --La de pedidos
COALESCE(S1.COD_INTERLOCUTOR_TCM, S2.COD_INTERLOCUTOR_TCM) AS TCM,
COALESCE(S1.COD_INTERLOCUTOR_SUP, S2.COD_INTERLOCUTOR_SUP) AS SUP
FROM GEOS_CONSEJO c
LEFT JOIN GEOS_SEGUIMIENTO_001 S1 on C.COD_SAP = S1.COD_SAP AND C.N_AMPL = S1.N_AMPL AND C.TIPO_SEG = '001'
LEFT JOIN GEOS_SEGUIMIENTO_002 S2 on C.COD_SAP = S2.COD_SAP AND C.N_AMPL = S2.N_AMPL AND C.TIPO_SEG = '002'
WHERE C.COD_ACTUACION IS NOT NULL
) O
INNER JOIN
(
SELECT MAX(FEC_ENVIO_SAP) AS FEC_ENVIO, COD_SAP + '-' + N_AMPL as OBRA
FROM GEOS_CABECERA_ACEP_SERVICIOS WHERE IND_ESTADO_SAP IN ('4','5')
GROUP BY COD_SAP + '-' + N_AMPL
) SP on o.COD_SAP + '-' + o.N_AMPL = SP.OBRA
INNER JOIN GEOS_CABECERA_ACEP_SERVICIOS S ON S.COD_SAP + '-' + S.N_AMPL = SP.OBRA AND S.FEC_ENVIO_SAP = SP.FEC_ENVIO
)
UNION
--- GRANEL
SELECT
O.COD_SAP as '@COD_SAP',
O.N_AMPL as '@N_AMPL',
O.CANAL as '@CANAL',
O.NOM_NOMBRE as '@NOM_NOMBRE',
O.NOM_PROVINCIA as '@NOM_PROVINCIA',
S.FEC_ENVIO_SAP as '@FEC_ENVIO_SAP',
S.DESC_ERROR as '@DESC_ERROR'
FROM
(
SELECT
G.COD_SAP,
'' AS N_AMPL,
'G' AS CANAL,
G.NOM_INSTALACION AS NOM_NOMBRE,
I.DES_PROVINCIA AS NOM_PROVINCIA,
I.COD_DIRECCION_TERRITORIAL,
G.COD_SAP as COD_ACTUACION,
'' as TCM,
G.COD_SUPERVISOR as SUP
FROM GEOS_OBRAS_GRANEL G
INNER JOIN INSTALACIONES I ON G.COD_SAP = I.COD_INSTALACION
) O
INNER JOIN
(
SELECT MAX(FEC_ENVIO_SAP) AS FEC_ENVIO, COD_SAP + '-' + N_AMPL as OBRA
FROM GEOS_CABECERA_ACEP_SERVICIOS WHERE IND_ESTADO_SAP IN ('4','5')
GROUP BY COD_SAP + '-' + N_AMPL
) SP on o.COD_SAP + '-' + o.N_AMPL = SP.OBRA
INNER JOIN GEOS_CABECERA_ACEP_SERVICIOS S ON S.COD_SAP + '-' + S.N_AMPL = SP.OBRA AND S.FEC_ENVIO_SAP = SP.FEC_ENVIO
FOR XML PATH('Certificacion'), ROOT ('Certificaciones');

But in this way I obtain both nodes "Pedidos" and "Certificaciones" separated, and I need to have both nodes in the same file. I need obtain the root "Notificaciones" node like a Parent node , because both "Pedidos" and "Certificaciones" node acording to previous structure they are not root nodes.
I don't know if I explained my problem properly, because I'm not write english language well.
I expect somebody help me.
Thanks in advance.
Arsenio.
Post #1518295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse