SQL & PL/SQL

How to convert a SELECT in XML in a hierarchical way?

Edisson Gabriel LópezJun 4 2022

Hi,
I have the following output of my SELECT I did it with dual to simulate and the same query logic I put in the following discussion:
How to add in Hierarchial or in tree? — oracle-tech
Then I need the result of that SELECT to be returned to me in CLOB in XML. Since Oracle BI Publisher works with XML:

WITH
    data AS          -- dataset simulation
        (
Select '1' LVL,  'ESF_A' PK1_START_VALUE,  '' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'ACTIVOS' DESCRIPTION,  '39190828556' SUM_TOTAL_ONE,  '-25229236703' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '2' LVL,  'ESF_ACT' PK1_START_VALUE,  'ESF_A' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'ACTIVO' DESCRIPTION,  '39190828556' SUM_TOTAL_ONE,  '-25229236703' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_AC' PK1_START_VALUE,  'ESF_ACT' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Activo corriente' DESCRIPTION,  '39020965705' SUM_TOTAL_ONE,  '-25229281532' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_ADCC' PK1_START_VALUE,  'ESF_AC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Deudores comerciales y otras cuentas por cobrar' DESCRIPTION,  '37908898836' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_AEE' PK1_START_VALUE,  'ESF_AC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Efectivo y equivalentes de efectivo' DESCRIPTION,  '-8270170' SUM_TOTAL_ONE,  '-2011950' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_AIM' PK1_START_VALUE,  'ESF_AC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Inventario de minerales' DESCRIPTION,  '134677932' SUM_TOTAL_ONE,  '-25227269582' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_AIMC' PK1_START_VALUE,  'ESF_AC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Activos por impuestos corrientes' DESCRIPTION,  '985659107' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_AIMT' PK1_START_VALUE,  'ESF_AC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Inventario de materiales' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_ANC' PK1_START_VALUE,  'ESF_ACT' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Activo no corriente' DESCRIPTION,  '169862851' SUM_TOTAL_ONE,  '44829' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_ADIR' PK1_START_VALUE,  'ESF_ANC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Diferido por impuesto de renta' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_AINT' PK1_START_VALUE,  'ESF_ANC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Intangibles' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_ANCC' PK1_START_VALUE,  'ESF_ANC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Deudores comerciales y otras cuentas por cobrar' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_ANPE' PK1_START_VALUE,  'ESF_ANC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Propiedad, planta y equipo, neto' DESCRIPTION,  '169862851' SUM_TOTAL_ONE,  '44829' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_APEE' PK1_START_VALUE,  'ESF_ANC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Proyecto de exploraciÃ³n y evaluaciÃ³n' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_A' PARENT  From DUAL UNION ALL
Select '1' LVL,  'ESF_PYP' PK1_START_VALUE,  '' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'PASIVOS Y PATRIMONIO' DESCRIPTION,  '-16924393691' SUM_TOTAL_ONE,  '-3789583' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '2' LVL,  'ESF_EQY' PK1_START_VALUE,  'ESF_PYP' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Patrimonio' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_ECE' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Capital emitido' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_EGPA' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Ganancias (pÃ©rdidas) acumuladas' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '37050103' PK1_START_VALUE,  'ESF_EGPA' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Ganancia O Perdida De Resultados Anteriores' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_EORI' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Otro resultado integral (ESFA)' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '37050102' PK1_START_VALUE,  'ESF_EORI' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Utilidades Retenidas Por Conversion Niif' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_EPCA' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Prima en colocaciÃ³n de acciones' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '32050501' PK1_START_VALUE,  'ESF_EPCA' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Prima En Colocacion De Acciones' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_ER' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Reservas ' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '33050501' PK1_START_VALUE,  'ESF_ER' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Reserva Legal' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '33050502' PK1_START_VALUE,  'ESF_ER' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Otras Reserva Legal' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '33150501' PK1_START_VALUE,  'ESF_ER' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Para Beneficencia Y Civismo' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '33150502' PK1_START_VALUE,  'ESF_ER' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Para Futuros Ensanches' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '33150503' PK1_START_VALUE,  'ESF_ER' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Adquis  0 Reposicion De Prop P Y E ' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '33150504' PK1_START_VALUE,  'ESF_ER' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Expansion De La Operacion-Reposic De Activos' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_EREA' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Resultados de ejercicios anteriores ' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '37050101' PK1_START_VALUE,  'ESF_EREA' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Utilidades Acumuladas' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_ERNP' PK1_START_VALUE,  'ESF_EQY' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Resultado neto del perÃodo' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  '36050501' PK1_START_VALUE,  'ESF_ERNP' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Utilidades Del Ejercicio' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '2' LVL,  'ESF_PS' PK1_START_VALUE,  'ESF_PYP' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Pasivo' DESCRIPTION,  '-16924393691' SUM_TOTAL_ONE,  '-3789583' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_PNC' PK1_START_VALUE,  'ESF_PS' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Pasivos no corriente' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PNAP' PK1_START_VALUE,  'ESF_PNC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Acreedores y otras cuentas por pagar' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PNBE' PK1_START_VALUE,  'ESF_PNC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Pasivo por beneficios a empleados' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PNCV' PK1_START_VALUE,  'ESF_PNC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Cuentas por pagar compaÃ±Ãas vinculadas' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PNOP' PK1_START_VALUE,  'ESF_PNC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Otros Pasivos' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PPD' PK1_START_VALUE,  'ESF_PNC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'ProvisiÃ³n por desmantelamiento' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '3' LVL,  'ESF_PSC' PK1_START_VALUE,  'ESF_PS' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Pasivos corriente' DESCRIPTION,  '-16924393691' SUM_TOTAL_ONE,  '-3789583' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PACP' PK1_START_VALUE,  'ESF_PSC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Acreedores y otras cuentas por pagar' DESCRIPTION,  '-1099337488' SUM_TOTAL_ONE,  '-2683771' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PBE' PK1_START_VALUE,  'ESF_PSC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Pasivo por beneficios a empleados' DESCRIPTION,  '-15828782849' SUM_TOTAL_ONE,  '-1421012' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PCPV' PK1_START_VALUE,  'ESF_PSC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Cuentas por pagar compaÃ±Ãas vinculadas' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PCYP' PK1_START_VALUE,  'ESF_PSC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'CrÃ©ditos y prÃ©stamos' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_PIC' PK1_START_VALUE,  'ESF_PSC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Pasivos por impuesto corriente' DESCRIPTION,  '3726646' SUM_TOTAL_ONE,  '315200' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL UNION ALL
Select '4' LVL,  'ESF_POP' PK1_START_VALUE,  'ESF_PSC' PARENT_PK1_VALUE,  '300000002652023' LEGAL_ENTITY_ID,  'Otros Pasivos' DESCRIPTION,  '0' SUM_TOTAL_ONE,  '0' SUM_TOTAL_TWO,  'ESF_PYP' PARENT  From DUAL
) 
select * from data

The result I need in XML would be something similar to this in hierarchy by levels, but instead of SQL as it is now, I need it as follows:

<PARENT_PRINCIPAL>
	<LVL>1</LVL>
	<PK1_START_VALUE>ESF_A</PK1_START_VALUE>
	<LEGAL_ENTITY_ID>300000002652023</LEGAL_ENTITY_ID>
	<DESCRIPTION>ACTIVOS</DESCRIPTION>
	<SUM_TOTAL_ONE>39190828556</SUM_TOTAL_ONE>
	<SUM_TOTAL_TWO>-25229236703</SUM_TOTAL_TWO>
	<PARENT>ESF_A</PARENT>
	<CHILD>
		<LVL>2</LVL>
		<PK1_START_VALUE>ESF_ACT</PK1_START_VALUE>
		<PARENT_PK1_VALUE>ESF_A</PARENT_PK1_VALUE>
		<LEGAL_ENTITY_ID>300000002652023</LEGAL_ENTITY_ID>
		<DESCRIPTION>ACTIVO</DESCRIPTION>
		<SUM_TOTAL_ONE>39190828556</SUM_TOTAL_ONE>
		<SUM_TOTAL_TWO>-25229236703</SUM_TOTAL_TWO>
		<PARENT>ESF_A</PARENT>
		<CHILD>
			<LVL>3</LVL>
			<PK1_START_VALUE>ESF_AC</PK1_START_VALUE>
			<PARENT_PK1_VALUE>ESF_ACT</PARENT_PK1_VALUE>
			<LEGAL_ENTITY_ID>300000002652023</LEGAL_ENTITY_ID>
			<DESCRIPTION>Activo corriente</DESCRIPTION>
			<SUM_TOTAL_ONE>39020965705</SUM_TOTAL_ONE>
			<SUM_TOTAL_TWO>-25229281532</SUM_TOTAL_TWO>
			<PARENT>ESF_A</PARENT>
			<CHILD>
				<LVL>4</LVL>
				<PK1_START_VALUE>ESF_ADCC</PK1_START_VALUE>
				<PARENT_PK1_VALUE>ESF_AC</PARENT_PK1_VALUE>
				<LEGAL_ENTITY_ID>300000002652023</LEGAL_ENTITY_ID>
				<DESCRIPTION>Deudores comerciales y otras cuentas por cobrar</DESCRIPTION>
				<SUM_TOTAL_ONE>37908898836</SUM_TOTAL_ONE>
				<SUM_TOTAL_TWO>0</SUM_TOTAL_TWO>
				<PARENT>ESF_A</PARENT>
			</CHILD>
		</CHILD>
	</CHILD>
<PARENT_PRINCIPAL>
<PARENT_PRINCIPAL>
	<LVL>1</LVL>
	<PK1_START_VALUE>ESF_PYP</PK1_START_VALUE>
	<LEGAL_ENTITY_ID>300000002652023</LEGAL_ENTITY_ID>
	<DESCRIPTION>PASIVOS Y PATRIMONIO</DESCRIPTION>
	<SUM_TOTAL_ONE>-16924393691</SUM_TOTAL_ONE>
	<SUM_TOTAL_TWO>-3789583</SUM_TOTAL_TWO>
	<PARENT>ESF_PYP</PARENT>
	<CHILD>
		<LVL>2</LVL>
		<PK1_START_VALUE>ESF_EQY</PK1_START_VALUE>
		<PARENT_PK1_VALUE>ESF_PYP</PARENT_PK1_VALUE>
		<LEGAL_ENTITY_ID>300000002652023</LEGAL_ENTITY_ID>
		<DESCRIPTION>Patrimonio</DESCRIPTION>
		<SUM_TOTAL_ONE>0</SUM_TOTAL_ONE>
		<SUM_TOTAL_TWO>0</SUM_TOTAL_TWO>
		<PARENT>ESF_PYP</PARENT>
	</CHILD>
<PARENT_PRINCIPAL>

I don't know if it is possible to have this XML result in CLOB. If I am doing something wrong, I welcome suggestions.

Thank you.

This post has been answered by Solomon Yakobson on Jun 4 2022

Jump to Answer

Added on Jun 4 2022

#bip, #plsql, #sql, #sqldeveloper, #xml

6 comments

163 views

1 mention