how do I modify XML with a data from a query? [message #676192] |
Mon, 20 May 2019 07:46 |
Buchas
Messages: 101 Registered: March 2006
|
Senior Member |
|
|
Hello,
I have XML (real world is ~10MB size):
<?xml version="1.0" encoding="UTF-8"?>
<message:StructureSpecificData xmlns:message="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message" xmlns:data="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/structurespecific" xmlns:common="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:BOL_RIAD_CTP="http://www.lb.lt/stattistic/anacredit/BOL_RIAD_CTP">
<message:Header>
<message:ID>2019010400000599</message:ID>
</message:Header>
<message:DataSet data:structureRef="BOL_RIAD_ENTTY_C" xsi:type="BOL_RIAD_CTP:BOL_RIAD_ENTTY_C" data:dataScope="DataStructure" data:action="Replace">
<Obs ENTTY_PRDB_CD="00180386" TYP_ENTTY="5" NTNL_ID_NP="37001" FIRST_NAME="Franklin" SURNAME="Dikinis" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180393" TYP_ENTTY="5" NTNL_ID_NP="45110" FIRST_NAME="Geidvilė" SURNAME="Šilobritienė" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180397" TYP_ENTTY="5" NTNL_ID_NP="36907" FIRST_NAME="Reimondas" SURNAME="Šiaudinis" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180400" TYP_ENTTY="5" NTNL_ID_NP="37109" FIRST_NAME="Dylanas" SURNAME="Jonevičius" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180402" TYP_ENTTY="5" NTNL_ID_NP="38101" FIRST_NAME="Samuel" SURNAME="Špėlys" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180403" TYP_ENTTY="5" NTNL_ID_NP="46612" FIRST_NAME="Jurmena" SURNAME="Hidalgo" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180414" TYP_ENTTY="5" NTNL_ID_NP="37801" FIRST_NAME="Simonas" SURNAME="ARSTIKAITIS" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180418" TYP_ENTTY="5" NTNL_ID_NP="47212" FIRST_NAME="Ori" SURNAME="Šilingienė" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180419" TYP_ENTTY="5" NTNL_ID_NP="48009" FIRST_NAME="Eirina" SURNAME="Volockienė" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180434" TYP_ENTTY="5" NTNL_ID_NP="45610" FIRST_NAME="Danuta" SURNAME="Grubinskaitė" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180436" TYP_ENTTY="5" NTNL_ID_NP="48205" FIRST_NAME="Fajina" SURNAME="BERZINSKAITE" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
<Obs ENTTY_PRDB_CD="00180439" TYP_ENTTY="5" NTNL_ID_NP="47804" FIRST_NAME="Agneška" SURNAME="Tervydienė" CNTRY_CTZNSHP_NP="LT" TYP_NTNL_ID_NP="LT_PC" CNTRY="LT" INSTTTNL_SCTR="S14_B" LGL_PRCDNG_STTS="1"/>
</message:DataSet>
</message:StructureSpecificData>
I need to update FIRST_NAME and SURNAME in XML with query results where NTNL_ID_NP column matches NTNL_ID_NP attribute. This attribute in XML is unique.
Test query:
with new_data as (
select level,
decode(level,1,'Pirmas',2,'Antras',3,'Trecias',4,'Ketvirtas',5,'Penktas') NEW_FIRST_NAME,
decode(level,1,'Pirmasis',2,'Antrasis',3,'Treciasis',4,'Ketvirtasis',5,'Penktasis') NEW_SURNAME,
decode(level,1,'37801',2,'37001',3,'48205',4,'47212',5,'36907') NTNL_ID_NP
from dual connect by level <= 5
)
select * from new_data,dual
How should I do this? Help please!
|
|
|
|
|
|
|