Forum Stats

  • 3,825,053 Users
  • 2,260,461 Discussions
  • 7,896,399 Comments

Discussions

Update Attributes based on the text value of node using xmlexists

user8195117
user8195117 Member Posts: 56 Red Ribbon
edited Jul 2, 2020 12:27PM in XML DB

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

  • odie_63
    odie_63 Member Posts: 8,490 Silver Trophy
    edited Jul 1, 2020 4:21AM 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

  • odie_63
    odie_63 Member Posts: 8,490 Silver Trophy
    edited Jul 1, 2020 4:21AM 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;
  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 1, 2020 9:32AM

    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.

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 1, 2020 9:42AM

    Do we require the xmlexists condition as the where condition in the for loop should be enough. Please let me know your suggestion.

  • odie_63
    odie_63 Member Posts: 8,490 Silver Trophy
    edited Jul 1, 2020 10:02AM
    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?

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 1, 2020 10:52AM

    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.

  • odie_63
    odie_63 Member Posts: 8,490 Silver Trophy
    edited Jul 1, 2020 11:08AM
    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 ...;
  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 2, 2020 11:22AM

    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 ''

  • odie_63
    odie_63 Member Posts: 8,490 Silver Trophy
    edited Jul 2, 2020 11:46AM

    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.