1 2 3 Previous Next 32 Replies Latest reply: Jul 11, 2012 9:41 AM by 548016 RSS

    how to search and replace only tag values in xml and not tag columns?

    548016
      Hi,

      I am new to xml db and i have a scenario where i need to search and replace xml content. The search is based on a plain text and not on any column.

      I have a table as follows:

      ID VARCHAR2(32 BYTE),
      MESSAGE_TYPE VARCHAR2(64 BYTE),
      MESSAGE_CONTENT XMLTYPE,
      REJECTED_REASON VARCHAR2(256 BYTE)

      And Sample XML as:

      <?xml version="1.0" encoding="US-ASCII"?>
      <MessageEnvelope>
      <Header>
      <Partner>Renault</Partner>
      <MessageType>Release</MessageType>
      <PartnerMessageType>S74</PartnerMessageType>
      <MessageTime>2001-12-17T09:30:47.0Z</MessageTime>
      <LinkToRawMessage>String</LinkToRawMessage>
      </Header>
      <Body>
      <Release>
      <Addresses>
      <DealerDestAddr>ABCD</DealerDestAddr>
      <DestAddr>ABCD</DestAddr>
      <NextDestAddr/>
      <StartAddr/>
      </Addresses>
      <Assignment/>
      <ClientStatus/>
      <Dates/>
      <HoldInfo>
      <HoldCode>HoldTest</HoldCode>
      </HoldInfo>
      <Message/>
      <Partner>
      <OrderGiverCode>CMR00BCV</OrderGiverCode>
      </Partner>
      <ToDo/>
      <Transport/>
      <Vehicle>
      <VIN>W0LGDM9A_Ran11115</VIN>
      </Vehicle>
      </Release>
      </Body>
      </MessageEnvelope>


      I am executing the foll query:

      UPDATE t_xml D SET D.MESSAGE_CONTENT = replace( D.MESSAGE_CONTENT, 'ABCD', 'Chennai' )
      WHERE d.MESSAGE_CONTENT.existsNode('//*[*="ABCD"]') = 1;

      This works fine and replaces both <DealerDestAddr>ABCD</DealerDestAddr> and <DestAddr>ABCD</DestAddr> tag values to Chennai. But the problem i face is if there is a node with tag name <ABCD> also gets modified to <Chennai>. Please help me to rectify this issue.

      Regards,
      Sprightee
        • 1. Re: how to search and replace only tag values in xml and not tag columns?
          odie_63
          Hi,

          If you know which nodes(s) to modify, it's better to be explicit :
          UPDATE t_xml d 
          SET d.message_content = 
              updateXML(
                d.message_content
              , '/MessageEnvelope/Body/Release/Addresses/DealerDestAddr/text()', 'Chennai' 
              , '/MessageEnvelope/Body/Release/Addresses/DestAddr/text()', 'Chennai'
              )
          WHERE existsNode(d.message_content, '//*[.="ABCD"]') = 1
          ;
          If you want to update any occurrence, then :
          UPDATE t_xml d 
          SET d.message_content = 
              updateXML(
                d.message_content
              , '//*[.="ABCD"]/text()'
              , 'Chennai'
              )
          WHERE existsNode(d.message_content, '//*[.="ABCD"]') = 1
          ;
          • 2. Re: how to search and replace only tag values in xml and not tag columns?
            548016
            Hi,

            Thanks for your reply. It was very useful to resolve my problem.

            As you mentioned as an alternate approach to mention the appropriate tag name as '/MessageEnvelope/Body/Release/Addresses/DealerDestAddr', i have one doubt. When i try to fetch the record with the following query it does not work.

            select * from t_xml d where existsNode(d.MESSAGE_CONTENT,'/MessageEnvelope/Body/Release/Addresses[DealerDestAddr="Chennai"]')=1 returned no rows.

            What am i missing in this?

            Regards,
            Sprightee
            • 3. Re: how to search and replace only tag values in xml and not tag columns?
              odie_63
              Stupid question but did you run the query before of after the update?
              • 4. Re: how to search and replace only tag values in xml and not tag columns?
                548016
                Hi,

                Before the update itself the query did not work.

                select * from t_xml x where existsNode(x.MESSAGE_CONTENT,'/MessageEnvelope/Body/Release/Addresses[DealerDestAddr="Chennai"]')=1

                I tried to fetch other nodes also and doesnot seem to work :(

                select * from t_xml x where x.MESSAGE_CONTENT.existsNode('/MessageEnvelope/Header[MessageType="Release"]')=1;
                • 5. Re: how to search and replace only tag values in xml and not tag columns?
                  548016
                  Hi,

                  Before the update itself the query did not work.

                  select * from t_xml x where existsNode(x.MESSAGE_CONTENT,'/MessageEnvelope/Body/Release/Addresses[DealerDestAddr="Chennai"]')=1

                  I tried to fetch other nodes also and doesnot seem to work :(

                  select * from t_xml x where x.MESSAGE_CONTENT.existsNode('/MessageEnvelope/Header[MessageType="Release"]')=1;
                  • 6. Re: how to search and replace only tag values in xml and not tag columns?
                    odie_63
                    Before the update itself the query did not work.
                    Well, by definition, before the update, the value is not "Chennai" so no wonder the query retrieves no row.

                    It should work after the update though.
                    • 7. Re: how to search and replace only tag values in xml and not tag columns?
                      548016
                      Hi,

                      I'm sorry for the wrong values.

                      The xml contains
                      <?xml version="1.0" encoding="US-ASCII"?>
                      <MessageEnvelope xsi:schemaLocation="http://www.groupecat.com/CLV2/MessageEnvelope/20120402 MessageEnvelope.xsd" xmlns="http://www.groupecat.com/CLV2/MessageEnvelope/20120402" xmlns:mt="http://www.groupecat.com/CLV2/MessageTypes/20120402" xmlns:re="http://www.groupecat.com/CLV2/Messages/Release/20120402" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                      <Header>
                      <Partner>Renault123</Partner>
                      *<MessageType>Release</MessageType>*
                      <PartnerMessageType>S74</PartnerMessageType>
                      <MessageTime>2001-12-17T09:30:47.0Z</MessageTime>
                      <LinkToRawMessage>String</LinkToRawMessage>
                      </Header>
                      .....

                      But my below query returns no record
                      select * from t_xml x where x.MESSAGE_CONTENT.existsNode('/MessageEnvelope/Header[MessageType="Release"]')=1;

                      How to retrieve the value of the tags?
                      • 8. Re: how to search and replace only tag values in xml and not tag columns?
                        odie_63
                        The real XML document has a default namespace, why not mention it in the first place?

                        This one should work then :
                        select * 
                        from t_xml x 
                        where existsNode( x.MESSAGE_CONTENT
                                        , '/MessageEnvelope/Header[MessageType="Release"]'
                                        , 'xmlns="http://www.groupecat.com/CLV2/MessageEnvelope/20120402"' ) = 1
                        ;
                        • 9. Re: how to search and replace only tag values in xml and not tag columns?
                          548016
                          Many Thanks.
                          How to get the values of nodes inside body tag.
                          Say for ex, how to get <re:DealerDestAddr>ABCD</re:DealerDestAddr>

                          I tried
                          select * from t_xml x where existsNode(x.MESSAGE_CONTENT,'/MessageEnvelope/Body/Release/Addresses[DealerDestAddr="ABCD"]',
                          'xmlns="http://www.groupecat.com/CLV2/MessageEnvelope/20120402"' )=1

                          and also
                          select * from t_xml x where existsNode(x.MESSAGE_CONTENT,'/MessageEnvelope/Body/Release/Addresses[DealerDestAddr="ABCD"]',
                          'xmlns:re="http://www.groupecat.com/CLV2/Messages/Release/20120402"')=1
                          • 10. Re: how to search and replace only tag values in xml and not tag columns?
                            odie_63
                            The rule to correctly write an XPath expression with namespaces is simple :

                            1- start from the root element and walk down the tree to the target node
                            2- for each step, ask yourself this question : does the current element I'm on belong to a namespace? If yes, add the namespace prefix before the element name, or leave it as it is if it's the default namespace.
                            3- declare all namespaces you have used in the XPath expression in the corresponding parameter of the function :

                            <tt>xmlns:ns0="some.namespace.0", xmlns:ns1="some.namespace.1", ...</tt> for prefixed namespaces
                            <tt>xmlns="my.default.namespace"</tt> for the default namespace

                            There are also more complex cases, for example when the default namespace is undeclared in the middle of the document, then we have to give the default namespace a prefix too and refer to it this way in the XPath.

                            In the present case, you might you need something like this :
                            select * 
                            from t_xml x 
                            where existsNode( x.MESSAGE_CONTENT
                                            , '/MessageEnvelope/Body/re:Release/re:Addresses[re:DealerDestAddr="ABCD"]',
                                              'xmlns="http://www.groupecat.com/CLV2/MessageEnvelope/20120402",
                                               xmlns:re="http://www.groupecat.com/CLV2/Messages/Release/20120402"' ) = 1
                            ;
                            but I can't be sure since you didn't give the complete XML document.
                            • 11. Re: how to search and replace only tag values in xml and not tag columns?
                              548016
                              Hi,

                              I'll follow your approach and try. Meanwhile i have a doubt. Cant we search the xml content with only tag names, without using default namespace. Say, my end user knows only the tag name <MessageType> and not the namespace and other technical stuff. For him, it is just like a string search.
                              Can this be achieved?
                              • 12. Re: how to search and replace only tag values in xml and not tag columns?
                                odie_63
                                Yes, you can use namespace wildcards too :
                                select *
                                from t_xml
                                where existsnode(message_content, '//*:MessageType[.="Release"]') = 1
                                ;
                                But in case you're using a structured or binary XML storage, bear in mind that's not a recommended approach because it forces a functional evaluation of the XPath expression, and decreases performance when used over large XMLs.
                                • 13. Re: how to search and replace only tag values in xml and not tag columns?
                                  548016
                                  Hi,
                                  I have been using the UpdateXML command to replace content of xml stored in XMLType. It was workinf absolutely fine. But now i'm getting an exception as follows:

                                  ORA-00600: internal error code, arguments: [qmcxeUpdateXml:2.1], [], [], [], [], [], [], []

                                  The table uses a Free Test Index.

                                  I searched the forum and found from the given link ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [ that corrupted Index may be the cause of this problem. I'm still clueless :(

                                  Can someone help me?
                                  • 14. Re: how to search and replace only tag values in xml and not tag columns?
                                    548016
                                    Hi,

                                    I am using UpdateXML to search a node and replace which is working fine.

                                    update t_xml x set x.message_content=
                                    updateXML(x.message_content,
                                    '/MessageEnvelope/Body/Announcement/ns3:Addresses/ns3:DealerDestAddr/ns2:SiteCode/text()','test',
                                    'xmlns="http://www.groupecat.com/CLV2/MessageEnvelope/20120501",
                                    xmlns:ns3="http://www.xxx.com/CLV2/Messages/Announcement/20120501",
                                    xmlns:ns2="http://www.xxx.com/CLV2/MessageTypes/20120501" '
                                    )
                                    where existsNode( x.MESSAGE_CONTENT,
                                    '/MessageEnvelope/Body/Announcement/ns3:Addresses/ns3:DealerDestAddr[ns2:SiteCode="aaa"]',
                                    'xmlns="http://www.xxx.com/CLV2/MessageEnvelope/20120501",
                                    xmlns:ns3="http://www.xxx.com/CLV2/Messages/Announcement/20120501",
                                    xmlns:ns2="http://www.xxx.com/CLV2/MessageTypes/20120501" ' ) = 1


                                    I am not able to get the namespacer prefix for my tag name from UI. For ex ns2:SiteCode is simply SiteCode.

                                    In the existsNode function i'm able to search without namesapce prefix by executing the query
                                    select count(*) from t_xml x
                                    where existsNode( x.MESSAGE_CONTENT,
                                    '/*:MessageEnvelope/*:Body/*:Announcement/*:Addresses/*:DealerDestAddr[*:SiteCode="aaa"]',
                                    'xmlns="http://www.xxx.com/CLV2/MessageEnvelope/20120501",
                                    xmlns:*="http://www.xxx.com/CLV2/Messages/Announcement/20120501",
                                    xmlns:*="http://www.xxx.com/CLV2/MessageTypes/20120501" ' ) = 1

                                    But in the update query if i give

                                    update t_xml x set x.message_content=
                                    updateXML(x.message_content,
                                    '/MessageEnvelope/Body/Announcement/*:Addresses/*:DealerDestAddr/*:SiteCode/text()','test',
                                    'xmlns="http://www.xxx.com/CLV2/MessageEnvelope/20120501",
                                    xmlns:*="http://www.xxx.com/CLV2/Messages/Announcement/20120501",
                                    xmlns:*="http://www.xxx.com/CLV2/MessageTypes/20120501" '
                                    )
                                    where existsNode( x.MESSAGE_CONTENT,
                                    '/MessageEnvelope/Body/Announcement/*:Addresses/*:DealerDestAddr[*:SiteCode="aaa"]',
                                    'xmlns="http://www.xxx.com/CLV2/MessageEnvelope/20120501",
                                    xmlns:*="http://www.xxx.com/CLV2/Messages/Announcement/20120501",
                                    xmlns:*="http://www.xxx.com/CLV2/MessageTypes/20120501" ' ) = 1
                                    This is not updating.

                                    Can someone tell me how to update the node without knowing the namespace prefix?

                                    Edited by: sprightee on Jun 26, 2012 2:00 AM
                                    1 2 3 Previous Next