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

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

    Re: UPDATE xml attribute value

    ‏2012-08-30T13:49:03Z  
    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
    321 Posts

    Re: UPDATE xml attribute value

    ‏2012-08-30T16:26:02Z  
    • eduardok
    • ‏2012-08-30T13:49:03Z
    Here's the UPDATE I've tried based on the example from http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/ (update 15):
    <pre class="jive-pre"> 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'; </pre>

    But ideally I want a fixed/static state, so something like <pre class="jive-pre"> modify do insert attribute state "custom" into $new/system </pre>
    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

    Re: UPDATE xml attribute value

    ‏2012-08-30T18:16:26Z  
    Hi Eduardo,

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

    <pre class="jive-pre"> modify do insert attribute state { "custom" } into $new/system </pre>
    Does that work for you?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    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
    321 Posts

    Re: UPDATE xml attribute value

    ‏2012-08-30T20:11:43Z  
    • eduardok
    • ‏2012-08-30T18:16:26Z
    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!
    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/