2 Replies Latest reply: Oct 29, 2013 8:45 AM by elcaro RSS

    Storing and Extracting XML messages

    elcaro

      Hi All,

       

      I have a table with a VARCHAR2(4000) column which contains XML messages like listed below.  I am reading these messages using the below query and then inserting into different tables.  For the 2nd XML message, the select query is becoming really large.  I am worried about larger XML messages where I will have to type all these path and namespaces etc.  Is there an efficient way of storing these and then extracting them with correct data types?  I might get a chance to change the way these XML messages are generated and then stored in the table.  So I am not restricted to the below XML messages.  I am using Oracle 11gR2 database (11.2.0.3).

       

      XML message #1

      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <SOAP-ENV:Body>
          <m:validate xmlns:m="http://abcllc.com">
            <m:Company>01</m:Company>
            <m:PurchaseOrder>673</m:PurchaseOrder>
            <m:POAmount>115875</m:POAmount>
          </m:validate>
        </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>
      

       

      XML message #2

      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <SOAP-ENV:Body>
          <m:insert xmlns:m="http://abcllc.com">
            <m:JournalEntry>
              <m:Status>ACTIVE</m:Status>
              <m:AccountingDate>2009-08-20</m:AccountingDate>
              <m:CurrencyCode>USD</m:CurrencyCode>
              <m:CreatedDate>2009-08-21</m:CreatedDate>
              <m:CreatedBy>JDOE</m:CreatedBy>
              <m:ActualFlag>A</m:ActualFlag>
              <m:JournalType>AP</m:JournalType>
              <m:Company>01</m:Company>
              <m:ProjNbr>064522</m:ProjNbr>
              <m:Task>0061</m:Task>
              <m:CostCenter>6361</m:CostCenter>
              <m:EnteredDR>125.95</m:EnteredDR>
              <m:EnteredCR>0</m:EnteredCR>
              <m:AccountedDR>125.95</m:AccountedDR>
              <m:AccountedCR>0</m:AccountedCR>
              <m:BatchNumber></m:BatchNumber>
              <m:ID>05031</m:ID>
              <m:TransDescription>BOLDT COMPANY</m:TransDescription>
              <m:FiscalYear></m:FiscalYear>
              <m:PeriodEntered>200908</m:PeriodEntered>
              <m:PeriodPosted>200908</m:PeriodPosted>
              <m:Quantity>0</m:Quantity>
              <m:RefNumber></m:RefNumber>
              <m:ExtRefNumber>39000-J68</m:ExtRefNumber>
              <m:POExtPrice>0</m:POExtPrice>
              <m:POLineRef></m:POLineRef>
              <m:PONumber>11158</m:PONumber>
              <m:POQuantity>0</m:POQuantity>
              <m:POUnitPrice>0</m:POUnitPrice>
            </m:JournalEntry>
          </m:insert>
        </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>
      

       

      Query for XML message #1

      WITH t AS (
        SELECT xmltype('
      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <SOAP-ENV:Body>
          <m:validate xmlns:m="http://abcllc.com">
            <m:Company>01</m:Company>
            <m:PurchaseOrder>673</m:PurchaseOrder>
            <m:POAmount>115875</m:POAmount>
          </m:validate>
        </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>
      ') xcol
        FROM dual
      )
      SELECT extractValue(
               xcol
             , '/SOAP-ENV:Envelope/SOAP-ENV:Body/m:validate/m:Company'
             , 'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                xmlns:m="http://abcllc.com"'
             ) company,
      
      
             extractValue(
               xcol
             , '/SOAP-ENV:Envelope/SOAP-ENV:Body/m:validate/m:PurchaseOrder'
             , 'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                xmlns:m="http://abcllc.com"'
             ) PO,
      
      
             extractValue(
               xcol
             , '/SOAP-ENV:Envelope/SOAP-ENV:Body/m:validate/m:POAmount'
             , 'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                xmlns:m="http://abcllc.com"'
             ) POAmt
      FROM t;
      

       

      Thanks in advance.

        • 1. Re: Storing and Extracting XML messages
          odie_63

          Hi,

           

          Two things :

           

          1) Do not store XML in VARCHAR2, or CLOB. Use XMLType, you'll benefit from parsing and storage optimizations.

          2) EXTRACTVALUE is deprecated and anyway not appropriate for this requirement. Use XMLTable to parse the document into relational rows and columns.

           

          e. g.

          SQL> create table tmp_xml of xmltype;

           

          Table created

           

          SQL>

          SQL> insert into tmp_xml values (

            2  xmltype('<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

            3        <SOAP-ENV:Body>

            4          <m:insert xmlns:m="http://abcllc.com">

            5            <m:JournalEntry>

            6              <m:Status>ACTIVE</m:Status>

            7              <m:AccountingDate>2009-08-20</m:AccountingDate>

            8              <m:CurrencyCode>USD</m:CurrencyCode>

            9              <m:CreatedDate>2009-08-21</m:CreatedDate>

          10              <m:CreatedBy>JDOE</m:CreatedBy>

          11              <m:ActualFlag>A</m:ActualFlag>

          12              <m:JournalType>AP</m:JournalType>

          13              <m:Company>01</m:Company>

          14              <m:ProjNbr>064522</m:ProjNbr>

          15              <m:Task>0061</m:Task>

          16              <m:CostCenter>6361</m:CostCenter>

          17              <m:EnteredDR>125.95</m:EnteredDR>

          18              <m:EnteredCR>0</m:EnteredCR>

          19              <m:AccountedDR>125.95</m:AccountedDR>

          20              <m:AccountedCR>0</m:AccountedCR>

          21              <m:BatchNumber></m:BatchNumber>

          22              <m:ID>05031</m:ID>

          23              <m:TransDescription>BOLDT COMPANY</m:TransDescription>

          24              <m:FiscalYear></m:FiscalYear>

          25              <m:PeriodEntered>200908</m:PeriodEntered>

          26              <m:PeriodPosted>200908</m:PeriodPosted>

          27              <m:Quantity>0</m:Quantity>

          28              <m:RefNumber></m:RefNumber>

          29              <m:ExtRefNumber>39000-J68</m:ExtRefNumber>

          30              <m:POExtPrice>0</m:POExtPrice>

          31              <m:POLineRef></m:POLineRef>

          32              <m:PONumber>11158</m:PONumber>

          33              <m:POQuantity>0</m:POQuantity>

          34              <m:POUnitPrice>0</m:POUnitPrice>

          35            </m:JournalEntry>

          36          </m:insert>

          37        </SOAP-ENV:Body>

          38      </SOAP-ENV:Envelope>')

          39  );

           

          1 row inserted

           

          SQL>

          SQL> select x.*

            2  from tmp_xml t

            3     , xmltable(

            4         xmlnamespaces(

            5           default 'http://abcllc.com'

            6         , 'http://schemas.xmlsoap.org/soap/envelope/' as "soap"

            7         )

            8       , '/soap:Envelope/soap:Body/insert/JournalEntry'

            9         passing t.object_value

          10         columns Status         varchar2(10) path 'Status'

          11               , AccountingDate date         path 'AccountingDate'

          12               , CurrencyCode   varchar2(3)  path 'CurrencyCode'

          13               , CreatedDate    date         path 'CreatedDate'

          14               , CreatedBy      varchar2(30) path 'CreatedBy'

          15       ) x

          16  ;

           

          STATUS     ACCOUNTINGDATE CURRENCYCODE CREATEDDATE CREATEDBY

          ---------- -------------- ------------ ----------- ------------------------------

          ACTIVE     20/08/2009     USD          21/08/2009  JDOE

          • 2. Re: Storing and Extracting XML messages
            elcaro

            odie_63 - perfect !!!  Thanks a lot for your answer.  This is what I was looking for.  It works like a charm.  Thanks again.