SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I can't nested some node in XML output


I can't nested some node in XML output

Author
Message
amadrazo2006
amadrazo2006
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 44
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search