Topic
4 replies Latest Post - ‏2012-08-30T20:11:43Z by MatthiasNicola
eduardok
eduardok
7 Posts
ACCEPTED ANSWER

Pinned topic UPDATE xml attribute value

‏2012-08-30T13:27:32Z |
Given:

<system><blabla>meh</blabla></system>

I want:

<system state=
"custom"><blabla>meh</blabla></system>


Tried:

SELECT XMLQUERY(
'copy $new := $XML_CONTENT modify ( 

do insert attribute 
"custom" into $new/system/@state ) 

return $new
') FROM xmls;


Also tried an


"UPDATE xmls SET XML_CONTENT ...".


How about if I had an XML already with the state attribute? Could I have a "do insert or replace" ?
Updated on 2012-08-30T20:11:43Z at 2012-08-30T20:11:43Z by MatthiasNicola
  • eduardok
    eduardok
    7 Posts
    ACCEPTED ANSWER

    Re: UPDATE xml attribute value

    ‏2012-08-30T13:49:03Z  in response to eduardok
    Here's the UPDATE I've tried based on the example from http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/ (update 15):
    
    UPDATE xmls SET XML_CONTENT = XMLQUERY(
    'copy $new := $XML_CONTENT modify  
    
    do insert attribute state 
    { $ID 
    }  into $new/system 
    
    return $new
    ') WHERE id IS NOT NULL and XML_TYPE=
    'system';
    


    But ideally I want a fixed/static state, so something like
    
    modify  
    
    do insert attribute state 
    "custom"  into $new/system
    
    • MatthiasNicola
      MatthiasNicola
      320 Posts
      ACCEPTED ANSWER

      Re: UPDATE xml attribute value

      ‏2012-08-30T16:26:02Z  in response to eduardok
      Hi Eduardo,

      if you want to insert an attribute with a fixed value, such as "custom", try this:

      
      modify 
      
      do insert attribute state 
      {
      "custom"
      } into $new/system
      

      Does that work for you?

      Matthias

      Matthias Nicola
      http://www.tinyurl.com/pureXML
      http://nativexmldatabase.com/
      • eduardok
        eduardok
        7 Posts
        ACCEPTED ANSWER

        Re: UPDATE xml attribute value

        ‏2012-08-30T18:16:26Z  in response to MatthiasNicola
        oh, man, I had tried that and it didn't work. But just now I've realized that it didn't because some XMLs already had the field.
        Alright... sounds like a WHERE would get rid of the existing ones. Thanks again!
        • MatthiasNicola
          MatthiasNicola
          320 Posts
          ACCEPTED ANSWER

          Re: UPDATE xml attribute value

          ‏2012-08-30T20:11:43Z  in response to eduardok
          Ah yes, within a given element there cannot be two attributes with the same name. So if the "state" attribute already existed then you cannot add another one with that name. Tricky! But yes, you can add a condition to check and skip those documents that already have a "state" attribute. Very good.


          Matthias Nicola
          http://www.tinyurl.com/pureXML
          http://nativexmldatabase.com/