Home » Developer & Programmer » JDeveloper, Java & XML » how do I modify XML with a data from a query? (Enterprise Edition Release 12.1.0.2.0)
how do I modify XML with a data from a query? [message #676192] Mon, 20 May 2019 07:46 Go to previous message
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!
 
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Replace value of node using Xmlquery
Next Topic: Help in Query: XML output to Columns using XMLTABLE
Goto Forum:
  


Current Time: Fri Mar 29 07:13:32 CDT 2024