Replace value of node using Xmlquery [message #675468] |
Mon, 01 April 2019 09:40 |
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
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 #675552 is a reply to message #675528] |
Thu, 04 April 2019 17:49 |
Solomon Yakobson
Messages: 3303 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
rs1969 wrote on Wed, 03 April 2019 09:13Can 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
|
|
|
|