Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Update Attributes based on the text value of node using xmlexists
Hi,
Below is the XML and there are repeating nested nodes like PD1 below. I have to modify the attributes in the repeating nodes when a text value matches some string. for example update PD1_UPC_CASE (@Error_Flag = Y and @Error_Msg) when xpath (/ROWSET/ROW/XML_DATA/PH1/PD1/PD1_UPC_CASE/text() = "000000000000"). Extractvalue does not work for repeated nodes and below XML update is not working.
The below update is updating all attributes for the node PD1_UPC_CASE which is incorrect. Could some one help me in identifying the correct node and update accordingy
update xml_doc
set xml_data =
xmlquery(
'copy $d := $x modify ( for $i in $d/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = "000000000000"]
return ( replace value of node $i/@Error_Flag with "Y"
)
)
return $d'
passing xml_data as x
returning content
)
where xmlexists('/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = "000000000000"]' passing xml_data)
and file_id = 11973809 AND seq_num = 53650;
Best Answer
-
You were close.
This expression only targets PD1 nodes :
$d/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = "000000000000"]
therefore $i/@Error_Flag doesn't target anything.
Here's one way to do what you want :
update xml_docset xml_data = xmlquery( 'copy $d := . modify ( for $i in $d/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"]/PD1_UPC_CASE where $i = "000000000000" return replace value of node $i/@Error_Flag with "Y" ) return $d' passing xml_data returning content )where xmlexists('/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = "000000000000"]' passing xml_data)and file_id = 11973809 AND seq_num = 53650;
Answers
-
You were close.
This expression only targets PD1 nodes :
$d/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = "000000000000"]
therefore $i/@Error_Flag doesn't target anything.
Here's one way to do what you want :
update xml_docset xml_data = xmlquery( 'copy $d := . modify ( for $i in $d/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"]/PD1_UPC_CASE where $i = "000000000000" return replace value of node $i/@Error_Flag with "Y" ) return $d' passing xml_data returning content )where xmlexists('/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = "000000000000"]' passing xml_data)and file_id = 11973809 AND seq_num = 53650;
-
This works. But we need to find the occurrence of PD1 before the update. Is there anyway... without finding the occurrence ... one can perform the update for the XML defined above.
-
Do we require the xmlexists condition as the where condition in the for loop should be enough. Please let me know your suggestion.
-
user8195117 wrote:Do we require the xmlexists condition as the where condition in the for loop should be enough. Please let me know your suggestion.
The XMLEXISTS condition is there to ensure that the XML document you're accessing for update actually contains the node you're looking for.
Functionally speaking, it could be omitted because the XQuery Update expression includes that condition too.
However it is likely to be more efficient to include it to filter out unwanted rows (especially if there's an XML index on XML_DATA column), rather than trying to update the document regardless.
Do you have issues using XMLEXISTS?
-
Its just that... the complete update is dynamic as the requirement is update multiple nodes based on the condition and so...the xpaths would keep changing.
-
user8195117 wrote:Its just that... the complete update is dynamic as the requirement is update multiple nodes based on the condition and so...the xpaths would keep changing.
Sure, if the target path changes, then the query must change accordingly.
But what does that have to do with using XMLExists or not?
If only the search value changes, however, you can make it a bind variable :
update xml_docset xml_data = xmlquery( 'copy $d := . modify ( for $i in $d/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"]/PD1_UPC_CASE where $i = $val return replace value of node $i/@Error_Flag with "Y" ) return $d' passing xml_data , :1 as "val" returning content )where xmlexists('/ROWSET/ROW/XML_DATA/PH1/PD1[PD1_COUNT="202"][PD1_UPC_CASE = $val]' passing xml_data , :1 as "val")and ...;
-
Hi,
What is wrong in the below update
UPDATE appowner.xml_doc
SET xml_data =
XMLQUERY (
'copy $d := . modify (for $j in $d/ROWSET/ROW/XML_DATA/PH1[PH1_COUNT="1"]/PH1_WSLR_ID where $j="WD134"
return replace value of node $j/@Error_Flag with "Y"
, replace value of node $j/@Error_Msg with "Wholesaler ID is either missing or invalid") return $d'
PASSING xml_data RETURNING CONTENT)
WHERE XMLEXISTS (
'/ROWSET/ROW/XML_DATA/PH1[PH1_COUNT="1"][PH1_WSLR_ID="WD134"]'
PASSING xml_data)
AND file_id = 11839147
AND seq_num = 51817;
Getting the error while executing it
[Error] Execution (2: 17): ORA-19228: XPST0008 - undeclared identifier: prefix 'j' local-name ''
-
You're missing a pair of parentheses around the two update primitives :
copy $d := .
modify (
for $j in $d/ROWSET/ROW/XML_DATA/PH1[PH1_COUNT="1"]/PH1_WSLR_ID
where $j="WD134"
return (
replace value of node $j/@Error_Flag with "YN"
, replace value of node $j/@Error_Msg with "Wholesaler ID is either missing or invalid2"
)
)
return $d
After the return clause of a FLWOR expression, a sequence is expected (XQuery is all about sequence).
To build such a sequence (of nodes, or operations in this case), you only have to wrap them in a sequence constructor.