7 Replies Latest reply on May 24, 2019 5:00 PM by Jorgelina1

    How to insert or update an attribute into an XML with xmlquery

    Jorgelina1

      Hi,

       

      I have a long XML, which is of the sort:

       

      <something>
         <root>
           <tag id="1"> ... </tag>
           <tag id="2"> ... </tag>
           <tag id="3" myattr="3"> ... </tag>
           <tag id="4"> ... </tag>
           <tag id="5" myattr="4"> ... </tag>
        </root>
      </something>
      

      So, from that I need to save the XML and also generate something in DB.

      The IDs in TAG will not be the sames as the IDs created in DB, but I will need the IDs on DB to be added into the XML to have a reference on whether something changes, or is added or deleted.

       

      So, I would need to:

       

      1- Make sure that in the XML any "tag" that does not have the myattr, to have it added.

      2- Change the myattr to the corresponding DB value, when Tag@id = "nn".

       

      I have been searching and I found something that would let me to update the value dynamically:

       

      with xd as (
                  select xml_data_field
                  from   xml_data_table
                  where  xml_id = 3117
                  )
      select 
              xmlquery(  
                  'copy $tmp := .    
                  modify (for $i in $tmp//*[local-name()=$input_tag]/@*[local-name()=$input_attribute] 
                  return replace value of node $i with $input_value)    
                  return $tmp'    
                  PASSING xmltype(xd.xml_data_field), 'something/root/tag' AS "input_tag", 'myattr' AS "input_attribute", '1' AS "input_value"    
                  returning content 
              ).GetClobVal()
      from   xd
      ;
      

       

      what I would be missing here is the table with the DB IDs and then the "relationship" to supply the '1'  (for the input_value) as the id from db, but also I would be missing, which I am not sure if it needs to be done in 2 steps, or could be done in one...  how to update those nodes (/tag) in which the attribute (myattr) already exists, and insert in those nodes (/tag) in which the attribute (myattr) does not exist.

      Even if I had to do it in 2 steps:

      1- Add the attribute with a "" value in those nodes (/tag) in which the attribute (myattr) does not exist

      2- Replace the value for specific node "id =  nn"

       

      I hope I am clear on what I need, I have been searching but I could not find anything that worked for the first step (except insertchildxml, but it is being deprecated in Oracle 12 and I do not want to use that, I would rather use xmlquery)... For the 2nd  step I have a vague idea on how to search for a node with specific id, but at this point would be helpful if someone gave me an example.

       

      Thanks and regards,

       

      Jorgelina

        • 1. Re: How to insert or update an attribute into an XML with xmlquery
          KKallaur

          http://www.liberidu.com/blog/2014/10/28/getting-started-processing-xml-content-with-the-xquery-update-facility-1-0/

          https://odieweblog.wordpress.com/2018/02/17/how-to-generate-empty-xml-attributes/

           

          Example below. This doesn't answer all your questions - I didn't include the answer for adding only where "myattr" doesn't exist, but it will get you started.

          Create table xml_docs (xml_doc_id number, xml_doc xmltype, attribute varchar2(100));
          
          insert into xml_doc(xml_doc_id, xml_doc, attribute)
          values(1, xmltype('<something>  
             <root>  
               <tag id="1"> node1 </tag>  
               <tag id="2"> node2 </tag>  
               <tag id="3" myattr="3"> node3 </tag>  
               <tag id="4"> node4 </tag>  
               <tag id="5" myattr="4"> node5 </tag>  
            </root>  
          </something> '), 'changeme');
          
          
          SELECT XMLQuery('copy $i := $x1
           modify insert node attribute myattr {$a1}
                           into $i/something/root/tag[@id="1"] return $i/something/root/tag[@id="1"]'
           PASSING xml_doc as "x1"
                             , attr as "a1"
           RETURNING CONTENT)
            FROM xml_docs;
          
          1 person found this helpful
          • 2. Re: How to insert or update an attribute into an XML with xmlquery
            Jorgelina1

            Thanks for your reply.

            What I understand from what I have already researched and your example... is that seems only you can update/add one attribute at a time, and not for all of them in one query.. is this the case?

             

            Edit:

            What I have so far, is

            1- A query to get the attribute value, as per my processing - the one I need to do - this will return a null if for the tag/@id=NN if the attribute is not there

            2- A query to add the attribute for a tag/@id=NN

            3- A query to update the attribute for tag/@id=NN

             

            But all I have is a node at a time.

             

            Being fair, I get the XML from "another source", I convert it into a XMLTABLE... With the IDs I need... so while looping through them, I could still use each of those query to see if the attribute is set in the XML, and update/add if needed, or leave it be. While I am processing and creating (if needed) my records in another table...

            So, I think I am set... but I thought that maybe a more massive way, that would let me use a table in a dynamic way to determine whether a certain attribute/value needs to be added/updated would be better.

             

            However, I am failing to prove/test/find a way to add the same attribute (with the same value) to all the nodes in the document. Nor how to update it. If I do not include [@id=NN] it does not work... and I keep getting an error about "error with insertion" (on the insert node attribute statement), I have added a for $i, and still... so it seems impossible to add it to all the nodes... which is a bit frustrating since I've been all day trying stuff out in order to achieve something in that regards.

             

            So, if you tell me that actually doing all/several nodes at a time is something not achievable, I will stop this research and work with what I have.

             

            Thanks again.

            • 3. Re: How to insert or update an attribute into an XML with xmlquery
              cormaco

              Here is a way to create the missing attributes with values taken from a table, the idea is to pass the values with their respective id as an XML fragment:

              with 
              xml_doc(xmldata) as (
              select xmltype(
              '<something>    
                 <root>    
                   <tag id="1"> node1 </tag>    
                   <tag id="2"> node2 </tag>    
                   <tag id="3" myattr="3"> node3 </tag>    
                   <tag id="4"> node4 </tag>    
                   <tag id="5" myattr="4"> node5 </tag>    
                </root>    
              </something>') from dual),
              id_tab(id,val) as (
                  select 1,'idval1' from dual union all
                  select 2,'idval2' from dual union all
                  select 3,'idval3' from dual union all
                  select 4,'idval4' from dual union all
                  select 5,'idval5' from dual
              )
              select xmlquery(
                  'copy $tmp := .
                  modify (
                      for $i in $tmp/something/root/tag[not(@myattr)]
                          return insert node attribute myattr {$idval/idval[@ID=$i/@id]/@VAL} into $i
                      )
                  return $tmp
                  '
                  passing xmldata,(select xmlagg(xmlelement("idval",xmlattributes(id,val))) from id_tab) as "idval"
                  returning content
              )
              from xml_doc
              

               

              Result:

              <something>
                  <root>
                      <tag id="1" myattr="idval1"> node1 </tag>
                      <tag id="2" myattr="idval2"> node2 </tag>
                      <tag id="3" myattr="3"> node3 </tag>
                      <tag id="4" myattr="idval4"> node4 </tag>
                      <tag id="5" myattr="4"> node5 </tag>
                  </root>
              </something>
              
              1 person found this helpful
              • 4. Re: How to insert or update an attribute into an XML with xmlquery
                Jorgelina1

                Thanks a lot.

                This helped me to resolve what I needed.

                Was able to add a where (on the 1st for) clause, because I needed to filter out specific tag ids, and to add another for in order to modify the ones that actually had the attribute, to change their value to the one I needed.

                Regards,

                 

                Jorgelina

                • 5. Re: How to insert or update an attribute into an XML with xmlquery
                  Jorgelina1

                  Hi Cormaco,

                   

                  I thought I had it all cooked up.

                  I added, as I mentioned before, a for to use to update values for when the attribute was existing.

                  My tests ran just fine.

                  Problem is when I used it with the real data, since it somehow collapsed (instead of an ora error, all I get is a reset connection, which makes me think that is probably going through an endless loop of some sort), the sentence I added is the problem...

                  So I need to ask, how would you change your current insertion for, to include another "for" to update the values, from the pair id/val when the @myattr does exist in the node?

                   

                  The one I added... looks like this:

                                      for $i in $tmp/something/root/tag[@myattr]

                                          return replace value of node $i/@myattr with ($idval/idval[@ID=$i/@id]/@VAL)

                   

                  I think would be valid to add that the xml might not have ANY @myattr the first time it is used.

                   

                  I guess it ran on my tests because they were pretty trivial, but with a big xml is not.

                  Would you help me, please?

                  Thanks and regards.

                  • 6. Re: How to insert or update an attribute into an XML with xmlquery
                    cormaco

                    If the code you wrote worked for your test cases, but not for your real XML, then maybe you have hit a capacity limit or a bug.

                    You could try posting this new problem in a more generic SQL or database forum with more visitors who can help you with specific troubleshooting.

                    • 7. Re: How to insert or update an attribute into an XML with xmlquery
                      Jorgelina1

                      The code I wrote, worked for the testing cases.

                       

                      In usage there is also a nested table, defined at server level;  so, instead of having a select to a kind-of a table, I have a  "select id, val from Table(l_node))"  being the l_node a table of a record type (again, not locally defined but defined at server level).

                      So I believe, that both things, the Table(l_node) and the for to update the node, have something "between them" that "hangs" the xmlquery. Because when only using the "for" for insertion, works just fine.

                       

                      So, my "solution" (kinda kicking the ball to another corner), was to redo the statements.

                       

                      Original was: Grab the XML, transform into the l_node, process it, update into the l_node the @myattr and finally running the xmlquery for insert and update of attribute @myattr.

                       

                      Now is:  Run the xmlquery to insert the attribute @myattr where it does not exist. With that XML transform into the l_node, process the l_node and while processing when @myattr needs updating, run a xmlquery just to update that specific $i/@myattr .

                       

                      Unfortunately, original way was my preferred way to go, but since it does not work, for no reason nor ora error.... I had to change things.

                       

                      Anyways thanks a lot! Maybe you are right and I hit a bug there....

                      Regards.

                       

                       

                      Edit:

                       

                      If it helps to anyone out there, either way to understand what happened, or to understand what the error is, posting my real code:  (p_hier_xml and v_hier_xml are the clobs)

                       

                              with
                                xml_doc(xmldata) as ( select xmltype(p_hier_xml) from dual ),
                                id_tab(id,val) as ( 
                                                   select id, db_id val
                                                   from   TABLE(l_node)
                                                  )  
                              select xmlquery(  
                                  'copy $tmp := . 
                                  modify (
                                          for $i in $tmp/mxGraphModel/root/mxCell[not(@db_id)]
                                              where not ($i[@id="0"] or $i[@id="1"] or $i[@edge="1"])
                                              return insert node attribute db_id {$idval/idval[@ID=$i/@id]/@VAL} into $i
                                          ,
                                          for $i in $tmp/mxGraphModel/root/mxCell[@db_id]
                                              return replace value of node $i/@db_id with ($idval/idval[@ID=$i/@id]/@VAL) 
                                         ) 
                                  return $tmp 
                                  '  
                                  passing xmldata,(select xmlagg(xmlelement("idval",xmlattributes(id,val))) from id_tab) as "idval"  
                                  returning content  
                              ).getclobval() 
                              into v_hier_xml
                              from xml_doc  
                      

                       

                      Regards.