1 2 Previous Next 27 Replies Latest reply on Aug 12, 2019 6:02 PM by mathguy

    How to make relations between these tables?

    nedjo

      I have the following tables:

       

      create table TBL$ORDERS

      (

        order_num  VARCHAR2(25),

        employee   VARCHAR2(100),

        createddt  VARCHAR2(20),

        modifieddt VARCHAR2(20),

        deleteddt  VARCHAR2(20),

        state      VARCHAR2(15),

        ts         VARCHAR2(20),

        nni        VARCHAR2(20)

      )

       

      create table TBL$METERS

      (

        xmlid      VARCHAR2(5),

        createddt  VARCHAR2(20),

        modifieddt VARCHAR2(20),

        deleteddt  VARCHAR2(20),

        vendor     VARCHAR2(25),

        model      VARCHAR2(25),

        serial     VARCHAR2(25)

      )

       

      create table TBL$TESTS

      (

        xmlid        VARCHAR2(5),

        createddt    VARCHAR2(20),

        testcount    VARCHAR2(5),

        successcount VARCHAR2(5),

        test1        VARCHAR2(50),

        test2        VARCHAR2(50),

        test3        VARCHAR2(50),

        test4        VARCHAR2(50)

      )

       

      with sample data as following:

       

      <Order order_num="testeewrr" employee="jaaaaaa" createdDT="2019-07-17T13:06:00" modifiedDT="2019-08-06T10:26:15" deletedDT="" state="OPENED" ts="Bel" nni="Asign 4">

      <Meter xmlID="1" createdDT="2019-07-17T13:06:00" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="ENEL" model="DB2" serial="123424" />

      <Test xmlID="1" createdDT="2019-07-22T12:48:10" testCount="4" successCount="4" test1="Connection established" test2="Serial number read" test3="Firmware read" test4="Time on meter is 22.07.2019. 12:46:30" />

       

      I need to make relations between these tables using PKs and FKs.

       

      I was told that one Order can have many Meters, and one Meter can have many Tests.

       

      But, I don't see attributes in these tables that can make a relation.

       

      Could someone help me to establish which are PK and which are FK fields in these tables?

       

      Thanks in advance.

        • 1. Re: How to make relations between these tables?
          nedjo

          XML:

           

          <Orders>

              <Order order_num="testeewrr" employee="jaaaaaa" createdDT="2019-07-17T13:06:00" modifiedDT="2019-08-06T10:26:15" deletedDT="" state="OPENED" ts="Bel" nni="Izvod 4">

                <Meter xmlID="1" createdDT="2019-07-17T13:06:00" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="ENEL" model="DB2" serial="123424" />

                <Meter xmlID="2" createdDT="2019-07-17T13:06:20" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="EWG" model="E311N2A20S" serial="45544545" />

                <Meter xmlID="3" createdDT="2019-07-17T13:06:39" modifiedDT="2019-07-18T14:24:45" deletedDT="" vendor="Meter&amp;Control" model="SM405D" serial="234324234" />

                <Meter xmlID="4" createdDT="2019-07-17T13:50:57" modifiedDT="2019-07-17T13:50:57" deletedDT="2019-07-18T14:08:34" vendor="ENEL" model="DB2" serial="" />

                <Meter xmlID="5" createdDT="2019-07-17T14:24:12" modifiedDT="2019-07-17T14:24:12" deletedDT="2019-07-18T14:08:36" vendor="ENEL" model="DB2" serial="" />

              </Order>

              <Order order_num="retert" employee="23432434" createdDT="2019-07-17T14:25:17" modifiedDT="2019-07-22T13:21:48" deletedDT="" state="CLOSED" ts="Crkveno brdo" nni="Izvod 2">

                <Meter xmlID="2" createdDT="2019-07-17T14:25:19" modifiedDT="2019-07-22T13:21:23" deletedDT="" vendor="ENEL" model="DB2" serial="10006502">

                  <Test xmlID="1" createdDT="2019-07-22T13:21:23" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 13:19:47" />

                </Meter>

              </Order>

              <Order order_num="121212" employee="sasasa" createdDT="2019-07-22T11:30:16" modifiedDT="2019-08-06T10:35:31" deletedDT="" state="OPENED" ts="Autoservis" nni="Izvod 1">

                <Meter xmlID="1" createdDT="2019-07-22T11:31:00" modifiedDT="2019-08-06T10:16:41" deletedDT="2019-07-24T13:52:04" vendor="ENEL" model="DB2" serial="">

                  <Test xmlID="1" createdDT="2019-07-22T12:48:10" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:46:30" />

                  <Test xmlID="2" createdDT="2019-07-22T12:48:54" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:14" />

                  <Test xmlID="3" createdDT="2019-07-22T12:49:28" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:48" />

                  <Test xmlID="4" createdDT="2019-07-22T12:51:47" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:50:07" />

                  <Test xmlID="5" createdDT="2019-07-25T15:50:19" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 25.07.2019. 15:48:40" />

                </Meter>

                <Meter xmlID="2" createdDT="2019-07-22T11:31:17" modifiedDT="2019-08-06T10:16:42" deletedDT="2019-07-24T13:52:07" vendor="ENEL" model="DB2" serial="">

                  <Test xmlID="1" createdDT="2019-07-22T12:48:16" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:18" />

                  <Test xmlID="2" createdDT="2019-07-22T12:48:57" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:47:59" />

                  <Test xmlID="3" createdDT="2019-07-22T12:49:31" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:48:33" />

                  <Test xmlID="4" createdDT="2019-07-22T12:51:52" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 22.07.2019. 12:50:55" />

                </Meter>

                <Meter xmlID="3" createdDT="2019-08-06T10:16:42" modifiedDT="2019-08-06T10:16:42" deletedDT="" vendor="ENEL" model="DB2" serial="" />

                <Meter xmlID="4" createdDT="2019-08-06T10:16:42" modifiedDT="2019-08-06T10:16:51" deletedDT="" vendor="ENEL" model="DB2" serial="" />

              </Order>

              <Order order_num="hola" employee="hermanos" createdDT="2019-07-22T13:48:23" modifiedDT="2019-08-06T10:19:14" deletedDT="" state="OPENED" ts="Autoservis" nni="Izvod 1">

                <Meter xmlID="4" createdDT="2019-07-22T13:48:23" modifiedDT="2019-08-06T10:19:20" deletedDT="2019-07-24T13:08:49" vendor="ENEL" model="DB2" serial="" />

                <Meter xmlID="5" createdDT="2019-07-24T13:08:36" modifiedDT="2019-08-06T10:19:20" deletedDT="2019-07-24T13:08:51" vendor="ENEL" model="DB2" serial="" />

                <Meter xmlID="6" createdDT="2019-07-24T13:08:45" modifiedDT="2019-07-24T13:51:00" deletedDT="2019-07-24T13:08:53" vendor="Mikroelektronika" model="MET410E34PI05IM006UN230" serial="11050507" />

                <Meter xmlID="7" createdDT="2019-07-24T13:09:02" modifiedDT="2019-07-24T13:51:00" deletedDT="2019-07-24T13:09:42" vendor="Mikroelektronika" model="MET410E34II05IM006UN058" serial="11050508" />

                <Meter xmlID="8" createdDT="2019-07-24T13:09:11" modifiedDT="2019-07-24T13:09:19" deletedDT="2019-07-24T13:09:44" vendor="ENEL" model="DB2" serial="A12345678" />

                <Meter xmlID="9" createdDT="2019-07-24T13:09:19" modifiedDT="2019-07-24T13:09:41" deletedDT="2019-07-24T13:09:45" vendor="ENEL" model="DB2M" serial="A12345678" />

                <Meter xmlID="10" createdDT="2019-07-24T13:09:46" modifiedDT="2019-07-24T13:12:09" deletedDT="2019-07-24T13:12:21" vendor="ENEL" model="DB2" serial="" />

                <Meter xmlID="11" createdDT="2019-07-24T13:12:34" modifiedDT="2019-07-24T13:15:38" deletedDT="2019-07-24T13:46:39" vendor="ENEL" model="DB2M" serial="A12345678" />

                <Meter xmlID="12" createdDT="2019-07-24T13:12:49" modifiedDT="2019-07-24T13:15:40" deletedDT="2019-07-24T13:46:39" vendor="Meter&amp;Control" model="SM405D" serial="12345678" />

                <Meter xmlID="13" createdDT="2019-07-24T13:13:02" modifiedDT="2019-07-24T13:13:06" deletedDT="2019-07-24T13:46:40" vendor="ENEL" model="DB2" serial="A12345678" />

                <Meter xmlID="14" createdDT="2019-07-24T13:13:06" modifiedDT="2019-07-24T13:13:08" deletedDT="2019-07-24T13:13:31" vendor="ENEL" model="DB2" serial="A12345678" />

                <Meter xmlID="15" createdDT="2019-07-24T13:13:08" modifiedDT="2019-07-24T13:13:27" deletedDT="2019-07-24T13:13:28" vendor="ENEL" model="DB2" serial="A12345678" />

                <Meter xmlID="17" createdDT="2019-07-24T13:40:59" modifiedDT="2019-07-24T13:41:02" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050501" />

                <Meter xmlID="18" createdDT="2019-07-24T13:41:02" modifiedDT="2019-07-24T13:41:09" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050502" />

                <Meter xmlID="19" createdDT="2019-07-24T13:41:09" modifiedDT="2019-07-24T13:41:12" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050504" />

                <Meter xmlID="20" createdDT="2019-07-24T13:41:12" modifiedDT="2019-07-24T13:41:15" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050505" />

                <Meter xmlID="21" createdDT="2019-07-24T13:41:15" modifiedDT="2019-07-24T13:41:18" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050506" />

                <Meter xmlID="22" createdDT="2019-07-24T13:41:18" modifiedDT="2019-07-24T13:41:21" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34PI05IM006UN230" serial="11050507" />

                <Meter xmlID="23" createdDT="2019-07-24T13:41:21" modifiedDT="2019-07-24T13:41:21" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34II05IM006UN058" serial="11050508" />

                <Meter xmlID="1" createdDT="2019-07-24T13:50:50" modifiedDT="2019-07-25T13:38:18" deletedDT="" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050501">

                  <Test xmlID="1" createdDT="2019-07-25T13:39:21" testCount="4" successCount="0" test1="ZenDirect failed to identify meter  [11050501]" test2="Нема конекције" test3="Нема конекције" test4="Нема конекције" />

                </Meter>

                <Meter xmlID="2" createdDT="2019-07-24T13:50:53" modifiedDT="2019-07-24T13:50:56" deletedDT="" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050502" />

                <Meter xmlID="3" createdDT="2019-07-24T13:50:56" modifiedDT="2019-07-24T13:50:57" deletedDT="" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050504" />

              </Order>

              <Order order_num="eneltest" employee="sasss" createdDT="2019-07-25T13:52:36" modifiedDT="2019-07-25T13:53:23" deletedDT="" state="CLOSED" ts="Autoservis" nni="Izvod 1">

                <Meter xmlID="1" createdDT="2019-07-25T13:52:58" modifiedDT="2019-07-25T13:52:58" deletedDT="" vendor="ENEL" model="DB2" serial="10006502">

                  <Test xmlID="1" createdDT="2019-07-25T13:53:16" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 25.07.2019. 13:51:37" />

                </Meter>

                <Meter xmlID="2" createdDT="2019-07-25T13:52:58" modifiedDT="2019-07-25T13:53:22" deletedDT="" vendor="ENEL" model="DB2M" serial="10006501">

                  <Test xmlID="1" createdDT="2019-07-25T13:53:21" testCount="4" successCount="4" test1="Успостављена конекција" test2="Очитан серијски број" test3="Очитан фирмвер" test4="Vrijeme na brojilu je 25.07.2019. 13:52:25" />

                </Meter>

              </Order>

            </Orders>

          • 2. Re: How to make relations between these tables?
            Paulzip

            I can't see the relationship.  Are you sure there aren't link tables?

            e.g.

            TBL$ORDERS_METERS

             

            Where you'd have order_num+xmlid as the PK or part of the PK?

            • 3. Re: How to make relations between these tables?
              Jonathan Lewis

              Judging from your second posting you're trying to convert XML into tables, but you're creating tables which match exactly the basic XML column lists.

               

              The XML implicitly identifieis the "foreign key" relationships, but you have to add explicit columns FROM the parent to the child to capure this information, so your orders table has a primary key order_num. Your meters table needs a column order_num which is declared as the foreign key to orders, then the primary key of meters is (order_num, xml_id), Your tests table then needs two columns (echoing the primary key of meters) call them order_num, meters_xml_id, then these two columns will be the foreign key to meters, and the primary key of tests will be (order_num, meters_xml_id, xml_id).

               

              Regards

              Jonathan Lewis

              • 4. Re: How to make relations between these tables?
                nedjo

                Yes, there should be field order_num in meter table, and meter_id in Test table.

                 

                But then, I have a problem with my parser xml procedure. Could you read my procedure and help me to include FK in it as well?

                 

                Procedure parse_xml is

                   p              Dbms_Xmlparser.Parser;

                   v_Doc          Dbms_Xmldom.Domdocument;

                   v_Root_Element Dbms_Xmldom.Domelement;

                   v_Child_Nodes  Dbms_Xmldom.Domnodelist;

                   v_Child_Node   Dbms_Xmldom.Domnode;

                   v_Text_Node    Dbms_Xmldom.Domnode;

                   v_Emp_Nodes    Dbms_Xmldom.Domnodelist;

                  

                   v_Emp_Nodes2    Dbms_Xmldom.Domnodelist;

                   v_Emp_Node     Dbms_Xmldom.Domnode;

                   v_Emp_Node2     Dbms_Xmldom.Domnode;

                  

                   temp varchar(100);

                   temp2 varchar(100);

                   temp3 varchar(100);

                  

                   temp4 varchar(100);

                   temp5 varchar(100);

                   temp6 varchar(100);

                   temp7 varchar(100);

                   temp8 varchar(100);

                  

                   temp9 varchar(100);

                   temp10 varchar(100);

                   temp11 varchar(100);

                   temp12 varchar(100);

                  

                   temp13 varchar(100);

                   ---

                   v_Xml_Data clob;

                  

                   o_order_num varchar(100);

                   o_employee varchar(100);

                   o_createdDT date;

                   o_modifiedDT date;

                   o_deletedDT date;

                   o_state varchar(100);

                   o_ts varchar(100);

                   o_nni varchar(100);

                  

                   m_xmlID number;

                   m_createdDT date;

                   m_modifiedDT date;

                   m_deletedDT date;

                   m_vendor varchar(100);

                   m_model varchar(100);

                   m_serial number;

                  

                   t_xmlID number;

                   t_createdDT date;

                   t_testCount number;

                   t_successCount number;

                   t_test1 varchar(100);

                   t_test2 varchar(100);

                   t_test3 varchar(100);

                   t_test4 varchar(100);

                   --

                   v_Attr_Nodes     Dbms_Xmldom.Domnamednodemap;

                   v_Attr_Node      Dbms_Xmldom.Domnode;

                   v_Attribute_Name Varchar2(50);

                   v_Node_Name      Varchar2(50);

                   v_Node_Value     Varchar2(100);

                Begin

                   -- Note text contains no <?xml version="1"?>

                   v_Xml_Data := ' <Orders>

                    <Order order_num="testeewrr" employee="jaaaaaa" createdDT="2019-07-17T13:06:00" modifiedDT="2019-08-06T10:26:15" deletedDT="" state="OPENED" ts="Bel" nni="Izvod 4">

                      <Meter xmlID="1" createdDT="2019-07-17T13:06:00" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="ENEL" model="DB2" serial="123424" />

                      <Meter xmlID="2" createdDT="2019-07-17T13:06:20" modifiedDT="2019-07-22T13:48:04" deletedDT="" vendor="EWG" model="E311N2A20S" serial="45544545" />

                      <Meter xmlID="3" createdDT="2019-07-17T13:06:39" modifiedDT="2019-07-18T14:24:45" deletedDT="" vendor="Meter&amp;Control" model="SM405D" serial="234324234" />

                      <Meter xmlID="4" createdDT="2019-07-17T13:50:57" modifiedDT="2019-07-17T13:50:57" deletedDT="2019-07-18T14:08:34" vendor="ENEL" model="DB2" serial="" />

                      <Meter xmlID="5" createdDT="2019-07-17T14:24:12" modifiedDT="2019-07-17T14:24:12" deletedDT="2019-07-18T14:08:36" vendor="ENEL" model="DB2" serial="" />

                    </Order>

                    <Order order_num="retert" employee="23432434" createdDT="2019-07-17T14:25:17" modifiedDT="2019-07-22T13:21:48" deletedDT="" state="CLOSED" ts="Crkveno brdo" nni="Izvod 2">

                      <Meter xmlID="2" createdDT="2019-07-17T14:25:19" modifiedDT="2019-07-22T13:21:23" deletedDT="" vendor="ENEL" model="DB2" serial="10006502">

                        <Test xmlID="1" createdDT="2019-07-22T13:21:23" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 13:19:47" />

                      </Meter>

                    </Order>

                    <Order order_num="121212" employee="sasasa" createdDT="2019-07-22T11:30:16" modifiedDT="2019-08-06T10:35:31" deletedDT="" state="OPENED" ts="Autoservis" nni="Izvod 1">

                      <Meter xmlID="1" createdDT="2019-07-22T11:31:00" modifiedDT="2019-08-06T10:16:41" deletedDT="2019-07-24T13:52:04" vendor="ENEL" model="DB2" serial="">

                        <Test xmlID="1" createdDT="2019-07-22T12:48:10" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:46:30" />

                        <Test xmlID="2" createdDT="2019-07-22T12:48:54" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:47:14" />

                        <Test xmlID="3" createdDT="2019-07-22T12:49:28" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:47:48" />

                        <Test xmlID="4" createdDT="2019-07-22T12:51:47" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:50:07" />

                        <Test xmlID="5" createdDT="2019-07-25T15:50:19" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 25.07.2019. 15:48:40" />

                      </Meter>

                      <Meter xmlID="2" createdDT="2019-07-22T11:31:17" modifiedDT="2019-08-06T10:16:42" deletedDT="2019-07-24T13:52:07" vendor="ENEL" model="DB2" serial="">

                        <Test xmlID="1" createdDT="2019-07-22T12:48:16" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:47:18" />

                        <Test xmlID="2" createdDT="2019-07-22T12:48:57" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:47:59" />

                        <Test xmlID="3" createdDT="2019-07-22T12:49:31" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:48:33" />

                        <Test xmlID="4" createdDT="2019-07-22T12:51:52" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 22.07.2019. 12:50:55" />

                      </Meter>

                      <Meter xmlID="3" createdDT="2019-08-06T10:16:42" modifiedDT="2019-08-06T10:16:42" deletedDT="" vendor="ENEL" model="DB2" serial="" />

                      <Meter xmlID="4" createdDT="2019-08-06T10:16:42" modifiedDT="2019-08-06T10:16:51" deletedDT="" vendor="ENEL" model="DB2" serial="" />

                    </Order>

                    <Order order_num="hola" employee="hermanos" createdDT="2019-07-22T13:48:23" modifiedDT="2019-08-06T10:19:14" deletedDT="" state="OPENED" ts="Autoservis" nni="Izvod 1">

                      <Meter xmlID="4" createdDT="2019-07-22T13:48:23" modifiedDT="2019-08-06T10:19:20" deletedDT="2019-07-24T13:08:49" vendor="ENEL" model="DB2" serial="" />

                      <Meter xmlID="5" createdDT="2019-07-24T13:08:36" modifiedDT="2019-08-06T10:19:20" deletedDT="2019-07-24T13:08:51" vendor="ENEL" model="DB2" serial="" />

                      <Meter xmlID="6" createdDT="2019-07-24T13:08:45" modifiedDT="2019-07-24T13:51:00" deletedDT="2019-07-24T13:08:53" vendor="Mikroelektronika" model="MET410E34PI05IM006UN230" serial="11050507" />

                      <Meter xmlID="7" createdDT="2019-07-24T13:09:02" modifiedDT="2019-07-24T13:51:00" deletedDT="2019-07-24T13:09:42" vendor="Mikroelektronika" model="MET410E34II05IM006UN058" serial="11050508" />

                      <Meter xmlID="8" createdDT="2019-07-24T13:09:11" modifiedDT="2019-07-24T13:09:19" deletedDT="2019-07-24T13:09:44" vendor="ENEL" model="DB2" serial="A12345678" />

                      <Meter xmlID="9" createdDT="2019-07-24T13:09:19" modifiedDT="2019-07-24T13:09:41" deletedDT="2019-07-24T13:09:45" vendor="ENEL" model="DB2M" serial="A12345678" />

                      <Meter xmlID="10" createdDT="2019-07-24T13:09:46" modifiedDT="2019-07-24T13:12:09" deletedDT="2019-07-24T13:12:21" vendor="ENEL" model="DB2" serial="" />

                      <Meter xmlID="11" createdDT="2019-07-24T13:12:34" modifiedDT="2019-07-24T13:15:38" deletedDT="2019-07-24T13:46:39" vendor="ENEL" model="DB2M" serial="A12345678" />

                      <Meter xmlID="12" createdDT="2019-07-24T13:12:49" modifiedDT="2019-07-24T13:15:40" deletedDT="2019-07-24T13:46:39" vendor="Meter&amp;Control" model="SM405D" serial="12345678" />

                      <Meter xmlID="13" createdDT="2019-07-24T13:13:02" modifiedDT="2019-07-24T13:13:06" deletedDT="2019-07-24T13:46:40" vendor="ENEL" model="DB2" serial="A12345678" />

                      <Meter xmlID="14" createdDT="2019-07-24T13:13:06" modifiedDT="2019-07-24T13:13:08" deletedDT="2019-07-24T13:13:31" vendor="ENEL" model="DB2" serial="A12345678" />

                      <Meter xmlID="15" createdDT="2019-07-24T13:13:08" modifiedDT="2019-07-24T13:13:27" deletedDT="2019-07-24T13:13:28" vendor="ENEL" model="DB2" serial="A12345678" />

                      <Meter xmlID="17" createdDT="2019-07-24T13:40:59" modifiedDT="2019-07-24T13:41:02" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050501" />

                      <Meter xmlID="18" createdDT="2019-07-24T13:41:02" modifiedDT="2019-07-24T13:41:09" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050502" />

                      <Meter xmlID="19" createdDT="2019-07-24T13:41:09" modifiedDT="2019-07-24T13:41:12" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050504" />

                      <Meter xmlID="20" createdDT="2019-07-24T13:41:12" modifiedDT="2019-07-24T13:41:15" deletedDT="2019-07-24T13:46:40" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050505" />

                      <Meter xmlID="21" createdDT="2019-07-24T13:41:15" modifiedDT="2019-07-24T13:41:18" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050506" />

                      <Meter xmlID="22" createdDT="2019-07-24T13:41:18" modifiedDT="2019-07-24T13:41:21" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34PI05IM006UN230" serial="11050507" />

                      <Meter xmlID="23" createdDT="2019-07-24T13:41:21" modifiedDT="2019-07-24T13:41:21" deletedDT="2019-07-24T13:46:41" vendor="Mikroelektronika" model="MET410E34II05IM006UN058" serial="11050508" />

                      <Meter xmlID="1" createdDT="2019-07-24T13:50:50" modifiedDT="2019-07-25T13:38:18" deletedDT="" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050501">

                        <Test xmlID="1" createdDT="2019-07-25T13:39:21" testCount="4" successCount="0" test1="ZenDirect failed to identify meter  [11050501]" test2="Nema konekcije" test3="Nema konekcije" test4="Nema konekcije" />

                      </Meter>

                      <Meter xmlID="2" createdDT="2019-07-24T13:50:53" modifiedDT="2019-07-24T13:50:56" deletedDT="" vendor="Mikroelektronika" model="MEM500E12NI05IM060UN230" serial="11050502" />

                      <Meter xmlID="3" createdDT="2019-07-24T13:50:56" modifiedDT="2019-07-24T13:50:57" deletedDT="" vendor="Mikroelektronika" model="MET410E34NI05IM060UN230" serial="11050504" />

                    </Order>

                    <Order order_num="eneltest" employee="sasss" createdDT="2019-07-25T13:52:36" modifiedDT="2019-07-25T13:53:23" deletedDT="" state="CLOSED" ts="Autoservis" nni="Izvod 1">

                      <Meter xmlID="1" createdDT="2019-07-25T13:52:58" modifiedDT="2019-07-25T13:52:58" deletedDT="" vendor="ENEL" model="DB2" serial="10006502">

                        <Test xmlID="1" createdDT="2019-07-25T13:53:16" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 25.07.2019. 13:51:37" />

                      </Meter>

                      <Meter xmlID="2" createdDT="2019-07-25T13:52:58" modifiedDT="2019-07-25T13:53:22" deletedDT="" vendor="ENEL" model="DB2M" serial="10006501">

                        <Test xmlID="1" createdDT="2019-07-25T13:53:21" testCount="4" successCount="4" test1="Uspostavljena konekcija" test2="Ocitan serijski broj" test3="?citan firmver" test4="Vrijeme na brojilu je 25.07.2019. 13:52:25" />

                      </Meter>

                    </Order>

                  </Orders>

                  ';

                   -- Create XML Parser.

                   p := Dbms_Xmlparser.Newparser;

                   Dbms_Xmlparser.Setvalidationmode(p

                                                   ,False);

                   -- Parse XML into DOM object                      

                   Dbms_Xmlparser.Parsebuffer(p,v_Xml_Data);

                  

                      -- Document             

                   v_Doc := Dbms_Xmlparser.Getdocument(p);

                   -- Root element (<orders>)

                   v_Root_Element := Dbms_Xmldom.Getdocumentelement(v_Doc);

                   -- Get attribute value

                  

                      -- Node list (order) of v_Root_Element (Dbms_xmldom.Domnodelist)

                   v_Emp_Nodes := Dbms_Xmldom.Getelementsbytagname(v_Root_Element

                                                                  ,'Order');

                   temp:= to_char(o_createdDT);

                   temp2:= to_char(o_modifiedDT);

                   temp3:= to_char(o_deletedDT);

                  

                   temp4:=to_char(m_xmlID);

                   temp5:=to_char(m_createdDT);

                   temp6:=to_char(m_modifiedDT);

                   temp7:=to_char(m_deletedDT);

                   temp8:=to_char(m_serial);

                  

                   temp9:=to_char(t_xmlID);

                   temp10:=to_char(t_createdDT);

                   temp11:=to_char(t_testCount);

                   temp12:=to_char(t_successCount);

                  

                   temp13:=to_char(m_xmlID);                                          

                                                                 

                   For j In 0 .. Dbms_Xmldom.Getlength(v_Emp_Nodes) Loop

                      v_Emp_Node := Dbms_Xmldom.Item(v_Emp_Nodes

                                                    ,j);

                      -- Attribute List (Dbms_xmldom.Domnamednodemap)                          

                      v_Attr_Nodes := Dbms_Xmldom.Getattributes(v_Emp_Node);

                      --

                      If (Dbms_Xmldom.Isnull(v_Attr_Nodes) = False) Then

                         For i In 0 .. Dbms_Xmldom.Getlength(v_Attr_Nodes) - 1 Loop

                            v_Attr_Node := Dbms_Xmldom.Item(v_Attr_Nodes

                                                           ,i);

                            v_Node_Name := Dbms_Xmldom.Getnodename(v_Attr_Node);

                            --

                            If v_Node_Name = 'order_num' Then

                               o_order_num := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'employee' Then

                              o_employee := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'createdDT' Then

                              temp := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'modifiedDT' Then

                              temp2 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'deletedDT' Then

                              temp3 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'state' Then

                              o_state := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'ts' Then

                              o_ts := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'nni' Then

                              o_nni := Dbms_Xmldom.Getnodevalue(v_Attr_Node);   

                            End If;

                           

                 

                        

                         End Loop;

                         Dbms_Output.Put_Line('o_order_num =' || o_order_num );

                         Dbms_Output.Put_Line('o_employee =' || o_employee );

                         Dbms_Output.Put_Line('o_createdDT =' || temp );

                         Dbms_Output.Put_Line('o_modifiedDT =' || temp2 );

                         Dbms_Output.Put_Line('o_deletedDT =' || temp3 );

                         Dbms_Output.Put_Line('o_state =' || o_state );

                         Dbms_Output.Put_Line('o_ts =' || o_ts );

                         Dbms_Output.Put_Line('o_nni =' || o_nni );

                        

                 

                         /*insert into tbl$orders

                         (order_num, employee, createdDT, modifiedDT, deletedDT, state, ts, nni)

                         values

                         (o_order_num, o_employee, temp, temp2, temp3, o_state, o_ts, o_nni);*/

                 

                        

                      End If;

                 

                   ---------                             

                end loop;

                 

                 

                 

                   v_Emp_Nodes := Dbms_Xmldom.Getelementsbytagname(v_Root_Element

                                                                  ,'Order');

                                                                 

                                                                 

                                                                 

                For j In 0 .. Dbms_Xmldom.Getlength(v_Emp_Nodes) Loop

                      v_Emp_Node := Dbms_Xmldom.Item(v_Emp_Nodes

                                                    ,j);

                      -- Attribute List (Dbms_xmldom.Domnamednodemap)                          

                      v_Attr_Nodes := Dbms_Xmldom.Getattributes(v_Emp_Node);

                      --

                      If (Dbms_Xmldom.Isnull(v_Attr_Nodes) = False) Then

                         For i In 0 .. Dbms_Xmldom.Getlength(v_Attr_Nodes) - 1 Loop

                            v_Attr_Node := Dbms_Xmldom.Item(v_Attr_Nodes

                                                           ,i);

                            v_Node_Name := Dbms_Xmldom.Getnodename(v_Attr_Node);

                            --

                            If v_Node_Name = 'xmlID' Then

                               temp13 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'createdDT' Then

                               temp5 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'modifiedDT' Then

                               temp6 := Dbms_Xmldom.Getnodevalue(v_Attr_Node); 

                            elsif v_Node_Name = 'deletedDT' Then

                               temp7 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'vendor' Then

                               m_vendor := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'model' Then

                               m_model := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            elsif v_Node_Name = 'serial' Then

                               temp8 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);               

                            End If;

                         End Loop;

                         Dbms_Output.Put_Line('m_xmlID =' || m_xmlID );

                         Dbms_Output.Put_Line('m_createdDT =' || temp5 );

                         Dbms_Output.Put_Line('m_modifiedDT =' || temp6 );

                         Dbms_Output.Put_Line('m_deletedDT =' || temp7 );

                         Dbms_Output.Put_Line('m_vendor =' || m_vendor );

                         Dbms_Output.Put_Line('m_model =' || m_model );

                         Dbms_Output.Put_Line('m_serial =' || temp8 );

                        

                         insert into tbl$meters

                         (xmlID, createdDT, modifiedDT, deletedDT, vendor, model, serial)

                         values

                         (temp13, temp5, temp6, temp7, m_vendor, m_model, temp8);

                        

                      End If;

                 

                   ---------                             

                end loop;

                 

                   v_Emp_Nodes := Dbms_Xmldom.Getelementsbytagname(v_Root_Element

                                                                  ,'Test');

                                                                 

                For j In 0 .. Dbms_Xmldom.Getlength(v_Emp_Nodes) Loop

                      v_Emp_Node := Dbms_Xmldom.Item(v_Emp_Nodes

                                                    ,j);

                      -- Attribute List (Dbms_xmldom.Domnamednodemap)                          

                      v_Attr_Nodes := Dbms_Xmldom.Getattributes(v_Emp_Node);

                      --

                      If (Dbms_Xmldom.Isnull(v_Attr_Nodes) = False) Then

                         For i In 0 .. Dbms_Xmldom.Getlength(v_Attr_Nodes) - 1 Loop

                            v_Attr_Node := Dbms_Xmldom.Item(v_Attr_Nodes

                                                           ,i);

                            v_Node_Name := Dbms_Xmldom.Getnodename(v_Attr_Node);

                            --

                            If v_Node_Name = 'xmlID' Then

                               temp9 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'createdDT' Then

                               temp10 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'testCount' Then

                               temp11 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'successCount' Then

                               temp12 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'test1' Then

                               t_test1 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'test2' Then

                               t_test2 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'test3' Then

                               t_test3 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);

                            ELSIF v_Node_Name = 'test4' Then

                               t_test4 := Dbms_Xmldom.Getnodevalue(v_Attr_Node);          

                               

                            End If;

                         End Loop;

                         Dbms_Output.Put_Line('t_xmlID =' || temp9 );

                         Dbms_Output.Put_Line('t_createdDT =' || temp10 );

                         Dbms_Output.Put_Line('t_testCount =' || temp11 );

                         Dbms_Output.Put_Line('t_successCount =' || temp12 );

                         Dbms_Output.Put_Line('t_test1 =' || t_test1 );

                         Dbms_Output.Put_Line('t_test2 =' || t_test2 );

                         Dbms_Output.Put_Line('t_test3 =' || t_test3 );

                         Dbms_Output.Put_Line('t_test4 =' || t_test4 );

                        

                         /*insert into tbl$tests

                         (xmlID, createdDT, testCount, successCount, test1, test2, test3, test4)

                         values

                         (temp9, temp10, temp11, temp12, t_test1, t_test2, t_test3, t_test4);*/

                 

                      End If;

                 

                   ---------                             

                end loop;

                                

                end;

                • 5. Re: How to make relations between these tables?
                  Paulzip

                  Aha, yes good points, Jonathan. I think you've hit the nail on the head here.

                   

                  I'd assumed the table structure was correct, and OP was trying to populate it from the XML, but it does appear the tables have been created directly one to one from the XML content, without the implied relationships given by the XML structure.

                  • 6. Re: How to make relations between these tables?
                    mathguy

                    negru wrote:

                     

                    Yes, there should be field order_num in meter table, and meter_id in Test table.

                     

                    But then, I have a problem with my parser xml procedure. Could you read my procedure and help me to include FK in it as well?

                     

                     

                     

                    No... the METER table needs one additional column, ORDER_NUM; that's correct. But the TEST table needs two additional columns: both ORDER_NUM and METER_ID. That is because METER_ID is not an independent primary key across all rows in METER; it's only unique within each ORDER_NUM. The primary key of METER is not METER_ID, but the pair (ORDER_NUM, METER_ID).

                     

                    Similarly, test id's are not unique; they are only unique within each METER. So the primary key of METER will have to be a triple, (ORDER_NUM, METER_ID, TEST_ID). (This is not needed for the relations between tables, but it is still important that you create a PK on the table).

                     

                    Question... what process creates the ORDER_NUM and the XMLID attributes? How can you guarantee that they will, in fact, be unique within their contexts? In an XML document you may have any number of ORDER elements with the same value for the ORDER_NUM attribute.

                     

                    By the way: Why the name ORDER_NUM when values may look like 'testeewrr'? NUM suggests NUMBER; why not ORDER_ID instead?

                     

                    Why are you saving all columns in string data type? Why not save dates, for example, in DATE data type? How will you know that the dates you save in your tables are legitimate dates, and you are not storing garbage like 33 December 2018? If you are certain the dates will all be valid, that's all the more reason to save them as dates!

                    • 7. Re: How to make relations between these tables?
                      Paulzip

                      You're over complicating things with your parsing. You simple need chained XML tables.  Your solution should be something like this...

                      create table TBL$ORDERS
                      (
                        order_num  VARCHAR2(25),
                        employee   VARCHAR2(100),
                        createddt  VARCHAR2(20),
                        modifieddt VARCHAR2(20),
                        deleteddt  VARCHAR2(20),
                        state      VARCHAR2(15),
                        ts         VARCHAR2(20),
                        nni        VARCHAR2(20),
                        constraint pk_orders primary key (order_num)
                      );
                      
                      create table TBL$METERS
                      (
                        order_num  VARCHAR2(25),
                        xmlid      VARCHAR2(5),
                        createddt  VARCHAR2(20),
                        modifieddt VARCHAR2(20),
                        deleteddt  VARCHAR2(20),
                        vendor     VARCHAR2(25),
                        model      VARCHAR2(25),
                        serial     VARCHAR2(25),
                        constraint pk_meters primary key (order_num, xmlid),
                        constraint fk_orders foreign key (order_num) references TBL$ORDERS (order_num)
                      );
                      
                      create table TBL$TESTS
                      (
                        order_num    VARCHAR2(25),
                        meterxmlid   VARCHAR2(5),  
                        xmlid        VARCHAR2(5),  
                        createddt    VARCHAR2(20),
                        testcount    VARCHAR2(5),
                        successcount VARCHAR2(5),
                        test1        VARCHAR2(50),
                        test2        VARCHAR2(50),
                        test3        VARCHAR2(50),
                        test4        VARCHAR2(50),
                        constraint pk_tests primary key (order_num, meterxmlid, xmlid),
                        constraint fk_meters foreign key (order_num, meterxmlid) references TBL$METERS (order_num, xmlid)
                      );
                      
                      create or replace procedure PopulateTables(pXMLClob clob) is  
                      begin  
                        for recOrders in (  
                          select *  
                          from   XMLTable(  
                                   '/Orders/Order'  
                                   passing XMLType(pXMLClob)  
                                   columns  
                                     order_num  VARCHAR2(25)  path '@order_num',  
                                     employee   VARCHAR2(100) path '@employee',  
                                     createddt  VARCHAR2(20)  path '@createdDT',  
                                     modifieddt VARCHAR2(20)  path '@modifiedDT',  
                                     deleteddt  VARCHAR2(20)  path '@deletedDT',  
                                     state      VARCHAR2(15)  path '@state',  
                                     ts         VARCHAR2(20)  path '@ts',  
                                     nni        VARCHAR2(20)  path '@nni',  
                                     Meters     xmltype       path 'Meter'  
                                   )  
                        )  
                        loop  
                          insert into TBL$ORDERS(order_num, employee, createddt, modifieddt, deleteddt, state, ts, nni)  
                          values (recOrders.order_num, recOrders.employee, recOrders.createddt, recOrders.modifieddt, recOrders.deleteddt, recOrders.state, recOrders.ts, recOrders.nni);  
                          for recMeters in (  
                            select *  
                            from   XMLTable(  
                                     '/Meter'  
                                     passing recOrders.Meters  
                                     columns  
                                        xmlid      VARCHAR2(5)  path '@xmlID',  
                                        createddt  VARCHAR2(20) path '@createdDT',  
                                        modifieddt VARCHAR2(20) path '@modifiedDT',  
                                        deleteddt  VARCHAR2(20) path '@deletedDT',  
                                        vendor     VARCHAR2(25) path '@vendor',  
                                        model      VARCHAR2(25) path '@model',  
                                        serial     VARCHAR2(25) path '@serial',  
                                        order_num  VARCHAR2(25) path '@nni',  
                                        tests      xmltype      path 'Test'  
                                   )      
                          )  
                          loop  
                            insert into TBL$METERS (order_num, xmlid, createddt, modifieddt, deleteddt, vendor, model, serial)  
                            values (recOrders.order_num, recMeters.xmlid, recMeters.createddt, recMeters.modifieddt, recMeters.deleteddt, recMeters.vendor, recMeters.model, recMeters.serial);  
                            for recTest in (  
                              select *  
                              from   XMLTable(  
                                       '/Test'  
                                       passing recMeters.Tests  
                                       columns  
                                         xmlid        VARCHAR2(5)  path '@xmlID',                    
                                         createddt    VARCHAR2(20) path '@createdDT',  
                                         testcount    VARCHAR2(5)  path '@testCount',   
                                         successcount VARCHAR2(5)  path '@successCount',                   
                                         test1        VARCHAR2(50) path '@test1',                   
                                         test2        VARCHAR2(50) path '@test2',                   
                                         test3        VARCHAR2(50) path '@test3',                   
                                         test4        VARCHAR2(50) path '@test4'  
                                     )          
                            )  
                            loop  
                              insert into TBL$TESTS (order_num, meterxmlid, xmlid, createddt, testcount, successcount, test1, test2, test3, test4)  
                              values (recOrders.order_num, recMeters.xmlid, recTest.xmlid, recTest.createddt, recTest.testcount, recTest.successcount, recTest.test1, recTest.test2, recTest.test3, recTest.test4);  
                            end loop;    
                          end loop;   
                        end loop;  
                        commit;    
                      end;  
                      /
                      
                      
                      -- Call the proc with the XML data clob
                      
                      
                      • 8. Re: How to make relations between these tables?
                        mathguy

                        I wrote:

                         

                         

                        Question... what process creates the ORDER_NUM and the XMLID attributes? How can you guarantee that they will, in fact, be unique within their contexts? In an XML document you may have any number of ORDER elements with the same value for the ORDER_NUM attribute.

                         

                        Thinking about this a bit more...

                         

                        Perhaps it doesn't make sense to use ORDER_NUM as PK in TBL$ORDERS and as FK in TBL$METERS to begin with. (For example, for the reason I mentioned.)

                         

                        Instead, create additional columns as follows:   ORDER_ID (populated by a sequence of some kind or another, depending on your Oracle version) as primary key in TBL$ORDERS, and ORDER_ID in TBL$METERS as foreign key pointing to TBL$ORDERS.ORDER_ID. Then, one more column, METER_ID (populated by a sequence) as primary key in TBL$METERS, and a column METER_ID in TBL$TESTS, foreign key pointing to TBL$METERS.METER_ID. And - as a best practice - one more column, TESTS_ID, in TBL$TESTS, populated by a sequence, to serve as PK in TBL$TESTS.

                         

                        You are already using some of these column names (ending in _ID) for values coming from the XML document. You should probably change those column names, perhaps to end in _XML_ID or whatever. Choose names that will make it easy to tell what's what (what the intent of each column is).

                        • 9. Re: How to make relations between these tables?
                          nedjo

                          Paulzip, I gave you correct answer mark. Could you expand your answer with the full / complete code?

                          • 10. Re: How to make relations between these tables?
                            nedjo

                            Paulzip, actually, your code doesn't work. I have tried it.

                            • 11. Re: How to make relations between these tables?
                              nedjo

                              @Paulzip line: passing XMLType()

                              • 12. Re: How to make relations between these tables?
                                Paulzip

                                negru wrote:

                                 

                                Paulzip, actually, your code doesn't work. I have tried it.

                                I didn't run it, but noticed the passing clauses should be changed to below..

                                 

                                1.       from   XMLTable( 
                                2.                '/Orders/Order' 
                                3.                passing recOrders.Meters
                                4. [snip...]
                                5.         from   XMLTable( 
                                6.                  '/Orders/Order' 
                                7.                  passing recMeters.Tests
                                8. --                 [snip... etc etc] 
                                9.                )
                                • 13. Re: How to make relations between these tables?
                                  nedjo

                                  Paulzip, still doesn't work. ora 06512: SYS.XMLTYPE

                                  • 14. Re: How to make relations between these tables?
                                    nedjo

                                    Paulzip, could you please test the procedure and write full code? I have tried and it doesn't insert data in the first table (orders).

                                     

                                    procedure PopulateTables(pXMLClob clob) is 

                                    begin 

                                      for recOrders in ( 

                                        select * 

                                        from   XMLTable( 

                                                 '/Orders/Order' 

                                                 passing XMLType('<Orders>'||pXMLClob||'</Orders>') 

                                                   columns 

                                                     order_num  VARCHAR2(25)  path '@order_num', 

                                                     employee   VARCHAR2(100) path '@employee', 

                                                     createddt  VARCHAR2(20)  path '@createdDT', 

                                                     modifieddt VARCHAR2(20)  path '@modifiedDT', 

                                                     deleteddt  VARCHAR2(20)  path '@deletedDT', 

                                                     state      VARCHAR2(15)  path '@state', 

                                                     ts         VARCHAR2(20)  path '@ts', 

                                                     nni        VARCHAR2(20)  path '@nni' 

                                                     /*Meters     xmltype      path 'Meter'*/ 

                                                 )

                                      ) 

                                      loop 

                                        insert into TBL$ORDERS(order_num, employee, createddt, modifieddt, deleteddt, state, ts, nni) 

                                        values (recOrders.order_num, recOrders.employee, recOrders.createddt, recOrders.modifieddt, recOrders.deleteddt, recOrders.state, recOrders.ts, recOrders.nni); 

                                       /* for recMeters in ( 

                                          select * 

                                          from   XMLTable( 

                                                   '/Orders/Order' 

                                                   passing recOrders.Meters 

                                                     columns 

                                                        xmlid      VARCHAR2(5)  path '@xmlid', 

                                                        createddt  VARCHAR2(20) path '@createdDT', 

                                                        modifieddt VARCHAR2(20) path '@modifiedDT', 

                                                        deleteddt  VARCHAR2(20) path '@deletedDT', 

                                                        vendor     VARCHAR2(25) path '@vendor', 

                                                        model      VARCHAR2(25) path '@model', 

                                                        serial     VARCHAR2(25) path '@serial', 

                                                        order_num  VARCHAR2(25) path '@order_num', 

                                                        tests      xmltype      path 'test' 

                                                 )     

                                        ) 

                                        loop 

                                          insert into TBL$METERS (order_num, xmlid, createddt, modifieddt, deleteddt, vendor, model, serial) 

                                          values (recOrders.order_num, recMeters.xmlid, recMeters.createddt, recMeters.modifieddt, recMeters.deleteddt, recMeters.vendor, recMeters.model, recMeters.serial); 

                                          for recTest in ( 

                                            select * 

                                            from   XMLTable( 

                                                     '/Orders/Order' 

                                                     passing recMeters.Tests 

                                                     columns

                                      xmlid        VARCHAR2(5) path '@xmlID',

                                      createddt    VARCHAR2(20) path '@createdDT',

                                      testcount    VARCHAR2(5) path '@testCount',

                                      successcount VARCHAR2(5) path '@successCount',

                                      test1        VARCHAR2(50) path '@test1',

                                      test2        VARCHAR2(50) path '@test2',

                                      test3        VARCHAR2(50) path '@test3',

                                      test4        VARCHAR2(50) path '@test4',

                                      meter_xmlid  VARCHAR2(5) path '@xmlID',

                                      order_order_num varchar(25) path '@order_num'

                                                     

                                                   )         

                                          ) 

                                          loop 

                                           

                                          insert into TBL$TESTS (xmlid, createddt, testcount, successcount, test1, test2, test3, test4, meter_xmlid , order_order_num  ) 

                                          values (recTest.xmlid, recTest.createddt, recTest.testcount, recTest.successcount, recTest.test1, recTest.test2, recTest.test3, recTest.test4, recMeters.xmlid, recOrders.Order_num);

                                           

                                          end loop;   

                                        end loop;*/  

                                      end loop; 

                                      commit;   

                                    end;                      

                                    1 2 Previous Next