1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

XML parsing failed

Discussion in 'SQL PL/SQL' started by venu57, Jun 12, 2015.

  1. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Guys,

    I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

    When i am trying to read some part of xml data using XMLSEQUENCE and EXTRACTVALUE methods, getting the below error.

    Error ::

    Error report:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00601: Invalid token in: '/pi:payroll_Extract/pi:payGroup/pi:Header'
    ORA-06512: at line 61
    31011. 00000 - "XML parsing failed"
    *Cause: XML parser returned an error while trying to parse the document.
    *Action: Check if the document to be parsed is valid.


    Code ::


    DECLARE
    X XMLTYPE := XMLTYPE(' <?xml version="1.0" encoding="UTF-8" ?>
    <pi:payroll_Extract xmlns:pi="urn:com.workday/picof">
    <pi:payGroup>
    <pi:Header>
    <pi:Version>18</pi:Version>
    <pi:period_Start>2011-04-01-07:00</pi:period_Start>
    <pi:period_End>2013-04-30-07:00</pi:period_End>
    <pi:Update_From>2011-04-27T11:38:46.000-07:00</pi:Update_From>
    <pi:Update_To>2013-04-27T11:45:41.982-07:00</pi:Update_To>
    </pi:Header>
    <pi:Employee>
    <pi:Summary>
    <pi:Employee_ID>0192104</pi:Employee_ID>
    <pi:Name>Kiran M</pi:Name>
    <pi:payroll_Company>ABC Company</pi:payroll_Company>
    <pi:pay_Group>Monthly (India)</pi:pay_Group>
    </pi:Summary>
    <pi:personal>
    <pi:First_Name>Kiran</pi:First_Name>
    <pi:Last_Name>M</pi:Last_Name>
    <pi:Gender>M</pi:Gender>
    <pi:Birth_Date>18-02-1889</pi:Birth_Date>
    <pi:Citizenship_Status>ROR</pi:Citizenship_Status>
    <pi:Nationality>IND</pi:Nationality>
    <pi:First_Municipality>HYD</pi:First_Municipality>
    <pi:First_Region>Andhra Pradesh</pi:First_Region>
    <pi:First_Postal_Code>500001</pi:First_Postal_Code>
    <pi:First_Country>IND</pi:First_Country>
    </pi:personal>
    <pi:Status>
    <pi:Staffing_Event>NEW</pi:Staffing_Event>
    <pi:Staffing_Event_Date>06-08-2011</pi:Staffing_Event_Date>
    <pi:Hire_Date>06-08-2011</pi:Hire_Date>
    <pi:Original_Hire_Date>06-08-2011</pi:Original_Hire_Date>
    </pi:Status>
    <pi:position>
    <pi:Operation>ADD</pi:Operation>
    <pi:Effective_Date>06-08-2011</pi:Effective_Date>
    <pi:Business_Title>Customer Service, PD</pi:Business_Title>
    <pi:Worker_Type>Regular</pi:Worker_Type>
    <pi:position_Time_Type>Full time</pi:position_Time_Type>
    </pi:position>
    <pi:Identifier>
    <pi:Operation>ADD</pi:Operation>
    <pi:Country>IND</pi:Country>
    </pi:Identifier>
    <pi:pay_Data>
    <pi:Date>07-04-2014</pi:Date>
    <pi:Amount>650</pi:Amount>
    <pi:Currency>INR</pi:Currency>
    </pi:pay_Data>
    <pi:Related_Person>
    <pi:Operation>ADD</pi:Operation>
    <pi:Relationship_Type>Father</pi:Relationship_Type>
    </pi:Related_Person>
    </pi:Employee>
    </pi:payGroup>
    </pi:payroll_Extract>');
    BEGIN
    FOR R IN (SELECT EXTRACTVALUE(VALUE(P), '/pi:Header/pi:Version/text()') AS VERSION,
    EXTRACTVALUE(VALUE(P), '/pi:Header/pi:period_Start/text()') AS PERIOD_START,
    EXTRACTVALUE(VALUE(P), '/pi:Header/pi:period_End/text()') AS PERIOD_END,
    EXTRACTVALUE(VALUE(P), '/pi:Header/pi:Update_From/text()') AS UPDATE_FROM,
    EXTRACTVALUE(VALUE(P), '/pi:Header/pi:Update_To/text()') AS UPDATE_TO
    FROM TABLE(XMLSEQUENCE(EXTRACT(X, '/pi:payroll_Extract/pi:payGroup/pi:Header'))) P) LOOP
    DBMS_OUTPUT.PUT_LINE(R.VERSION || ' ' || R.PERIOD_START || ' ' ||
    R.PERIOD_END || ' ' || R.UPDATE_FROM || ' ' ||
    R.UPDATE_TO);
    END LOOP;
    END;

    /


    Please provide the solution for the above scenario. Else just let me know how to read xml file data into oracle table.