Topic
  • 3 replies
  • Latest Post - ‏2012-02-28T17:57:24Z by MatthiasNicola
VV4H_mario_fernandes
43 Posts

Pinned topic Use XMLVALIDATE when updating table

‏2012-02-28T16:47:59Z |
Please tell me how to use xmlvalidate while doing the following update.
I want to validate the xml after the value has been updated.
update kasper.xml_client
set xml_client = xmlquery( 'transform
copy $new := $XML_CLIENT
modify do replace value of $new/kasperSchema/TDSClient/venomJmsMessage/sentTime
with 905-477-9011
return $new')
where
XMLEXISTS('$d/kasperSchema/TDSClient/kasperClient/client' passing xml_client as "d")
;
Updated on 2012-02-28T17:57:24Z at 2012-02-28T17:57:24Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: Use XMLVALIDATE when updating table

    ‏2012-02-28T16:55:25Z  
    You can wrap the XMLVALIDATE function around the XMLQUERY function:

    
    UPDATE kasper.xml_client set xml_client = xmlvalidate( xmlquery( 
    'transform copy $new := $XML_CLIENT modify 
    
    do replace value of $new/kasperSchema/TDSClient/venomJmsMessage/sentTime with 905-477-9011 
    
    return $new
    ')  ACCORDING TO XMLSCHEMA ID kasper.myschema) WHERE XMLEXISTS(
    '$d/kasperSchema/TDSClient/kasperClient/client' passing xml_client as 
    "d") ;
    



    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: Use XMLVALIDATE when updating table

    ‏2012-02-28T17:18:35Z  
    You can wrap the XMLVALIDATE function around the XMLQUERY function:

    <pre class="jive-pre"> UPDATE kasper.xml_client set xml_client = xmlvalidate( xmlquery( 'transform copy $new := $XML_CLIENT modify do replace value of $new/kasperSchema/TDSClient/venomJmsMessage/sentTime with 905-477-9011 return $new ') ACCORDING TO XMLSCHEMA ID kasper.myschema) WHERE XMLEXISTS( '$d/kasperSchema/TDSClient/kasperClient/client' passing xml_client as "d") ; </pre>


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    how can I get the updated xml so that I can process it after the update.
    I dont want to issue a separate select statement.
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: Use XMLVALIDATE when updating table

    ‏2012-02-28T17:57:24Z  
    how can I get the updated xml so that I can process it after the update.
    I dont want to issue a separate select statement.
    You can retrieve the newly inserted or updated rows in the same stmt, like this:

    SELECT ... FROM NEW TABLE (INSERT INTO mytable ...);

    SELECT ... FROM NEW TABLE (UPDATE mytable SET ... WHERE...);

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.routines.doc/doc/c0011133.html


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