Home » Developer & Programmer » JDeveloper, Java & XML » Replace value of node using Xmlquery (Oracle 11.2.0.3)
Replace value of node using Xmlquery [message #675468] Mon, 01 April 2019 09:40 Go to next message
rs1969
Messages: 24
Registered: December 2007
Location: UK
Junior Member
I'm trying to replace the value of a node within a XML using the corresponding value from another XML. I have tried using XMLQuery but just can't get it to work.

The code attached here is trying to replace the value of node <resourceProvider_MarketParticipant> with the new value, but the resultant output has only the nested element <TimeSeries> (I need the entire contents of originaldata.xml_payload returned with the replaced value).

Also, at some point I want to replace the value of nodes <marketAgreement.mRID> (old value = "$anon_bid_id", new value = "$bid_id") and <registeredResource.mRID> (old value = "$anon_bmu", new value = "$bmu") within the same update statement if possible.

This is my first attempt at XMLQuery, so any help is appreciated Smile

Thanks in advance.

with inputdata (xmldata)
as
(
   select XMLelement("MeritOrderList_MarketDocument"
         ,XMLAttributes(
            'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
            'urn:iec62325.351:tc57wg16:451-7:moldocument:7:1' AS "xmlns",
            'urn:iec62325.351:tc57wg16:451-7:moldocument:7:1 xsd/iec62325-451-7-moldocument_V7_1.xsd' AS "xsi:schemaLocation"
          ), XMLAgg(xmldata)) AS xmldata
     from (
           select xmltype(
                     q'[<TimeSeries>
                     		<marketAgreement.mRID v_old="BTC000000E8" v_new="BTC001"></marketAgreement.mRID>
                     		<resourceProvider_MarketParticipant.mRID v_old="TAC000080B1" v_new="TAC001"></resourceProvider_MarketParticipant.mRID>
                     		<registeredResource.mRID v_old="BMC00000145" v_new="BMC001"></registeredResource.mRID>
                     	</TimeSeries>]') AS xmldata
                     from dual
                     union all
                     select xmltype(
                     q'[<TimeSeries>
                     		<marketAgreement.mRID v_old="BTC000000E9" v_new="BTC002"></marketAgreement.mRID>
                     		<resourceProvider_MarketParticipant.mRID v_old="TAC000080B1" v_new="TAC001"></resourceProvider_MarketParticipant.mRID>
                     		<registeredResource.mRID v_old="BMC00000145" v_new="BMC001"></registeredResource.mRID>
                     	</TimeSeries>]')
                     from dual
          )
)
,originaldata (xml_payload)
as
(SELECT XMLType(q'[<?xml version="1.0" encoding="US-ASCII"?>
<MeritOrderList_MarketDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iec62325.351:tc57wg16:451-7:moldocument:7:1" xsi:schemaLocation="urn:iec62325.351:tc57wg16:451-7:moldocument:7:1 xsd/iec62325-451-7-moldocument_V7_1.xsd">
  <mRID>RRO_TAC000080B1_20190329T1100T</mRID>
  <revisionNumber>1</revisionNumber>
  <createdDateTime>2019-03-28T16:14:29Z</createdDateTime>
  <domain.mRID codingScheme="A01">10Y1001C--00031A</domain.mRID>
  <TimeSeries>
    <marketAgreement.mRID>BTC000000E8</marketAgreement.mRID>
    <marketAgreement.createdDateTime>2019-03-28T16:14:29Z</marketAgreement.createdDateTime>
    <priority>2</priority>
    <resourceProvider_MarketParticipant.mRID codingScheme="A01">TAC000080B1</resourceProvider_MarketParticipant.mRID>
    <registeredResource.mRID codingScheme="A01">BMC00000145</registeredResource.mRID>
    <bid_Period.timeInterval>
      <start>2019-03-29T11:00Z</start>
      <end>2019-03-29T12:00Z</end>
    </bid_Period.timeInterval>
  </TimeSeries>
  <TimeSeries>
    <marketAgreement.mRID>BTC000000E9</marketAgreement.mRID>
    <marketAgreement.createdDateTime>2019-03-28T16:14:29Z</marketAgreement.createdDateTime>
    <priority>2</priority>
    <resourceProvider_MarketParticipant.mRID codingScheme="A01">TAC000080B1</resourceProvider_MarketParticipant.mRID>
    <registeredResource.mRID codingScheme="A01">BMC00000145</registeredResource.mRID>
    <bid_Period.timeInterval>
      <start>2019-03-29T11:00Z</start>
      <end>2019-03-29T12:00Z</end>
    </bid_Period.timeInterval>
  </TimeSeries>
</MeritOrderList_MarketDocument>]')
from dual
)
select XMLQuery(q'[declare default element namespace "urn:iec62325.351:tc57wg16:451-7:moldocument:7:1";
                   for $i in $p/MeritOrderList_MarketDocument/TimeSeries
                       let $anon_party := $i/resourceProvider_MarketParticipant.mRID/@v_old
                       let $ta_id := $i/resourceProvider_MarketParticipant.mRID/@v_new
                       let $anon_bid_id := $i/marketAgreement.mRID/@v_old
                       let $bid_id := $i/marketAgreement.mRID/@v_new
                       let $anon_bmu := $i/registeredResource.mRID/@v_old
                       let $bmu := $i/registeredResource.mRID/@v_new
                       for $j in $q/MeritOrderList_MarketDocument/TimeSeries[resourceProvider_MarketParticipant.mRID = $anon_party]
                          return copy $tmp := $j modify (
                          for $k in $tmp
                              return (replace value of node $k with $ta_id))
                          return $tmp
                ]'
                PASSING x.xmldata AS "p", y.xml_payload AS "q" RETURNING CONTENT
       ) col_2
 from inputdata x
      cross join originaldata y;

[Updated on: Mon, 01 April 2019 10:18]

Report message to a moderator

Re: Replace value of node using Xmlquery [message #675528 is a reply to message #675468] Wed, 03 April 2019 08:13 Go to previous messageGo to next message
rs1969
Messages: 24
Registered: December 2007
Location: UK
Junior Member
Can anyone help?
Re: Replace value of node using Xmlquery [message #675552 is a reply to message #675528] Thu, 04 April 2019 17:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
rs1969 wrote on Wed, 03 April 2019 09:13
Can anyone help?
select XMLQuery(q'[declare default element namespace "urn:iec62325.351:tc57wg16:451-7:moldocument:7:1";
                copy $tmp := $q
                  modify(
                         for $i in $p/MeritOrderList_MarketDocument/TimeSeries
                           let $anon_party := $i/resourceProvider_MarketParticipant.mRID/@v_old
                           let $ta_id := $i/resourceProvider_MarketParticipant.mRID/@v_new
                           let $anon_bid_id := $i/marketAgreement.mRID/@v_old
                           let $bid_id := $i/marketAgreement.mRID/@v_new
                           let $anon_bmu := $i/registeredResource.mRID/@v_old
                           let $bmu := $i/registeredResource.mRID/@v_new
                            for $j in $tmp/MeritOrderList_MarketDocument/TimeSeries[
                                                                                        marketAgreement.mRID = $anon_bid_id
                                                                                    and
                                                                                        resourceProvider_MarketParticipant.mRID = $anon_party
                                                                                    and
                                                                                        registeredResource.mRID = $anon_bmu
                                                                                   ]
                             return (
                                     replace value of node $j/marketAgreement.mRID with $bid_id,
                                     replace value of node $j/resourceProvider_MarketParticipant.mRID with $ta_id,
                                     replace value of node $j/registeredResource.mRID with $bmu
                                    )
                        )
                          return $tmp
                ]'
                PASSING x.xmldata AS "p", y.xml_payload AS "q" RETURNING CONTENT
       ) col_2
 from inputdata x
      cross join originaldata y
/

COL_2
------------------------------------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="WINDOWS-1252"?>
<MeritOrderList_MarketDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iec62325.351:tc57wg16:451-7:moldocument:7:1" xsi:schemaLocation="urn:iec62325.351:tc57wg16:451-7:moldocument:7:1 xsd/iec62325-451-7-moldocument_V7_1.xsd">
  <mRID>RRO_TAC000080B1_20190329T1100T</mRID>
  <revisionNumber>1</revisionNumber>
  <createdDateTime>2019-03-28T16:14:29Z</createdDateTime>
  <domain.mRID codingScheme="A01">10Y1001C--00031A</domain.mRID>
  <TimeSeries>
    <marketAgreement.mRID>BTC001</marketAgreement.mRID>
    <marketAgreement.createdDateTime>2019-03-28T16:14:29Z</marketAgreement.createdDateTime>
    <priority>2</priority>
    <resourceProvider_MarketParticipant.mRID codingScheme="A01">TAC001</resourceProvider_MarketParticipant.mRID>
    <registeredResource.mRID codingScheme="A01">BMC001</registeredResource.mRID>
    <bid_Period.timeInterval>
      <start>2019-03-29T11:00Z</start>
      <end>2019-03-29T12:00Z</end>
    </bid_Period.timeInterval>
  </TimeSeries>
  <TimeSeries>
    <marketAgreement.mRID>BTC002</marketAgreement.mRID>
    <marketAgreement.createdDateTime>2019-03-28T16:14:29Z</marketAgreement.createdDateTime>
    <priority>2</priority>
    <resourceProvider_MarketParticipant.mRID codingScheme="A01">TAC001</resourceProvider_MarketParticipant.mRID>
    <registeredResource.mRID codingScheme="A01">BMC001</registeredResource.mRID>
    <bid_Period.timeInterval>
      <start>2019-03-29T11:00Z</start>
      <end>2019-03-29T12:00Z</end>
    </bid_Period.timeInterval>
  </TimeSeries>
</MeritOrderList_MarketDocument>


SQL>

SY.

[Updated on: Thu, 04 April 2019 17:54]

Report message to a moderator

Re: Replace value of node using Xmlquery [message #675561 is a reply to message #675552] Sat, 06 April 2019 06:34 Go to previous message
rs1969
Messages: 24
Registered: December 2007
Location: UK
Junior Member
Thanks a lot. I'll give it a try on Monday.
Previous Topic: Oracle Java11 - Licensing Details
Next Topic: how do I modify XML with a data from a query?
Goto Forum:
  


Current Time: Sat Dec 14 07:32:34 CST 2019