6 Replies Latest reply on May 2, 2012 9:14 AM by 931085

    Navigate trough different "levels" in the XML starting from known element?

    931085
      I am quite new to this so I must ask even if it's a stupid question...

      I'm trying to build a generic oracle procedure which can insert data into a table from an XML file undependent of which message it containes but of course, the messages has the same structure.

      I need to find the first element after Data, in this case "CUS_ORD_HEAD" but it could be any message like PO_HEAD.
      So I need to somehow find the element without knowing it's element-name. The same goes for CUS_ORD_LINE which could be PO_LINE. Is there any way to navigate trough the different "levels" in the XML starting from a known element.

      <CustomerOrder>
      <Metadata>
      <TransActionIdentity>1</TransActionIdentity>
      </Metadata>
      <Data>
      <CUS_ORD_HEAD Customer="ABC" CustomerOrderNumber="1234">
      <CUS_ORD_LINE CustomerOrderNumber="1234" OrderedQuantity="10" ProductNumber="1001403" CustomerOrderLinePosition="1"/>
      <HAPI_CUS_ORD_LINE CustomerOrderNumber="1234" OrderedQuantity="1" ProductNumber="2530" CustomerOrderLinePosition="2"/>
      <CUS_ORD_LINE_TEXT CustomerOrderLinePosition="2" Text="Test" CUSTOMERORDERNUMBER="1234"/>
      </HAPI_CUS_ORD_HEAD>
      </Data>
      </CustomerOrder>
        • 1. Re: Navigate trough different "levels" in the XML starting from known element?
          odie_63
          Hi,
          I'm trying to build a generic oracle procedure which can insert data into a table from an XML file undependent of which message it containes but of course, the messages has the same structure.
          Generally speaking, that's not a good approach. The database works much better when everything is known before runtime.

          It's doable though.
          Different approaches for different db versions. What's yours? (select * from v$version)

          Additional question : how do you know which element/attribute to insert into which column of the target table?
          • 2. Re: Navigate trough different "levels" in the XML starting from known element?
            931085
            I found this one which returns a list of all elements, so I can easely find all element after the Data-element. I now need a simular function which returns all attributes from a given element. When I have the Element and all Attributes I can build a query to read from the XL and insert it into the correct interface table using "execute immediate". The element will have the same name as the interface table's columns. So what I need right now is a way to get all attributes from a given element.

            Have already done this on one mesage by hardcoding all elements and attributes and it works very well but I don't want one procedure for each message...

            select t.object_value.getrootelement() as "NODES"
            from
            XMLTABLE('//*'
            passing xmltype('<?xml version="1.0" encoding="utf-8"?>
            <CustomerOrder>
            <Metadata>
            <TransActionIdentity>1</TransActionIdentity>
            </Metadata>
            <Data>
            <CUS_ORD_HEAD CustomerOrderNumber ="1234" Customer="ABC">
            <CUS_ORD_LINE CustomerOrderNumber="1234" CustomerOrderLinePosition="1" ProductNumber="1001403" OrderedQuantity ="10">
            </CUS_ORD_LINE>
            <CUS_ORD_LINE CustomerOrderNumber="1234" CustomerOrderLinePosition="2" ProductNumber="2530" OrderedQuantity ="1">
            <CUS_ORD_LINE_TEXT CUSTOMERORDERNUMBER="1234" CustomerOrderLinePosition="2" Text="Test" />
            </CUS_ORD_LINE>
            </CUS_ORD_HEAD>
            </Data>
            </CustomerOrder>')
            ) t;

            Edited by: 928082 on 2012-apr-19 02:18
            • 3. Re: Navigate trough different "levels" in the XML starting from known element?
              odie_63
              Still no database version? I'm wondering whether you want help or not :)

              Please take no offense but you said you're new to this, that's OK, no problem, but if you start with bad practices, you won't get far.
              but I don't want one procedure for each message...
              How many different structures are to be expected?

              From the XML structure you posted, what will the generated INSERT look like?
              • 4. Re: Navigate trough different "levels" in the XML starting from known element?
                931085
                Oh, sorry for that... Oracle 10g is what we are using, I hope you get the "picture" now since it's a little bit hard to explain sometimes.

                This is the qry I will build for each element, as you can see the tablename is the same as the XML element and the atributes are the same as the columns which solves many things when finding the correct interface table to save data in.

                *// The query I will build*
                insert into CUS_ORD_HEAD ( Customer, CustomerOrderNumber  )
                select *
                from xmltable('//Data'
                passing XMLType_W
                columns Customer                        varchar2(35)    path '@Customer',
                CustomerOrderNumber      varchar2(35)    path '@CustomerOrderNumber'  ) ;

                plsql_block := 'insert into ' || Table_string_W || '(' || Column_string  || ')' ||
                *' select *' ||*
                *' from xmltable(' || XML_Path ||*
                *' passing :MyXMLType' ||*
                *' columns ' || Column_path_string_W ||*
                *')';*

                execute immediate plsql_block using XMLType_W;

                Since this will be used to integrate against different customer systems there could be many different messages but they will all follow the same syntax. In this way we can use one webservice containing many different messages, all the customer needs is the xsd-file for each message. Please don't comment the structure, it's only a simple test to test the parsing, the real xml/xsd will follow necassery standards.
                • 5. Re: Navigate trough different "levels" in the XML starting from known element?
                  odie_63
                  the tablename is the same as the XML element and the atributes are the same as the columns
                  OK, understood.

                  You can retrieve both element name and attribute names at the same time using something like the following.
                  Attribute names are stored in a collection which is accessed iteratively in order to build the dynamic parts of the query :
                  SQL> CREATE OR REPLACE TYPE TColumnList IS TABLE OF VARCHAR2(30);
                    2  /
                   
                  Type created
                   
                  SQL> set serveroutput on
                  SQL> 
                  SQL> DECLARE
                    2  
                    3   xmldoc   xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>
                    4  <CustomerOrder>
                    5  <Metadata>
                    6  <TransActionIdentity>1</TransActionIdentity>
                    7  </Metadata>
                    8  <Data>
                    9  <CUS_ORD_HEAD CustomerOrderNumber ="1234" Customer="ABC">
                   10  <CUS_ORD_LINE CustomerOrderNumber="1234" CustomerOrderLinePosition="1" ProductNumber="1001403" OrderedQuantity ="10">
                   11  </CUS_ORD_LINE>
                   12  <CUS_ORD_LINE CustomerOrderNumber="1234" CustomerOrderLinePosition="2" ProductNumber="2530" OrderedQuantity ="1">
                   13  <CUS_ORD_LINE_TEXT CUSTOMERORDERNUMBER="1234" CustomerOrderLinePosition="2" Text="Test" />
                   14  </CUS_ORD_LINE>
                   15  </CUS_ORD_HEAD>
                   16  </Data>
                   17  </CustomerOrder>');
                   18  
                   19   --t_column_list TColumnList := TColumnList();
                   20   --v_table_name  VARCHAR2(30);
                   21  
                   22   tmp_cols      VARCHAR2(1000);
                   23   tmp_paths     VARCHAR2(1000);
                   24   tmp_qry       VARCHAR2(4000);
                   25  
                   26  BEGIN
                   27  
                   28    FOR r IN (
                   29      SELECT table_name
                   30           , set(cast(collect(column_name) as TColumnList)) as column_list
                   31      FROM XMLTable(
                   32           'for $i in /CustomerOrder/Data/descendant::*
                   33              , $j in $i/attribute::*
                   34            return element e
                   35            {
                   36              element TABLE_NAME {name($i)}
                   37            , element COLUMN_NAME {name($j)}
                   38            }'
                   39            passing xmldoc
                   40            columns
                   41              table_name   varchar2(30)
                   42            , column_name  varchar2(30)
                   43           )
                   44      GROUP BY table_name
                   45    )
                   46    LOOP
                   47  
                   48      tmp_cols := NULL;
                   49      tmp_paths := NULL;
                   50  
                   51      --dbms_output.put_line(r.table_name);
                   52  
                   53      FOR i in 1 .. r.column_list.count LOOP
                   54  
                   55        tmp_cols := tmp_cols || ', ' || r.column_list(i);
                   56        tmp_paths := tmp_paths || ', ' || r.column_list(i) || ' varchar2(35) path ''@' || r.column_list(i) || '''';
                   57  
                   58      END LOOP;
                   59  
                   60      tmp_cols := ltrim(tmp_cols, ', ');
                   61      tmp_paths := ltrim(tmp_paths, ', ');
                   62  
                   63      tmp_qry := 'INSERT INTO ' || r.table_name || ' (' || tmp_cols || ') ' ||
                   64                 'SELECT ' || tmp_cols ||
                   65                 ' FROM XMLTable(''/CustomerOrder/Data/descendant::' || r.table_name || '''' ||
                   66                 ' PASSING :1 ' ||
                   67                 'COLUMNS ' || tmp_paths ||
                   68                 ')';
                   69  
                   70      dbms_output.put_line(tmp_qry);
                   71  
                   72    END LOOP;
                   73  
                   74  END;
                   75  /
                   
                  INSERT INTO CUS_ORD_HEAD (CustomerOrderNumber, Customer) SELECT CustomerOrderNumber, Customer FROM XMLTable('/CustomerOrder/Data/descendant::CUS_ORD_HEAD' PASSING :1 COLUMNS CustomerOrderNumber varchar2(35) path '@CustomerOrderNumber', Customer varchar2(35) path '@Customer')
                  INSERT INTO CUS_ORD_LINE (CustomerOrderNumber, OrderedQuantity, ProductNumber, CustomerOrderLinePosition) SELECT CustomerOrderNumber, OrderedQuantity, ProductNumber, CustomerOrderLinePosition FROM XMLTable('/CustomerOrder/Data/descendant::CUS_ORD_LINE' PASSING :1 COLUMNS CustomerOrderNumber varchar2(35) path '@CustomerOrderNumber', OrderedQuantity varchar2(35) path '@OrderedQuantity', ProductNumber varchar2(35) path '@ProductNumber', CustomerOrderLinePosition varchar2(35) path '@CustomerOrderLinePosition')
                  INSERT INTO CUS_ORD_LINE_TEXT (CUSTOMERORDERNUMBER, Text, CustomerOrderLinePosition) SELECT CUSTOMERORDERNUMBER, Text, CustomerOrderLinePosition FROM XMLTable('/CustomerOrder/Data/descendant::CUS_ORD_LINE_TEXT' PASSING :1 COLUMNS CUSTOMERORDERNUMBER varchar2(35) path '@CUSTOMERORDERNUMBER', Text varchar2(35) path '@Text', CustomerOrderLinePosition varchar2(35) path '@CustomerOrderLinePosition')
                   
                  PL/SQL procedure successfully completed
                   
                  • 6. Re: Navigate trough different "levels" in the XML starting from known element?
                    931085
                    Thanks, did exactly what I wanted...