Topic
  • 2 replies
  • Latest Post - ‏2013-07-23T17:42:23Z by ChrisBaldwin
ChrisBaldwin
ChrisBaldwin
2 Posts

Pinned topic Omit XML Declaration from xmlquery result

‏2013-07-12T18:14:33Z |

When I run an xmlquery, I am getting the XML declaration + my desired field returned.  How do I only return the data I am trying to query.  Here is the result I get when I run the query below:

 

<?xml version="1.0" encoding="IBM037"?>THIS IS MY COMMENT

 

Here is my

 EXEC SQL                                         
   SELECT         
   xmlquery                                       
   ('$c/*:Message/messageDetail/comments/text()'  
   PASSING A.PAYLOAD_DATA AS "c")                 
   INTO :WS-PAYLOAD-DAT1                          
   FROM TABLE_MSG A,                
   WHERE A.ACCT_ID = '123456abc123'
   FETCH FIRST ROW ONLY                           
   WITH UR                                        
 END-EXEC

 

 

  • MatthiasNicola
    MatthiasNicola
    322 Posts
    ACCEPTED ANSWER

    Re: Omit XML Declaration from xmlquery result

    ‏2013-07-12T22:59:05Z  

    Hi Chris,

    I think you can use the XMLSERIALIZE function to explicitly avoid the XML declaration, like this:

     EXEC SQL                                         
       SELECT   
      
    XMLSERIALIZE(  xmlquery('$c/*:Message/messageDetail/comments/text()'  
       PASSING A.PAYLOAD_DATA AS "c")   
      
     AS BLOB  EXCLUDING XMLDECLARATION)               
       INTO :WS-PAYLOAD-DAT1                          
       FROM TABLE_MSG A,                
       WHERE A.ACCT_ID = '123456abc123'
       FETCH FIRST ROW ONLY                           
       WITH UR                                        
     END-EXEC

     Give it a try.

    http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_bif_xmlserialize.htm
     

    Thanks,

    - Matthias
     

     

  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: Omit XML Declaration from xmlquery result

    ‏2013-07-12T22:59:05Z  

    Hi Chris,

    I think you can use the XMLSERIALIZE function to explicitly avoid the XML declaration, like this:

     EXEC SQL                                         
       SELECT   
      
    XMLSERIALIZE(  xmlquery('$c/*:Message/messageDetail/comments/text()'  
       PASSING A.PAYLOAD_DATA AS "c")   
      
     AS BLOB  EXCLUDING XMLDECLARATION)               
       INTO :WS-PAYLOAD-DAT1                          
       FROM TABLE_MSG A,                
       WHERE A.ACCT_ID = '123456abc123'
       FETCH FIRST ROW ONLY                           
       WITH UR                                        
     END-EXEC

     Give it a try.

    http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_bif_xmlserialize.htm
     

    Thanks,

    - Matthias
     

     

  • ChrisBaldwin
    ChrisBaldwin
    2 Posts

    Re: Omit XML Declaration from xmlquery result

    ‏2013-07-23T17:42:23Z  

    Hi Chris,

    I think you can use the XMLSERIALIZE function to explicitly avoid the XML declaration, like this:

     EXEC SQL                                         
       SELECT   
      
    XMLSERIALIZE(  xmlquery('$c/*:Message/messageDetail/comments/text()'  
       PASSING A.PAYLOAD_DATA AS "c")   
      
     AS BLOB  EXCLUDING XMLDECLARATION)               
       INTO :WS-PAYLOAD-DAT1                          
       FROM TABLE_MSG A,                
       WHERE A.ACCT_ID = '123456abc123'
       FETCH FIRST ROW ONLY                           
       WITH UR                                        
     END-EXEC

     Give it a try.

    http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_bif_xmlserialize.htm
     

    Thanks,

    - Matthias
     

     

    Worked great.  Thank you!