Topic
  • 4 replies
  • Latest Post - ‏2014-05-30T06:10:32Z by MatthiasNicola
kuttu_123
kuttu_123
3 Posts

Pinned topic DB2 pure xml

‏2014-05-24T15:03:51Z |

I am facing issue in the below query while using COBOL program

XMLDATA passed to the XMLPARSE function is a XML data in a string format read from a file

01 XML-DATA                   PIC X(44500000) VALUE SPACES.

Error while executing the program is SQLCODE -20345 . The XML document is well formed as I validated with XML validator .

Please help me if there is any other point to be taken care in the programming , query or input .

 

 

EXEC SQL
           SELECT xmldocument(x.body)
                  into :XML-DOC
             FROM xmltable(
                  xmlnamespaces
           ('urn:metlife.com/us/ins/product/groupvoluntaryworkbenefits/c
      -     'ustomer_AsyncServiceWSV1' AS "gwb",
           'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv"),
                '$i/soapenv:Envelope/soapenv:Body'
           passing
           xmlparse(document :XML-DATA strip whitespace)
           AS "i"
           columns
           "BODY" xml path 'gwb:publishCustomerEventRequest')
           AS X
           END-EXEC

Attachments

Updated on 2014-05-24T15:04:55Z at 2014-05-24T15:04:55Z by kuttu_123
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: DB2 pure xml

    ‏2014-05-26T09:08:02Z  

    Hello,

    I'm wondering if the problem is related to incorrect spelling of the namespace URI for "gwb".

    In the sample document, this namespace is defined as follows, with an uppercase "C" in "Customer_AsyncServiceWSV1":

    xmlns:gwb="urn:metlife.com/us/ins/product/groupvoluntaryworkbenefits/Customer_AsyncServiceWSV1"

    But, in your query the same URI uses a lowercase "c":

    'urn:metlife.com/us/ins/product/groupvoluntaryworkbenefits/customer_AsyncServiceWSV1'

    Since namespace URIs are case-sensitive, the XML column produced by your XMLTABLE function likely returns empty XML documents, which *might* be the reason why you get the error message about non well-formed documents.

    So, my first suggestion is to correct the namespace declaration in the query and then see if the error still occurs. Let me know what you find and then we'll take it form there.

    Thanks,

    Matthias

     

  • kuttu_123
    kuttu_123
    3 Posts

    Re: DB2 pure xml

    ‏2014-05-26T09:41:19Z  

    Hello,

    I'm wondering if the problem is related to incorrect spelling of the namespace URI for "gwb".

    In the sample document, this namespace is defined as follows, with an uppercase "C" in "Customer_AsyncServiceWSV1":

    xmlns:gwb="urn:metlife.com/us/ins/product/groupvoluntaryworkbenefits/Customer_AsyncServiceWSV1"

    But, in your query the same URI uses a lowercase "c":

    'urn:metlife.com/us/ins/product/groupvoluntaryworkbenefits/customer_AsyncServiceWSV1'

    Since namespace URIs are case-sensitive, the XML column produced by your XMLTABLE function likely returns empty XML documents, which *might* be the reason why you get the error message about non well-formed documents.

    So, my first suggestion is to correct the namespace declaration in the query and then see if the error still occurs. Let me know what you find and then we'll take it form there.

    Thanks,

    Matthias

     

    Hi

    I have tried changing the case to 'C' but even then it is returning the same SQLCODE of -20345

    I am reading the XML from a mainframe flat file into the variable XML-DATA .Please find the declaration for XML-DATA below

    01 XML-DATA                   PIC X(44500000) VALUE SPACES.  

    Then trying to parse the just the soap body alone .

    The same if I store in a DB2 table and pass the XML column to the XMLTABLE it works .

    Is it necessary that I need to store the xml in a DB2 table and then pass the XML from DB2 XML column itself to the XMLTABLE ?

    wondering why the parsing step is not able to except the data taken from the file since it is just a string . Do I need to take care of anything else while passing the string.

    I tried vaidating the XML using XML validator and it says its well formed XML .

    Thanks

    Kuttu

  • kuttu_123
    kuttu_123
    3 Posts

    Re: DB2 pure xml

    ‏2014-05-27T05:35:46Z  
    • kuttu_123
    • ‏2014-05-26T09:41:19Z

    Hi

    I have tried changing the case to 'C' but even then it is returning the same SQLCODE of -20345

    I am reading the XML from a mainframe flat file into the variable XML-DATA .Please find the declaration for XML-DATA below

    01 XML-DATA                   PIC X(44500000) VALUE SPACES.  

    Then trying to parse the just the soap body alone .

    The same if I store in a DB2 table and pass the XML column to the XMLTABLE it works .

    Is it necessary that I need to store the xml in a DB2 table and then pass the XML from DB2 XML column itself to the XMLTABLE ?

    wondering why the parsing step is not able to except the data taken from the file since it is just a string . Do I need to take care of anything else while passing the string.

    I tried vaidating the XML using XML validator and it says its well formed XML .

    Thanks

    Kuttu

    Hi

    Could you please look into the issue and help me on how to proceed further .

    Is it necessary to store XML in db2 TABLE column and then use DB2 pure xml data to extract the data ?

  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: DB2 pure xml

    ‏2014-05-30T06:10:32Z  
    • kuttu_123
    • ‏2014-05-26T09:41:19Z

    Hi

    I have tried changing the case to 'C' but even then it is returning the same SQLCODE of -20345

    I am reading the XML from a mainframe flat file into the variable XML-DATA .Please find the declaration for XML-DATA below

    01 XML-DATA                   PIC X(44500000) VALUE SPACES.  

    Then trying to parse the just the soap body alone .

    The same if I store in a DB2 table and pass the XML column to the XMLTABLE it works .

    Is it necessary that I need to store the xml in a DB2 table and then pass the XML from DB2 XML column itself to the XMLTABLE ?

    wondering why the parsing step is not able to except the data taken from the file since it is just a string . Do I need to take care of anything else while passing the string.

    I tried vaidating the XML using XML validator and it says its well formed XML .

    Thanks

    Kuttu

    Hi...

    one suggestion that might solve the problem is to change the declaration of XML-DATA  from


    01 XML-DATA                   PIC X(44500000) VALUE SPACES 

    to

    01  XML-DATA USAGE IS SQL TYPE IS CLOB(1M)

     

    If this does not solve the problem, please open a PMR and generate a slip dump for -20345.

     

    Also, here is a similar example that works as expected:

           01  XML-DATA USAGE IS SQL TYPE IS CLOB(1M).
           01  XML-DOC  USAGE IS SQL TYPE IS CLOB(1M).
    ...
             MOVE 'CALL XMLTABLE                 ' TO STNAME              
             EXEC SQL                                                     
             SELECT xmldocument(x.body)   into :XML-DOC                   
              FROM xmltable(                                              
               xmlnamespaces                                              
               ('urn:metlife.com' AS "gwb",                               
                'http://soap/envelope/' AS "soapenv"),                    
                '$i/soapenv:Envelope/soapenv:Body'                        
                 passing                                                  
                  xmlparse(document  :XML-DATA strip whitespace) AS "i"   
                 columns                                                  
                  "BODY" xml path                                         
                   'gwb:publishCustomerEventRequest')                     
                  AS X                                                    
             END-EXEC

     

    Thanks,

    - Matthias