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 next 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!
Re: how do I modify XML with a data from a query? [message #676193 is a reply to message #676192] Mon, 20 May 2019 08:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Buchas wrote on Mon, 20 May 2019 05:46

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.

How should I do this? Help please!

From where is the (new) value for FIRST_NAME and SURNAME obtained?
From where does the target XML originate?
Re: how do I modify XML with a data from a query? [message #676194 is a reply to message #676193] Mon, 20 May 2019 08:32 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
New values of FIRST_NAME and SURNAME are generated as hash (to obfuscate identity).
Target XML is given to me from another financial institution.

EDIT - in reality NTNL_ID_NP should be also changed with a value obtained from another DB table. I just tried to simplify all this with a test query.

[Updated on: Mon, 20 May 2019 08:40]

Report message to a moderator

Re: how do I modify XML with a data from a query? [message #676195 is a reply to message #676194] Mon, 20 May 2019 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Buchas wrote on Mon, 20 May 2019 06:32
New values of FIRST_NAME and SURNAME are generated as hash (to obfuscate identity).
Target XML is given to me from another financial institution.
If you desire an actual usable SQL solution, we need to know source table & column names and target table & column names
The choice is yours.
IMO, you are mistaken if you believe that you have posted sufficient details for any working SQL response.
Re: how do I modify XML with a data from a query? [message #676196 is a reply to message #676195] Mon, 20 May 2019 08:46 Go to previous message
Buchas
Messages: 101
Registered: March 2006
Senior Member
The tablename is edoc. It has column edoc_xml which is xmltype. The old xml is in a row with id = 10879603. The modified xml should be placed on top (updated).

[Updated on: Mon, 20 May 2019 08:47]

Report message to a moderator

Previous Topic: Replace value of node using Xmlquery
Next Topic: Help in Query: XML output to Columns using XMLTABLE
Goto Forum:
  


Current Time: Thu Mar 28 10:44:55 CDT 2024