Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

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

Comments

EdStevens

This really has nothing to do with SQL Developer (the subject of this forum). It is a much better fit in SQL & PL/SQL.
And just as a side comment, storing 'age' as data is a flawed design. The 'age' of everyone and every thing is increasing by the day, if not by the second. What is your plan to keep 'age' current? Better to store 'date of birth' and the calculate 'age' when needed at run-time.

User_H3J7U

The 'age' of everyone and every thing is increasing by the day, if not by the second.
Sometimes the age stops increasing.
изображение.png

EdStevens

Pour me one!
Of course, that's not actually the age, but the amount of time it was 'aged' in the barrel before bottling.

1 - 3

Post Details

Added on Jun 4 2022
#bip, #plsql, #sql, #sqldeveloper, #xml
6 comments
439 views
1 mention