12 Replies Latest reply: Jun 26, 2012 5:15 AM by Sachu RSS

    Help to upload the below xml to oracle table

    Sachu
      Dear All,

      I tried by best to upload the below xml to oracle table but giving link between the tables is very difficult for me. can anybody help me to import the below XML to oracle table

      <?xml version="1.0" encoding="utf-8"?>
      <Claim.Submission xmlns:tns="http://www.haad.ae/DataDictionary/CommonTypes" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.haad.ae/DataDictionary/CommonTypes/ClaimSubmission.xsd">
      <Header>
      <SenderID>MF65</SenderID>
      <ReceiverID>C014</ReceiverID>
      <TransactionDate>12/03/2012 10:40</TransactionDate>
      <RecordCount>1</RecordCount>
      <DispositionFlag>PRODUCTION</DispositionFlag>
      </Header>
      <Claim>
      <ID>23112</ID>
      <MemberID>100000874</MemberID>
      <PayerID>A022</PayerID>
      <ProviderID>MF65</ProviderID>
      <EmiratesIDNumber>111-1111-1111111-1</EmiratesIDNumber>
      <Gross>115</Gross>
      <PatientShare>20</PatientShare>
      <Net>95</Net>
      <Encounter>
      <FacilityID>MF65</FacilityID>
      <Type>1</Type>
      <PatientID>47685</PatientID>
      <Start>11/02/2012 12:00</Start>
      </Encounter>
      <Diagnosis>
      <Type>Principal</Type>
      <Code>461.9</Code>
      </Diagnosis>
      <Diagnosis>
      <Type>Secondary</Type>
      <Code>462</Code>
      </Diagnosis>
      <Activity>
      <ID>23112_1</ID>
      <Start>11/02/2012 12:00</Start>
      <Type>3</Type>
      <Code>99202</Code>
      <Quantity>1</Quantity>
      <Net>95</Net>
      <Clinician>D1310</Clinician>
      </Activity>
      </Claim>
      </Claim.Submission>
        • 1. Re: Help to upload the below xml to oracle table
          odie_63
          Hi,

          Please provide more information :

          1) database version (select * from v$version)
          2) where does the XML come from : external file, table column (datatype?), ... ?
          3) give the structure (DDL) of your target tables and explain which XML element goes to which column

          Thanks.
          • 2. Re: Help to upload the below xml to oracle table
            Sachu
            Hi,

            Thanks for the reply..here is the info

            Version 11 g 11.2.0.1.0
            XML is downloaded to my local drive
            target table has the same ddl as in XML
            eg:
            senderid,receiverid,........clinician.

            the issue im facing is. Header is the root node, under Header there can be multiple 'Claim'. Under one claim there will be one 'Encounter' under 'Encounter' there can be multiple 'Diagnosis' and again under 'Claim' there can be multiple 'Activity'. But in XML there is no field to link between these nodes..So its is difficult to link between this while uploading to a table :(

            Regards
            Hisham.A
            • 3. Re: Help to upload the below xml to oracle table
              odie_63
              Hi Hisham,

              Thanks for the details.
              under 'Encounter' there can be multiple 'Diagnosis'
              That's not the case in the sample XML you gave. Could you clarify?

              Also, in the sample XML, Activity and Diagnosis are siblings : it's not possible to store them in the same table if they occur multiple times.
              Repeating nested elements have to be stored in separate tables.

              Edited by: odie_63 on 12 juin 2012 10:38
              • 4. Re: Help to upload the below xml to oracle table
                Sachu
                Hi Odie,

                Thanks for the reply.

                You are correct we need multiple tables.

                My architecture is like this

                1 table for -Header
                1 table for -Claim
                1 table for - Encounter
                1 table for - Diagnosis
                1 table for - Activity

                First i will retrieve all the values in to a table having all the fields. I am able to do that. using the below code

                with t0 as (
                SELECT SenderID,
                ReceiverID,
                DispositionFlag,
                TransactionDate,
                RecordCount
                FROM
                haad_xml_file xf,
                xmltable(xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi"),
                'Claim.Submission' passing xf.filecontent
                columns SenderID varchar2(100) path 'Header/SenderID',
                ReceiverID varchar2(100) path 'Header/ReceiverID',
                DispositionFlag varchar2(100) path 'Header/DispositionFlag',
                TransactionDate varchar2(100) path 'Header/TransactionDate',
                RecordCount NUMBER path 'Header/RecordCount'

                ) Header),
                t1 as (


                SELECT
                c.ID,
                c.MemberID,
                c.PayerID,
                c.EmiratesIDNumber,
                c.Gross,
                c.PatientShare,
                c.Net,
                ec."Type",
                ec.PatientID,
                ec."Start",
                ec.StartType,
                ec."End",
                ec.EndType,
                dg."D_Type",
                dg.code,
                ac.ac_id,
                ac.ac_start,
                ac.ac_type,
                ac.ac_code,
                ac.ac_quantity,
                ac.ac_net,
                ac.ac_clinician,
                ac.ac_PriorAuthorizationID
                FROM
                haad_xml_file xf,
                xmltable(xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi"),
                'Claim.Submission/Claim' passing xf.filecontent
                columns
                claim_no for ordinality,
                ID varchar2(100) path 'ID',
                MemberID varchar2(100) path 'MemberID',
                PayerID varchar2(100) path 'PayerID',
                EmiratesIDNumber varchar2(100) path 'EmiratesIDNumber',
                Gross NUMBER(16,2) path 'Gross',
                PatientShare NUMBER(16,2) path 'PatientShare',
                Net NUMBER(16,2) path 'Net'

                ) c,
                xmltable('$d/Claim.Submission/Claim[$cn]/Encounter'
                passing xf.filecontent as "d",
                c.claim_no as "cn"
                columns
                "Type" varchar2(100) path 'Type',
                PatientID varchar2(100) path 'PatientID',
                "Start" varchar2(100) path 'Start',
                StartType Number path 'StartType',
                "End" Varchar2(100) path 'End',
                EndType Number path 'EndType'


                ) ec,
                xmltable('$d/Claim.Submission/Claim[$cn]/Diagnosis'
                passing xf.filecontent as "d",
                c.claim_no as "cn"
                columns
                "D_Type" varchar2(100) path 'Type',
                code varchar2(100) path 'Code'


                ) dg,
                xmltable('$d/Claim.Submission/Claim[$cn]/Activity'
                passing xf.filecontent as "d",
                c.claim_no as "cn"
                columns activity_no for ordinality,
                ac_id varchar2(100) path 'ID',
                ac_start varchar2(100) path 'Start',
                ac_type varchar2(100) path 'Type',
                ac_code varchar2(100) path 'Code',
                ac_quantity varchar2(100) path 'Quantity',
                ac_net varchar2(100) path 'Net',
                ac_clinician varchar2(100) path 'Clinician',
                ac_PriorAuthorizationID varchar2(100) path 'PriorAuthorizationID'



                ) ac

                )
                select distinct * from t0,t1 order by t1.id;

                Then i will validate and will store in to seperate tables (This part i didnt done now)

                Also XML Hierarchy is like this

                1. Header - Under header multiple claim can be there
                2.Claim - Under Claim following will come (Under 1 Claim there will be 1 Encounter and can be multiple diagnosis or multiple acivity)
                a.Encounter
                b.Claim
                c.Activity - Under activity there can be multiple Observations
                . Observations

                The problem I am facing is eg: consider my XML contain 2 Claims and under 1 Claim node there is no activity.
                Then my above code will retrieve only the node details that having activity :( the node dont have activity will be neglected. Can you help me on this?



                The problem now I am facing is
                • 5. Re: Help to upload the below xml to oracle table
                  odie_63
                  OK, you're on the right track :)

                  Are you working with this set of schemas : http://eclaimlink.ae/dhd_schemas.aspx ?
                  Then my above code will retrieve only the node details that having activity the node dont have activity will be neglected.
                  You have to use an outer join :
                         xmltable('$d/Claim.Submission/Claim[$cn]/Activity' 
                                                   passing xf.filecontent as "d",
                                                   c.claim_no as "cn"
                                    columns  activity_no for ordinality,
                                           ac_id varchar2(100) path 'ID',
                                           ac_start varchar2(100) path 'Start',
                                           ac_type varchar2(100) path 'Type',
                                           ac_code varchar2(100) path 'Code',
                                           ac_quantity varchar2(100) path 'Quantity',
                                           ac_net varchar2(100) path 'Net',
                                           ac_clinician varchar2(100) path 'Clinician',
                                           ac_PriorAuthorizationID varchar2(100) path 'PriorAuthorizationID'               
                                ) (+) ac
                  A few more comments :

                  - for performance, make sure you use binary xml storage :
                  create table haad_xml_file (
                    filecontent xmltype
                  )
                  xmltype column filecontent store as securefile binary xml
                  ;
                  - do not use positional predicates (the [$cn] part), it's slow :
                  $d/Claim.Submission/Claim[$cn]/Encounter
                  Instead, extract repeating elements as xmltype and pass the column to the next XMLTable, like this :
                  SELECT h.SenderID,
                         h.ReceiverID,
                         h.DispositionFlag,
                         h.TransactionDate,
                         h.RecordCount,
                         c.ID,
                         c.MemberID,
                         c.PayerID,
                         c.EmiratesIDNumber,
                         c.Gross,
                         c.PatientShare,
                         c.Net,
                         ec."Type",
                         ec.PatientID,
                         ec."Start",
                         ec.StartType,
                         ec."End",
                         ec.EndType,
                         dg."D_Type",
                         dg.code,
                         ac.ac_id,
                         ac.ac_start,
                         ac.ac_type,
                         ac.ac_code,
                         ac.ac_quantity,
                         ac.ac_net,
                         ac.ac_clinician,
                         ac.ac_PriorAuthorizationID
                  FROM haad_xml_file xf 
                     , xmltable('/Claim.Submission' 
                                passing xf.filecontent 
                                columns SenderID        varchar2(100) path 'Header/SenderID'
                                      , ReceiverID      varchar2(100) path 'Header/ReceiverID'
                                      , DispositionFlag varchar2(100) path 'Header/DispositionFlag'
                                      , TransactionDate varchar2(100) path 'Header/TransactionDate'
                                      , RecordCount     NUMBER        path 'Header/RecordCount'
                                      , Claims          xmltype       path 'Claim'
                                ) h
                     , xmltable('/Claim' 
                                passing h.Claims
                                columns claim_no         for ordinality
                                      , ID               varchar2(100) path 'ID'
                                      , MemberID         varchar2(100) path 'MemberID'
                                      , PayerID          varchar2(100) path 'PayerID'
                                      , EmiratesIDNumber varchar2(100) path 'EmiratesIDNumber'
                                      , Gross            NUMBER(16,2)  path 'Gross'
                                      , PatientShare     NUMBER(16,2)  path 'PatientShare'
                                      , Net              NUMBER(16,2)  path 'Net'
                                      , Encounters       xmltype       path 'Encounter'
                                      , Diagnoses        xmltype       path 'Diagnosis'
                                      , Activities       xmltype       path 'Activity'
                                ) c
                     , xmltable('/Encounter' 
                                passing c.Encounters
                                columns "Type"    varchar2(100) path 'Type'
                                      , PatientID varchar2(100) path 'PatientID'
                                      , "Start"   varchar2(100) path 'Start'
                                      , StartType Number        path 'StartType'
                                      , "End"     Varchar2(100) path 'End'
                                      , EndType   Number        path 'EndType'
                                ) ec
                     , xmltable('/Diagnosis' 
                                passing c.Diagnoses
                                columns "D_Type" varchar2(100) path 'Type'
                                      , code     varchar2(100) path 'Code'           
                                ) dg
                     , xmltable('/Activity' 
                                passing c.Activities
                                columns activity_no for ordinality
                                      , ac_id        varchar2(100) path 'ID'
                                      , ac_start     varchar2(100) path 'Start'
                                      , ac_type      varchar2(100) path 'Type'
                                      , ac_code      varchar2(100) path 'Code'
                                      , ac_quantity  varchar2(100) path 'Quantity'
                                      , ac_net       varchar2(100) path 'Net'
                                      , ac_clinician varchar2(100) path 'Clinician'
                                      , ac_PriorAuthorizationID varchar2(100) path 'PriorAuthorizationID'               
                                ) (+) ac
                  ;
                  - Have you considered using Object-Relational storage for this?
                  Since you have the schemas, you could register them in the database, that will automatically create the appropriate storage structure for your XML documents as well as validate them at insert time.
                  You could then build individual views to query the different nested parts of the document and ultimately insert the data in final relational tables.
                  • 6. Re: Help to upload the below xml to oracle table
                    Sachu
                    Hi Odie,

                    Thanks a lot..its working for my examples..will get back to u if it got stucked for some others
                    on the same time can u pls tell how can i achieve the below . Since I am new to doing this XML i didnt understand this. Also can u pls give help in modifying the code given below
                    with one more nod 'Observation'. I modified ur code but it is giving error for me. Please have a look..Your help on this will be highly appreciated.

                    **"Have you considered using Object-Relational storage for this?**
                    **Since you have the schemas, you could register them in the database, that will automatically create the appropriate storage structure for your XML documents as well as validate them at insert time.**
                    **You could then build individual views to query the different nested parts of the document and ultimately insert the data in final relational tables."**

                    SELECT h.SenderID,
                    h.ReceiverID,
                    h.DispositionFlag,
                    h.TransactionDate,
                    h.RecordCount,
                    c.ID,
                    c.MemberID,
                    c.PayerID,
                    c.EmiratesIDNumber,
                    c.Gross,
                    c.PatientShare,
                    c.Net,
                    ec."Type",
                    ec.PatientID,
                    ec."Start",
                    ec.StartType,
                    ec."End",
                    ec.EndType,
                    dg."D_Type",
                    dg.code,
                    ac.ac_id,
                    ac.ac_start,
                    ac.ac_type,
                    ac.ac_code,
                    ac.ac_quantity,
                    ac.ac_net,
                    ac.ac_clinician,
                    ac.ac_PriorAuthorizationID
                    FROM haad_xml_file xf
                    , xmltable('/Claim.Submission'
                    passing xf.filecontent
                    columns SenderID varchar2(100) path 'Header/SenderID'
                    , ReceiverID varchar2(100) path 'Header/ReceiverID'
                    , DispositionFlag varchar2(100) path 'Header/DispositionFlag'
                    , TransactionDate varchar2(100) path 'Header/TransactionDate'
                    , RecordCount NUMBER path 'Header/RecordCount'
                    , Claims xmltype path 'Claim'
                    ) h
                    , xmltable('/Claim'
                    passing h.Claims
                    columns claim_no for ordinality
                    , ID varchar2(100) path 'ID'
                    , MemberID varchar2(100) path 'MemberID'
                    , PayerID varchar2(100) path 'PayerID'
                    , EmiratesIDNumber varchar2(100) path 'EmiratesIDNumber'
                    , Gross NUMBER(16,2) path 'Gross'
                    , PatientShare NUMBER(16,2) path 'PatientShare'
                    , Net NUMBER(16,2) path 'Net'
                    , Encounters xmltype path 'Encounter'
                    , Diagnoses xmltype path 'Diagnosis'
                    , Activities xmltype path 'Activity'
                    , Observation xmltype path 'Activity/Observation'
                    ) c
                    , xmltable('/Encounter'
                    passing c.Encounters
                    columns "Type" varchar2(100) path 'Type'
                    , PatientID varchar2(100) path 'PatientID'
                    , "Start" varchar2(100) path 'Start'
                    , StartType Number path 'StartType'
                    , "End" Varchar2(100) path 'End'
                    , EndType Number path 'EndType'
                    ) ec
                    , xmltable('/Diagnosis'
                    passing c.Diagnoses
                    columns "D_Type" varchar2(100) path 'Type'
                    , code varchar2(100) path 'Code'
                    ) dg
                    , xmltable('/Activity'
                    passing c.Activities
                    columns activity_no for ordinality
                    , ac_id varchar2(100) path 'ID'
                    , ac_start varchar2(100) path 'Start'
                    , ac_type varchar2(100) path 'Type'
                    , ac_code varchar2(100) path 'Code'
                    , ac_quantity varchar2(100) path 'Quantity'
                    , ac_net varchar2(100) path 'Net'
                    , ac_clinician varchar2(100) path 'Clinician'
                    , ac_PriorAuthorizationID varchar2(100) path 'PriorAuthorizationID'
                    ) (+) ac
                    ,xmltable('Activity/Observation'
                    passing c.Observation
                    columns Observation_no for ordinality
                    ,o_type varchar2(100) path 'Type',
                    o_code varchar2(100) path 'Code',
                    o_value varchar2(100) path 'Value',
                    o_valuetype varchar2(100) path 'ValueType')(+)o
                    ;
                    • 7. Re: Help to upload the below xml to oracle table
                      Sachu
                      Hi Odie,

                      Go it :)

                      SELECT h.SenderID,
                      h.ReceiverID,
                      h.DispositionFlag,
                      h.TransactionDate,
                      h.RecordCount,
                      c.ID,
                      c.MemberID,
                      c.PayerID,
                      c.EmiratesIDNumber,
                      c.Gross,
                      c.PatientShare,
                      c.Net,
                      ec."Type",
                      ec.PatientID,
                      ec."Start",
                      ec.StartType,
                      ec."End",
                      ec.EndType,
                      dg."D_Type",
                      dg.code,
                      ac.ac_id,
                      ac.ac_start,
                      ac.ac_type,
                      ac.ac_code,
                      ac.ac_quantity,
                      ac.ac_net,
                      ac.ac_clinician,
                      ac.ac_PriorAuthorizationID,
                      o.o_type,
                      o.o_code,
                      o.o_value,
                      o.o_valuetype
                      FROM haad_xml_file xf
                      , xmltable('/Claim.Submission'
                      passing xf.filecontent
                      columns SenderID varchar2(100) path 'Header/SenderID'
                      , ReceiverID varchar2(100) path 'Header/ReceiverID'
                      , DispositionFlag varchar2(100) path 'Header/DispositionFlag'
                      , TransactionDate varchar2(100) path 'Header/TransactionDate'
                      , RecordCount NUMBER path 'Header/RecordCount'
                      , Claims xmltype path 'Claim'
                      ) h
                      , xmltable('/Claim'
                      passing h.Claims
                      columns claim_no for ordinality
                      , ID varchar2(100) path 'ID'
                      , MemberID varchar2(100) path 'MemberID'
                      , PayerID varchar2(100) path 'PayerID'
                      , EmiratesIDNumber varchar2(100) path 'EmiratesIDNumber'
                      , Gross NUMBER(16,2) path 'Gross'
                      , PatientShare NUMBER(16,2) path 'PatientShare'
                      , Net NUMBER(16,2) path 'Net'
                      , Encounters xmltype path 'Encounter'
                      , Diagnoses xmltype path 'Diagnosis'
                      , Activities xmltype path 'Activity'
                      --, Observation xmltype path 'Observation'
                      ) c
                      , xmltable('/Encounter'
                      passing c.Encounters
                      columns "Type" varchar2(100) path 'Type'
                      , PatientID varchar2(100) path 'PatientID'
                      , "Start" varchar2(100) path 'Start'
                      , StartType Number path 'StartType'
                      , "End" Varchar2(100) path 'End'
                      , EndType Number path 'EndType'
                      ) ec
                      , xmltable('/Diagnosis'
                      passing c.Diagnoses
                      columns "D_Type" varchar2(100) path 'Type'
                      , code varchar2(100) path 'Code'
                      ) dg
                      , xmltable('/Activity'
                      passing c.Activities
                      columns activity_no for ordinality
                      , ac_id varchar2(100) path 'ID'
                      , ac_start varchar2(100) path 'Start'
                      , ac_type varchar2(100) path 'Type'
                      , ac_code varchar2(100) path 'Code'
                      , ac_quantity varchar2(100) path 'Quantity'
                      , ac_net varchar2(100) path 'Net'
                      , ac_clinician varchar2(100) path 'Clinician'
                      , ac_PriorAuthorizationID varchar2(100) path 'PriorAuthorizationID'
                      ,Observation xmltype path 'Observation'
                      ) (+) ac
                      ,xmltable('Observation'
                      passing ac.Observation
                      columns Observation_no for ordinality
                      ,o_type varchar2(100) path 'Type',
                      o_code varchar2(100) path 'Code',
                      o_value varchar2(100) path 'Value',
                      o_valuetype varchar2(100) path 'ValueType')(+)o
                      ;


                      Will disturb you if I got stuck some where..I am new to XML so definitely u can expect my doubts :)

                      Also pls tell me how can I achieve

                      Have you considered using Object-Relational storage for this?
                      Since you have the schemas, you could register them in the database, that will automatically create the appropriate storage structure for your XML documents as well as validate them at insert time.
                      You could then build individual views to query the different nested parts of the document and ultimately insert the data in final relational tables.
                      • 8. Re: Help to upload the below xml to oracle table
                        odie_63
                        Also pls tell me how can I achieve

                        Have you considered using Object-Relational storage for this?
                        Since you have the schemas, you could register them in the database, that will automatically create the appropriate storage structure for your XML documents as well as validate them at insert time.
                        You could then build individual views to query the different nested parts of the document and ultimately insert the data in final relational tables.
                        Please see the documentation for an introduction to the concepts :
                        http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/partpg2.htm#g997354

                        You'll find practical examples on the {forum:id=34} forum, and its FAQ : {thread:id=410714}
                        • 9. Re: Help to upload the below xml to oracle table
                          Sachu
                          Hi Odie,

                          Thanks a lot.. I will check and will do the practicals..In case of any issue i will post my queries..

                          I am marking this as a correct answer :-)
                          • 10. Re: Help to upload the below xml to oracle table
                            Sachu
                            Hi Odie,

                            Now I got stucked in another situation. Now the XML content is tored in Oracle as BLOB so my current procedure wich mentioned below can't extract XML values from BLOB. SO I need to convert BLOB to CLOB first and extract to XML TYpe. Can you pls help me how to do it?

                            Regards
                            Hisham.A
                            • 11. Re: Help to upload the below xml to oracle table
                              odie_63
                              Now the XML content is tored in Oracle as BLOB so my current procedure wich mentioned below can't extract XML values from BLOB.
                              That's clearly a step backwards.
                              Why use BLOB instead of XMLType which is the native and performant datatype to manage XML in Oracle?
                              SO I need to convert BLOB to CLOB first and extract to XML TYpe.
                              You don't need to convert to an intermediate CLOB, the XMLType object has a constructor working with BLOB directly :

                              Assuming the encoding is UTF-8 :
                              xmltype(your_blob_col, nls_charset_id('AL32UTF8'))
                              • 12. Re: Help to upload the below xml to oracle table
                                Sachu
                                Dear Odie,

                                You are the man. Its working fine :) I am using BLOB because we are creating an application through IPEX for uploading the XML files. In that only BLOB type is specified.

                                Thanks