9 Replies Latest reply: Mar 21, 2012 3:29 PM by odie_63 RSS

    Read XML File on Unix from PL SQL

    Jeet_A
      Hi,

      How to read XML file placed on Unix Dir .

      If file name is yx.xml and file contain below data. Please let me know how to read

      column values such as AutomatedTransferOrders uniqueId="11761:234570"
      2)Cto Type = CTO
      3)Description = Test PIP


      <AgileData xsi:schemaLocation="http://www.oracle.com/ http://www.oracle.com/technology/products/applications/xml/aXML.xsd">
      <AutomatedTransferOrders uniqueId="11761:234570">
      <CoverPage>
      <CtoType>CTO</CtoType>
      <CtoNumber>CTO00069</CtoNumber>
      <Description>Created by subscriber DBA Program Prototype Change Subscriber.</Description>
      <Status>Released</Status>
      <Workflow>Default CTOs</Workflow>
      <Subscriber>DBA Program Prototype Change Subscriber</Subscriber>
      <DateOriginated>2012-03-15T00:29:10Z</DateOriginated>
      <DateReleased>2012-03-15T00:29:11Z</DateReleased>
      </CoverPage>
      <SelectedObjects referentId="6000:6243458">
      <Type>Program Change Order</Type>
      <NameNumber>PG0000038</NameNumber>
      <Description>Test PIP</Description>
      <LifecycleStatus>Released</LifecycleStatus>
      </SelectedObjects>
      </AutomatedTransferOrders>
      </AgileData>

      Appreciate your help.

      Thanks and Regards,
      Abhi
        • 2. Re: Read XML File on Unix from PL SQL
          Jeet_A
          Hi,

          Thanks for update.

          i could retrive data when I removed
          xsi:schemaLocation="http://www.oracle.com/technology/products/applications/xml/plm/2010/09/ http://www.oracle.com/technology/products/applications/xml/plm/2010/09/aXML.xsd

          22 select x.*
          23 from t
          24 ,xmltable('/AgileData/AutomatedTransferOrders'
          25 passing t.xml
          26 columns uniqueId varchar2(40) path './@uniqueId'
          27 ,AtoNumber varchar2(20) path './AtoNumber'
          28 ) x
          29 /

          UNIQUEID ATONUMBER
          ---------------------------------------- --------------------
          11761:6243470 ATO00069


          I have two questions
          1) How to handle xsi
          2) How to read Tags inside other tag eg.
          SelectedObjects ReferenceId and NameNumber

          '<AgileData xsi:schemaLocation="http://www.oracle.com/technology/products/applications/xml/plm/2010/09/ http://www.oracle.com/technology/products/applications/xml/plm/2010/09/aXML.xsd">
          <AutomatedTransferOrders uniqueId="11761:6243470">
          <AtoType>ATO</AtoType>
          <AtoNumber>ATO00069</AtoNumber>
          <Description>Created by subscriber BDA Program Prototype Change Subscriber.</Description>
          <Status>Released</Status>
          <Workflow>Default ATOs</Workflow>
          <Subscriber>BDA Program Prototype Change Subscriber</Subscriber>
          <DateOriginated>2012-03-15T00:29:10Z</DateOriginated>
          <DateReleased>2012-03-15T00:29:11Z</DateReleased>
          <SelectedObjects referentId="6000:6243458">
          <Type>Program Change Order</Type>
          <NameNumber>PG0000038</NameNumber>
          <Description>Test PIP</Description>
          <LifecycleStatus>Released</LifecycleStatus>
          </SelectedObjects>
          </AutomatedTransferOrders>
          </AgileData>'


          Appreciate your help.

          Thanks and Regards,
          Abhi
          • 3. Re: Read XML File on Unix from PL SQL
            odie_63
            Hi Abhi,

            The XML you gave is not valid because the xsi prefix is not declared anywhere in the root element.
            This has to be fixed in the source.
            • 4. Re: Read XML File on Unix from PL SQL
              Jeet_A
              Hi,

              Thanks Odie.
              Could you please tell me how can i read values for inner Tags . Multiple Values or sort of Child values
              eg.
              Workflow


              <AgileData>
              <AutomatedTransferOrders uniqueId="11761:6243470">
              <CoverPage>
              <AtoType>ATO</AtoType>
              <AtoNumber>ATO00069</AtoNumber>
              <Description>Created by subscriber BDA Program Prototype Change Subscriber.</Description>
              <Status>Released</Status>
              <Workflow>Default ATOs</Workflow>
              <Subscriber>BDA Program Prototype Change Subscriber</Subscriber>
              <DateOriginated>2012-03-15T00:29:10Z</DateOriginated>
              <DateReleased>2012-03-15T00:29:11Z</DateReleased>
              </CoverPage>
              <SelectedObjects referentId="6000:6243458">
              <Type>Program Change Order</Type>
              <NameNumber>PG0000038</NameNumber>
              <Description>Test PIP</Description>
              <LifecycleStatus>Released</LifecycleStatus>
              </SelectedObjects>
              </AutomatedTransferOrders>
              <ChangeOrders uniqueId="6000:6243458">
              <Workflow>
              <StatusCode>Process is moved forward</StatusCode>
              <WorkflowStatus>Pending</WorkflowStatus>
              <Workflow>BDA Program Prototype Workflow</Workflow>
              <StatusChangedBy>Gay Groce (ggroce)</StatusChangedBy>
              <LocalClientTime>2012-03-15T00:29:11Z</LocalClientTime>
              </Workflow>
              <Workflow>
              <StatusCode>Process is skipped</StatusCode>
              <WorkflowStatus>PD Approval</WorkflowStatus>
              <Workflow>BDA Program Prototype Workflow</Workflow>
              <StatusChangedBy>Gay Groce (ggroce)</StatusChangedBy>
              <LocalClientTime>2012-03-15T00:29:11Z</LocalClientTime>
              </Workflow>
              <Workflow>
              <StatusCode>Current Process</StatusCode>
              <WorkflowStatus>Released</WorkflowStatus>
              <Workflow>BDA Program Prototype Workflow</Workflow>
              </Workflow>
              <Workflow><StatusCode>Future Process</StatusCode>
              <WorkflowStatus>Implemented</WorkflowStatus>
              <Workflow>BDA Program Prototype Workflow</Workflow>
              </Workflow>
              </ChangeOrders>
              </AgileData>
              Appreciate your help.

              Thanks a lot,
              Abhi

              Edited by: abhi649769 on Mar 20, 2012 9:21 PM
              • 5. Re: Read XML File on Unix from PL SQL
                AlexAnd
                sample
                Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
                Connected as apps
                 
                SQL> 
                SQL> with t as
                  2   (select xmltype('<AgileData>
                  3    <AutomatedTransferOrders uniqueId="11761:6243470">
                  4      <CoverPage>
                  5        <AtoType>ATO</AtoType>
                  6        <AtoNumber>ATO00069</AtoNumber>
                  7        <Description>Created by subscriber BDA Program Prototype Change Subscriber.</Description>
                  8        <Status>Released</Status>
                  9        <Workflow>Default ATOs</Workflow>
                 10        <Subscriber>BDA Program Prototype Change Subscriber</Subscriber>
                 11        <DateOriginated>2012-03-15T00:29:10Z</DateOriginated>
                 12        <DateReleased>2012-03-15T00:29:11Z</DateReleased>
                 13      </CoverPage>
                 14      <SelectedObjects referentId="6000:6243458">
                 15        <Type>Program Change Order</Type>
                 16        <NameNumber>PG0000038</NameNumber>
                 17        <Description>Test PIP</Description>
                 18        <LifecycleStatus>Released</LifecycleStatus>
                 19      </SelectedObjects>
                 20    </AutomatedTransferOrders>
                 21    <ChangeOrders uniqueId="6000:6243458">
                 22      <Workflow>
                 23        <StatusCode>Process is moved forward</StatusCode>
                 24        <WorkflowStatus>Pending</WorkflowStatus>
                 25        <Workflow>BDA Program Prototype Workflow</Workflow>
                 26        <StatusChangedBy>Gay Groce (ggroce)</StatusChangedBy>
                 27        <LocalClientTime>2012-03-15T00:29:11Z</LocalClientTime>
                 28      </Workflow>
                 29      <Workflow>
                 30        <StatusCode>Process is skipped</StatusCode>
                 31        <WorkflowStatus>PD Approval</WorkflowStatus>
                 32        <Workflow>BDA Program Prototype Workflow</Workflow>
                 33        <StatusChangedBy>Gay Groce (ggroce)</StatusChangedBy>
                 34        <LocalClientTime>2012-03-15T00:29:11Z</LocalClientTime>
                 35      </Workflow>
                 36      <Workflow>
                 37        <StatusCode>Current Process</StatusCode>
                 38        <WorkflowStatus>Released</WorkflowStatus>
                 39        <Workflow>BDA Program Prototype Workflow</Workflow>
                 40      </Workflow>
                 41      <Workflow>
                 42      <StatusCode>Future Process</StatusCode>
                 43        <WorkflowStatus>Implemented</WorkflowStatus>
                 44        <Workflow>BDA Program Prototype Workflow</Workflow>
                 45      </Workflow>
                 46    </ChangeOrders>
                 47  </AgileData>') col
                 48      from dual)
                 49  --
                 50  select x.*
                 51    from t,
                 52         xmltable('AgileData/ChangeOrders/Workflow'
                 53                  passing t.col
                 54                  columns StatusCode varchar2(100) path 'StatusCode',
                 55                          WorkflowStatus varchar2(100) path 'WorkflowStatus',
                 56                          Workflow varchar2(100) path 'Workflow',
                 57                          StatusChangedBy varchar2(100) path 'StatusChangedBy',
                 58                          LocalClientTime varchar2(100) path 'LocalClientTime') x
                 59  /
                 
                STATUSCODE                                                                       WORKFLOWSTATUS                                                                   WORKFLOW                                                                         STATUSCHANGEDBY                                                                  LOCALCLIENTTIME
                -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
                Process is moved forward                                                         Pending                                                                          BDA Program Prototype Workflow                                                   Gay Groce (ggroce)                                                               2012-03-15T00:29:11Z
                Process is skipped                                                               PD Approval                                                                      BDA Program Prototype Workflow                                                   Gay Groce (ggroce)                                                               2012-03-15T00:29:11Z
                Current Process                                                                  Released                                                                         BDA Program Prototype Workflow                                                                                                                                    
                Future Process                                                                   Implemented                                                                      BDA Program Prototype Workflow                                                                                                                                    
                 
                SQL> 
                • 6. Re: Read XML File on Unix from PL SQL
                  Jeet_A
                  Hi,

                  Thanks Alex. Appreciate your help.

                  My question is how can we pull child columns along with reference to parent. How can we see parent reference or parent primary_id.

                  eg. what i need to do if i want to pull full information
                  AutomatedTransferOrders uniqueId
                  /AutomatedTransferOrders/AtoType
                  /AutomatedTransferOrders/CoverPage/SelectedObjects referentId
                  Description
                  ChangeOrders uniqueId (For 6000:6243458 )
                  /ChangeOrders/Workflow/StatusCode
                  /ChangeOrders/Workflow/WorkflowStatus
                  ChangeOrders uniqueId (For 6000:6243459 )
                  /ChangeOrders/Workflow/StatusCode
                  /ChangeOrders/Workflow/WorkflowStatus



                  <AgileData>
                  <AutomatedTransferOrders uniqueId="11761:6243470">
                  <CoverPage>
                  <AtoType>ATO</AtoType>
                  <Workflow>Default ATOs</Workflow>
                  <DateReleased>2012-03-15T00:29:11Z</DateReleased>
                  </CoverPage>
                  <SelectedObjects referentId="6000:6243458">
                  <Type>Program Change Order</Type>
                  <NameNumber>PG0000038</NameNumber>
                  <Description>Test PIP</Description>
                  <LifecycleStatus>Released</LifecycleStatus>
                  </SelectedObjects>
                  </AutomatedTransferOrders>
                  <ChangeOrders uniqueId="6000:6243458">
                  <Workflow>
                  <StatusCode>Process is moved forward</StatusCode>
                  <WorkflowStatus>Pending</WorkflowStatus>
                  </Workflow>
                  <Workflow>
                  <StatusCode>Process is skipped</StatusCode>
                  <WorkflowStatus>PD Approval</WorkflowStatus>
                  </Workflow>
                  </ChangeOrders>
                  <ChangeOrders uniqueId="6000:6243459">
                  <Workflow>
                  <StatusCode>Process is moved forward</StatusCode>
                  <WorkflowStatus>Pending</WorkflowStatus>
                  </Workflow>
                  <Workflow>
                  <StatusCode>Process is skipped</StatusCode>
                  <WorkflowStatus>PD Approval</WorkflowStatus>
                  </Workflow>
                  </ChangeOrders>
                  </AgileData>



                  Thanks,
                  Abhi
                  • 7. Re: Read XML File on Unix from PL SQL
                    AlexAnd
                    you can use several xmltable, for example - Re: How to extract data in a given format

                    plz post sample output
                    plz format your code by forum's tags
                    • 8. Re: Read XML File on Unix from PL SQL
                      Jeet_A
                      Hi,


                      Format would be
                      There would be one uniqueId and referentId and two ChangeOrders uniqueId and each Change Orders has 2 Workflow Status .

                      uniqueId     AtoType referentId(SelectedObjects)     Description     ChangeOrders uniqueId      StatusCode          WorkflowStatus     
                      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      11761:6243470 ATO 6000:6243458 Test PIP 6000:6243458          Process is moved forward     Pending
                      11761:6243470 ATO 6000:6243458 Test PIP 6000:6243458          Process is skipped          PD Approval
                      11761:6243470 ATO 6000:6243458 Test PIP 6000:6243459          Process is moved forward     Pending
                      11761:6243470 ATO 6000:6243458 Test PIP 6000:6243459          Process is skipped          PD Approval




                      or same format Pipe saperated
                      uniqueId|AtoType|referentId|Description|ChangeOrders|uniqueId|StatusCode|WorkflowStatus     
                      ------------------------------------------------------------------------------------------------------------------------------------------------
                      11761:6243470|ATO|6000:6243458|Test PIP|6000:6243458|Process is moved forward|Pending
                      11761:6243470|ATO|6000:6243458|Test PIP|6000:6243458|Process is skipped|PD Approval
                      11761:6243470|ATO|6000:6243458|Test PIP|6000:6243459|Process is moved forward|Pending
                      11761:6243470|ATO|6000:6243458|Test PIP|6000:6243459|Process is skipped|PD Approval

                      Thanks,
                      Abhi

                      Edited by: abhi649769 on Mar 21, 2012 2:19 PM
                      • 9. Re: Read XML File on Unix from PL SQL
                        odie_63
                        Try to understand how it works so that you can do it yourself next time :)
                        SQL> create table tmp_xml of xmltype;
                         
                        Table created
                         
                        SQL> insert into tmp_xml values(
                          2   xmltype(bfilename('TEST_DIR','test.xml'), nls_charset_id('AL32UTF8'))
                          3  );
                         
                        1 row inserted
                         
                        SQL> commit;
                         
                        Commit complete
                         
                        SQL> select h.uniqueId
                          2       , h.AtoType
                          3       , h.referentId
                          4       , h.Description
                          5       , co.uniqueId
                          6       , w.StatusCode
                          7       , w.WorkflowStatus
                          8  from tmp_xml t
                          9     , xmltable('/AgileData' passing t.object_value
                         10         columns uniqueId     varchar2(30) path 'AutomatedTransferOrders/@uniqueId'
                         11               , AtoType      varchar2(30) path 'AutomatedTransferOrders/CoverPage/AtoType'
                         12               , referentId   varchar2(30) path 'AutomatedTransferOrders/SelectedObjects/@referentId'
                         13               , Description  varchar2(80) path 'AutomatedTransferOrders/SelectedObjects/Description'
                         14               , ChangeOrders xmltype      path 'ChangeOrders'
                         15       ) h
                         16     , xmltable('/ChangeOrders' passing h.ChangeOrders
                         17         columns uniqueId     varchar2(30) path '@uniqueId'
                         18               , Workflows    xmltype      path 'Workflow'
                         19       ) co
                         20     , xmltable('/Workflow' passing co.Workflows
                         21         columns StatusCode     varchar2(80) path 'StatusCode'
                         22               , WorkflowStatus varchar2(30) path 'WorkflowStatus'
                         23       ) w
                         24  ;
                         
                        UNIQUEID            ATOTYPE     REFERENTID       DESCRIPTION     UNIQUEID         STATUSCODE                   WORKFLOWSTATUS
                        ------------------- ----------- ---------------- --------------- ---------------- ---------------------------- ------------------
                        11761:6243470       ATO         6000:6243458     Test PIP        6000:6243458     Process is moved forward     Pending
                        11761:6243470       ATO         6000:6243458     Test PIP        6000:6243458     Process is skipped           PD Approval
                        11761:6243470       ATO         6000:6243458     Test PIP        6000:6243459     Process is moved forward     Pending
                        11761:6243470       ATO         6000:6243458     Test PIP        6000:6243459     Process is skipped           PD Approval