Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2012
    Posts
    37

    Unanswered: SQL Loader to read XML file

    Hi
    I am trying to develop the sql loader which should study the below XML

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <AuthEpisodeSisMsgXs xmlns:ns4="sis_message" xmlns="auth">
        <ns4:sis_msg_metadata>
            <ns4:INTERFACE_NAME>port</ns4:INTERFACE_NAME>
            <ns4:DATA_SOURCE>System</ns4:DATA_SOURCE>
            <ns4:DATA_DEST>CHECK</ns4:DATA_DEST>
            <ns4:BATCH_ID>12</ns4:BATCH_ID>
            <ns4:CRT_DT>2013-12-06T13:20:00-05:00</ns4:CRT_DT>
            <ns4:USER_ID>User</ns4:USER_ID>
            <ns4:RECORD_COUNT>54</ns4:RECORD_COUNT>
        </ns4:sis_msg_metadata>
        <sis_msg_content>
            <ns0:member_lookup xmlns:ns0="member">
                <ns0:MEMBER_ID_TYPE_CD>ELIG</ns0:MEMBER_ID_TYPE_CD>
                <ns0:MEMBER_ID>XXXXXXXXX</ns0:MEMBER_ID>
            </ns0:member_lookup>
            <ns0:episode_coverage xmlns:ns0="episode">
                <ns0:SUBSCRIBER_ID/>
                <ns0:DEPENDENT_CD>01</ns0:DEPENDENT_CD>
                <ns0:GROUP_ID>000000000</ns0:GROUP_ID>
                <ns0:EFFECTIVE_DT>2007-09-01T00:00:00+00:00</ns0:EFFECTIVE_DT>
            </ns0:episode_coverage>
            <ns0:episode_common xmlns:ns0="episode">
                <ns0:CERT_AUTH_NO>HHHHH</ns0:CERT_AUTH_NO>
                <ns0:EXT_EPISODE_ID/>
                <ns0:EPISODE_TYPE_CD>OP</ns0:EPISODE_TYPE_CD>
                <ns0:REQUEST_RECEIVED_DT>2008-10-20T00:00:00+00:00</ns0:REQUEST_RECEIVED_DT>
                <ns0:REFERRAL_SOURCE_CD>ere</ns0:REFERRAL_SOURCE_CD>
                <ns0:EPISODE_START_DT>2008-06-04T00:00:00+00:00</ns0:EPISODE_START_DT>
                <ns0:EPISODE_STATUS>OTHER</ns0:EPISODE_STATUS>
                <ns0:STATUS_REASON_CD/>
                <ns0:ASSIGNED_USER/>
                <ns0:REVIEW_TYPE_CD>NONE</ns0:REVIEW_TYPE_CD>
                <ns0:URGENCY_TYPE_CD>Standard</ns0:URGENCY_TYPE_CD>
            </ns0:episode_common>
            <ns0:diagnoses xmlns:ns0="episode">
                <ns0:diagnosis>
                    <ns0:DIAGNOSIS_TYPE_CD>I</ns0:DIAGNOSIS_TYPE_CD>
                    <ns0:DIAGNOSIS_CD>84500</ns0:DIAGNOSIS_CD>
                    <ns0:DIAGNOSIS_DT>1999-05-31T00:00:00+00:00</ns0:DIAGNOSIS_DT>
                    <ns0:PRIMARY_FLAG>true</ns0:PRIMARY_FLAG>
                </ns0:diagnosis>
            </ns0:diagnoses>
            <ns0:providers xmlns:ns0="episode">
                <ns0:provider>
                    <ns0:PROVIDER_ID>00000</ns0:PROVIDER_ID>
                    <ns0:PROVIDER_ROLE_CD>REQUEST</ns0:PROVIDER_ROLE_CD>
                    <ns0:SERVICE_LOCATION_ID>PT</ns0:SERVICE_LOCATION_ID>
                    <ns0:IN_NETWORK>true</ns0:IN_NETWORK>
                </ns0:provider>
                <ns0:provider>
                    <ns0:PROVIDER_ID>00000</ns0:PROVIDER_ID>
                    <ns0:PROVIDER_ROLE_CD>SERVICE</ns0:PROVIDER_ROLE_CD>
                    <ns0:SERVICE_LOCATION_ID>PT</ns0:SERVICE_LOCATION_ID>
                    <ns0:IN_NETWORK>true</ns0:IN_NETWORK>
                </ns0:provider>
            </ns0:providers>
            <ns0:notes xmlns:ns0="episode">
                <ns0:note>
                    <ns0:NOTE_TYPE_CD>INFORMATION</ns0:NOTE_TYPE_CD>
                    <ns0:NOTE_TEXT>PE</ns0:NOTE_TEXT>
                </ns0:note>
                <ns0:note>
                    <ns0:NOTE_TYPE_CD>ARE</ns0:NOTE_TYPE_CD>
                     <ns0:NOTE_TEXT>RRRRRR</ns0:NOTE_TEXT>
                </ns0:note>
            </ns0:notes>
            <ns0:notifications xmlns:ns0="episode">
                <ns0:notification>
                    <ns0:NOTIFICATION_TITLE>(COMM)</ns0:NOTIFICATION_TITLE>
                    <ns0:NOTIFICATION_DT>1999-05-31T00:00:00+00:00</ns0:NOTIFICATION_DT>
                </ns0:notification>
            </ns0:notifications>
            <auth_stay>
                <SERVICE_TYPE_CD>AE</SERVICE_TYPE_CD>
                <ADMIT_DT>2008-06-04T00:00:00+00:00</ADMIT_DT>
                <DISCHARGE_DT>1999-05-31T00:00:00+00:00</DISCHARGE_DT>
                <DISCHARGE_DISP_CD/>
                <APPROVED_LEVEL_OF_CARE_CD> </APPROVED_LEVEL_OF_CARE_CD>
                <PLACE_OF_SERVICE_CD>PT</PLACE_OF_SERVICE_CD>
                <REQUESTED_DAYS>0</REQUESTED_DAYS>
                <ASSIGNED_DAYS>0</ASSIGNED_DAYS>
                <DECISION_CD>OPDEN</DECISION_CD>
                <DECISION_REASON_CD>OPDEN-AD</DECISION_REASON_CD>
                <ASSIGNED_FROM_DT>2008-06-04T00:00:00+00:00</ASSIGNED_FROM_DT>
                <ASSIGNED_TO_DT>2008-06-04T00:00:00+00:00</ASSIGNED_TO_DT>
                <NOTE/>
            </auth_stay>
            <auth_services>
                <auth_service>
                    <SERVICE_TYPE_CD>SAE</SERVICE_TYPE_CD>
                    <PROCEDURE_TYPE_CD>CT</PROCEDURE_TYPE_CD>
                    <PROCEDURE_CD>-</PROCEDURE_CD>
                    <PROCEDURE_MODIFIER_CD/>
                    <PLACE_OF_SERVICE_CD>PT</PLACE_OF_SERVICE_CD>
                    <REQUESTED_UNITS>0</REQUESTED_UNITS>
                    <ASSIGNED_UNITS>0</ASSIGNED_UNITS>
                    <DECISION_CD>OPDEN</DECISION_CD>
                    <DECISION_REASON_CD>OPDEN-AD</DECISION_REASON_CD>
                    <ASSIGNED_FROM_DT>2008-06-04T00:00:00+00:00</ASSIGNED_FROM_DT>
                    <ASSIGNED_TO_DT>2008-06-04T00:00:00+00:00</ASSIGNED_TO_DT>
                    <SEQUENCE_NO>1</SEQUENCE_NO>
                    <NEGOTIATED_RATE>1.1</NEGOTIATED_RATE>
                    <NOTE/>
                    <FREQUENCY/>
                    <REQUESTED_FROM_DT>2008-06-04T00:00:00+00:00</REQUESTED_FROM_DT>
                    <REQUESTED_TO_DT>2008-06-04T00:00:00+00:00</REQUESTED_TO_DT>
                </auth_service>
            </auth_services>
        </sis_msg_content>
        <sis_msg_content>

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what table, columns, & values should reside in the DB after SQL*Loader runs?

    post complete results from SQL below

    SELECT * FROM V$VERSION;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    37
    The table name is temp_data
    The Columns names are
    Code:
    requestcategory
    authstatus
    memberid
    authnumber
    ediservicetype
    rqstprvid
    hphcserviceprvid
    secondsvcprdid
    decisionstartdt
    decisionenddt
    arcd
    ascd
    initrequestdate
    dischargedflag
    urgency_type_cd
    requested_days
    assigned_days
    request_start_date
    request_end_date
    member_class
    member_cov_eff_dt
    group_id
    diagcd1
    diagcd2
    diagcd3
    diagcd4
    diagcd5
    diagcd6
    diagcd7
    diagcd8
    diagcd9
    diagcd10
    diagcd11
    diagcd12
    diag_type_cd1
    diag_type_cd2
    diag_type_cd3
    diag_type_cd4
    diag_type_cd5
    diag_type_cd6
    diag_type_cd7
    diag_type_cd8
    diag_type_cd9
    diag_type_cd10
    diag_type_cd11
    diag_type_cd12
    diag_iteration1
    diag_iteration2
    diag_iteration3
    diag_iteration4
    diag_iteration5
    diag_iteration6
    diag_iteration7
    diag_iteration8
    diag_iteration9
    diag_iteration10
    diag_iteration11
    diag_iteration12
    proccd1
    proccd2
    proccd3
    proccd4
    proccd5
    proccd6
    proccd7
    proccd8
    proccd9
    proccd10
    proccd11
    proccd12
    procedure_type_cd1
    procedure_type_cd2
    procedure_type_cd3
    procedure_type_cd4
    procedure_type_cd5
    procedure_type_cd6
    procedure_type_cd7
    procedure_type_cd8
    procedure_type_cd9
    procedure_type_cd10
    procedure_type_cd11
    procedure_type_cd12
    proc_iteration1
    proc_iteration2
    proc_iteration3
    proc_iteration4
    proc_iteration5
    proc_iteration6
    proc_iteration7
    proc_iteration8
    proc_iteration9
    proc_iteration10
    proc_iteration11
    proc_iteration12
    procrqstqty1
    procapprqty1
    procrqstqty2
    procapprqty2
    procrqstqty3
    procapprqty3
    procrqstqty4
    procapprqty4
    procrqstqty5
    procapprqty5
    procrqstqty6
    procapprqty6
    procrqstqty7
    procapprqty7
    procrqstqty8
    procapprqty8
    procrqstqty9
    procapprqty9
    procrqstqty10
    procapprqty10
    procrqstqty11
    procapprqty11
    procrqstqty12
    procapprqty12
    lettertype
    lettersentdtm
    notes
    note_type_cd1
    note_type_cd2
    note_type_cd2_desc

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OK, you have a table.
    what does table TEMP_DATA have to do with posted XML?
    You need to provide more details regarding the problem you are trying to solve.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The following link contains information on using external XML files

    Toby's Oracle Blog, and Other Stuff: Query an XML File Like An External Table
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2012
    Posts
    37
    What I want to do is I just want to design a SQL loader which should read the XML and populate the table. Thats IT

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2012
    Posts
    37
    I have done it many times But do not find any fruitful solution. if you know what I mean,

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does every XML value map to specific column?
    if so, which column?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Mar 2012
    Posts
    37
    Great... You can say the XML tags are the column in the tables..
    For Example- :
    MEMBER_ID_TYPE_CD>ELIG</ns0:MEMBER_ID_TYPE_CD>
    MEMBER_ID>XXXXXXXXX</ns0:MEMBER_ID

    So in my table there will be one column MEMBER_ID_TYPE_CD and MEMBER_ID

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by goodman2253 View Post
    Great... You can say the XML tags are the column in the tables..
    For Example- :
    MEMBER_ID_TYPE_CD>ELIG</ns0:MEMBER_ID_TYPE_CD>
    MEMBER_ID>XXXXXXXXX</ns0:MEMBER_ID

    So in my table there will be one column MEMBER_ID_TYPE_CD and MEMBER_ID
    if you can say anything you want to say, but MEMBER_ID NOT exist in your posting of the purported column names in TEMP_DATA table.

    1) have you ever used XML files with Oracle before now?
    2) have you ever developed SQL*Loader script before now?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •