8 Replies Latest reply: Mar 2, 2012 8:01 AM by 698705 RSS

    Parse XML  from a Table with different xml info

    698705
      Hi,

      I have issue's parsing XML stored in a table column (XMLTYPE)
      The table structure is the following:
      CREATE TABLE SOURCE (
              MESSAGE_ROW_ID NUMBER, 
           CREATED_DATE TIMESTAMP (6), 
           CALLER_ID VARCHAR2(255 BYTE), 
           SERVICE_ID VARCHAR2(255 BYTE), 
           COUNTRY_CODE VARCHAR2(2 BYTE), 
           INTERNAL_CORRELATION_ID VARCHAR2(255 BYTE), 
           INTERNAL_TRACE_ID VARCHAR2(255 BYTE), 
           RECEIVED_DATE TIMESTAMP (3) WITH TIME ZONE, 
           STATUS VARCHAR2(20 BYTE), 
           QUEUE_NAME VARCHAR2(255 BYTE), 
           EXTERNAL_CORRELATION_ID VARCHAR2(255 BYTE), 
           BUS_PROC_CORRELATION_ID VARCHAR2(255 BYTE), 
           FORWARDED_DATE TIMESTAMP (3) WITH TIME ZONE, 
           MSISDN VARCHAR2(50 BYTE), 
           ENGINE_ID VARCHAR2(50 BYTE), 
           TRACKING_CORRELATION_ID VARCHAR2(2000 BYTE), 
           DP_DEVICE VARCHAR2(50 BYTE), 
           DP_LOGPOINT VARCHAR2(20 BYTE), 
           DP_TARGET_URL VARCHAR2(255 BYTE), 
           DP_IP_SOURCE VARCHAR2(50 BYTE), 
           DP_PORT VARCHAR2(5 BYTE), 
           DP_URI VARCHAR2(255 BYTE), 
           DP_SIZE NUMBER, 
           READY_TO_ARCHIVE CHAR(1 BYTE), 
           MESSAGE_CONTEXT XMLTYPE, 
           MESSAGE_DATA XMLTYPE
         ) 
      The XML stored in 2 fileds (message_data -> XML Header) and (message_context -> SOAP envelope) and are in different format and containing different nodes/child nodes based on the queue_name. (each queue_name has a different XML)
      Example of xml data in one of the queue_names:

      queue_name:
      cal.eu.wifi-setup.gatewaynorth.response     
      message_context:
      "<?xml version="1.0" encoding="UTF-8"?>
      <msgContext>
          <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
              <jms1:JMSDestination>vf.gig.eu.reporting.input</jms1:JMSDestination>
              <jms1:JMSDeliveryMode>NON_PERSISTENT</jms1:JMSDeliveryMode>
              <jms1:JMSMessageID>ID:E4EMS-SERVER.60F64ED8C5671:242E79B</jms1:JMSMessageID>
              <jms1:JMSTimestamp>1324549679876</jms1:JMSTimestamp>
              <jms1:JMSExpiration>0</jms1:JMSExpiration>
              <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
              <jms1:JMSPriority>4</jms1:JMSPriority>
              <jms1:JMSCorrelationID>c6c2c82a-5dae-42b6-bbe7-d332d6d1dd72</jms1:JMSCorrelationID>
          </JMSHeaders>
          <OtherProperties>
              <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">LIGUK</auditPartnerId>
              <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">getSessionIdRequest</auditServiceId>
              <auditCountryCode xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ES</auditCountryCode>
              <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">1045024-c8e8ec80-0fe5-469b-8250-78825ffa3e03</correlationTransactionId>
              <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2011-12-22 10:27:59.867 +0000</auditReceivedDate>
              <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
              <auditQueueName xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">cal.eu.wifi-setup.gatewaynorth.response</auditQueueName>
              <correlationExternalId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
              <correlationBusinessProcessId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms"/>
              <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2011-12-22 10:27:59.876 +0000</auditForwardedDate>
              <auditMsisdn xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">123456789</auditMsisdn>
              <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ATS_instance1</auditEngineId>
              <correlationTracking xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">wifi-setup-client.cal.ATS_instance1.atsEnabler1.ATS_instance1</correlationTracking>
              <replyToEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ATS_instance1</replyToEngineId>
              <port xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">-1</port>
              <headers xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Content-Type: text/xml;Charset=UTF-8
      
      Content-Length: 991
      
      Connection: close
      </headers>
          </OtherProperties>
      </msgContext>"     
      Message_data:
      "<?xml version="1.0" encoding="UTF-8"?>
      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
          <SOAP-ENV:Body>
              <SOAP-ENV:Fault>
                  <faultcode>ATS-2001407</faultcode>
                  <faultstring>Error returned from ATS Enabler</faultstring>
                  <faultactor>ATS</faultactor>
                  <detail>
                      <ns0:serviceException xmlns:ns0="http://www.csapi.org/AuthenticationTokenService/interface/v0_8">
                          <ns1:messageId xmlns:ns1="http://www.csapi.org/schema/parlayx/common/v3_1">SVC0002</ns1:messageId>
                          <ns1:text xmlns:ns1="http://www.csapi.org/schema/parlayx/common/v3_1">Invalid input value for message part Service-Name</ns1:text>
                          <ns1:variables xmlns:ns1="http://www.csapi.org/schema/parlayx/common/v3_1">Service-Name</ns1:variables>
                      </ns0:serviceException>
                  </detail>
              </SOAP-ENV:Fault>
          </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>"
      How can i extract info from these XML's columns into relational data by getting the nodes & child nodes dynamically?


      Thanks

      Edited by: alekons on Feb 29, 2012 8:08 AM
        • 1. Re: Parse XML  from a Table with different xml info
          Jason_(A_Non)
          So you are wanting to extract an unknown number of columns from message_context and message_data, where that number varies depending upon the contents of queue_name since that defines the XML in message_content and message_data?

          And how do you plan to identify these unknown values that you extract and what do you plan to do with them? I think we are trying to see the bigger picture here so we can help provide the best solution.
          • 2. Re: Parse XML  from a Table with different xml info
            698705
            You are right.

            What i want to do is to select all fields from the SOURCE table parsing also all the nodes from the xml columns and store then in a another table - all in one row- based on queue_name.
            Every queue_name has different information stored in XMLTYPE column (message_context & message_data).


            For example lets say parsing the below part and store the into into Columns in another table
            .......
            <faultcode>ATS-2001407</faultcode>
                        <faultstring>Error returned from ATS Enabler</faultstring>
                        <faultactor>ATS</faultactor>
            .........
            and stoe them in the TARGET table
            faultcode                   faultstring                                            faultactor
            --------------------------------------------------------------------------------------------
            ATS-2001407           Error returned from ATS Enabler                  ATS
            With something like this
            insert into TARGET from select {All columns}+{XML parsing} from source 
            • 3. Re: Parse XML  from a Table with different xml info
              odie_63
              Here's the idea (basically) :
              select s.queue_name
                   , mc.*
                   , md.*
              from source s
                 , xmltable(
                     xmlnamespaces('http://www.tibco.com/namespaces/tnt/plugins/jms' as "jms1")
                   , '/msgContext'
                     passing s.message_context
                     columns JMSDestination  varchar2(80) path 'JMSHeaders/jms1:JMSDestination'
                           , JMSDeliveryMode varchar2(80) path 'JMSHeaders/jms1:JMSDeliveryMode'
                           /*, add more elements from JMSHeaders here*/
                           , auditPartnerId  varchar2(80) path 'OtherProperties/auditPartnerId'
                           , auditServiceId  varchar2(80) path 'OtherProperties/auditServiceId'
                           /*, add more elements from OtherProperties here*/
                   ) (+) mc
                 , xmltable(
                     xmlnamespaces(
                       'http://schemas.xmlsoap.org/soap/envelope/' as "s"
                     , 'http://www.csapi.org/AuthenticationTokenService/interface/v0_8' as "ns0"
                     , 'http://www.csapi.org/schema/parlayx/common/v3_1' as "ns1"
                     )
                   , '/s:Envelope/s:Body/s:Fault'
                     passing s.message_data
                     columns faultcode   varchar2(80) path 'faultcode'
                           , faultstring varchar2(80) path 'faultstring'
                           , faultactor  varchar2(80) path 'faultactor'
                           , messageId   varchar2(80) path 'detail/ns0:serviceException/ns1:messageId'
                           , text        varchar2(80) path 'detail/ns0:serviceException/ns1:text'
                           , variables   varchar2(80) path 'detail/ns0:serviceException/ns1:variables'
                   ) (+) md
              where queue_name = 'cal.eu.wifi-setup.gatewaynorth.response'
              ;
              I put outer joins in case one of the XMLType is NULL.
              You can remove them if you're sure both XML contents are never empty.

              Edited by: odie_63 on 1 mars 2012 12:24
              • 4. Re: Parse XML  from a Table with different xml info
                698705
                Thanks Odie works like a charm .

                Now how can parse multiple queue_names in one query ?
                • 5. Re: Parse XML  from a Table with different xml info
                  odie_63
                  Now how can parse multiple queue_names in one query ?
                  Remove the WHERE clause?

                  If you mean something else, please explain.
                  • 6. Re: Parse XML  from a Table with different xml info
                    698705
                    Hi Odie,

                    The XML format is different per queue_name.

                    another XML for queue gig.eu.cache.request is
                    <?xml version="1.0" encoding="UTF-8"?>
                    <msgContext>
                        <JMSHeaders xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
                            <jms1:JMSDestination>gig.eu.preprod.gatewaynorth.messagestore</jms1:JMSDestination>
                            <jms1:JMSDeliveryMode>NON_PERSISTENT</jms1:JMSDeliveryMode>
                            <jms1:JMSMessageID>ID:GIG-PREPROD-INBOUND.41F34E70B90F1:4E2BBAB</jms1:JMSMessageID>
                            <jms1:JMSTimestamp>1329217935774</jms1:JMSTimestamp>
                            <jms1:JMSExpiration>0</jms1:JMSExpiration>
                            <jms1:JMSRedelivered>false</jms1:JMSRedelivered>
                            <jms1:JMSPriority>4</jms1:JMSPriority>
                            <jms1:JMSCorrelationID>PRE_PROD_NORTHd0022064-1df3-43ef-be5b-93aedc96b3b2</jms1:JMSCorrelationID>
                        </JMSHeaders>
                        <OtherProperties>
                            <auditPartnerId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">010000000001189CC2BC2C</auditPartnerId>
                            <auditServiceId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">Auth</auditServiceId>
                            <correlationTransactionId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">d0022064-1df3-43ef-be5b-93aedc96b3b2</correlationTransactionId>
                            <auditReceivedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:15.756 +0100</auditReceivedDate>
                            <auditStatus xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">ok</auditStatus>
                            <auditQueueName xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">gig.eu.cache.request</auditQueueName>
                            <auditForwardedDate xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">2012-02-14 12:12:15.756 +0100</auditForwardedDate>
                            <auditEngineId xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorth</auditEngineId>
                            <correlationTracking xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dpNorth</correlationTracking>
                            <request xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">POST /carrier https/1.1</request>
                            <headers xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">
                    Accept-Encoding: gzip,deflate
                    Content-Type: text/xml; charset=UTF-8
                    SOAPAction: "http://www.carrier.com/Auth"
                    User-Agent: Jakarta Commons-HttpClient/3.1
                    Content-Length: 4062
                    Via: 1.1 AAAAACUBCgA-
                    http_SERIALNUMBER: 010000000001189CC2BC2C
                    auditPartnerId: 010000000001189CC2BC2C</headers>
                            <Host xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">dgig.com</Host>
                        </OtherProperties>
                    </msgContext>
                    <?xml version="1.0" encoding="UTF-8"?>
                    <soapenv:Envelope xmlns:ns0="http://www.android.google.com/billing" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
                       <soapenv:Header xmlns:wsa="http://www.w3.org/2005/08/addressing"><wsa:Action>http://www.carrier.com/Auth</wsa:Action></soapenv:Header>
                       <soapenv:Body wsu:Id="id-6" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
                          <ns0:Auth>
                             <parameters>
                                <Version>1</Version>
                                <RequestId>1cc96f5e-4021-4531-a9c4-56f3b0d510ed</RequestId>
                                <CorrelationId>740118.3267391648</CorrelationId>
                                <PurchaseTime>12</PurchaseTime>
                                <BillingAgreement>BA0001</BillingAgreement>
                                <ItemName>Item1</ItemName>
                                <MerchantName>Merchant1</MerchantName>
                                <MerchantContact>MContact1</MerchantContact>
                             </parameters>
                          </ns0:Auth>
                       </soapenv:Body>
                    </soapenv:Envelope>
                    In total i have 14 to 16 different XML's. Some of the nodes/child nodes are common but others are not.
                    How can i parse all of them ?

                    Edited by: alekons on Mar 1, 2012 8:27 AM
                    • 7. Re: Parse XML  from a Table with different xml info
                      odie_63
                      Ok, understood.

                      But you want to insert the data in the same target table regardless of the source queue? Which would mean you already know the name of expected elements?

                      It'll be very difficult (not to say impossible) with only one query.
                      Most likely, you'll need to build a mapping table describing which XML element from a given queue go to which column in the target table.
                      • 8. Re: Parse XML  from a Table with different xml info
                        698705
                        But you want to insert the data in the same target table regardless of the source queue?
                        Correct.
                        build a mapping table describing which XML element from a given queue go to which column in the target table.
                        Seems challenging. How can i do that ?