I can't nested some node in XML output

• amadrazo2006

  Old Hand

  Points: 389

  More actions

  November 28, 2013 at 2:30 am

  #74189

  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.