2 Replies Latest reply on Aug 9, 2017 6:27 AM by Tony Kirn-Oracle

    BI Publisher "Failed to Load XML" when creating sample data

    dman6750

      I am new to BI Publisher and I am receiving the error "Failed to Load XML" after trying to view the data generated by the following SQL code (runs successfully in SQL Developer):

       

      SELECT oh.order_number,

      ol.line_number,

      ol.creation_date,

      ol.ORDERED_ITEM,

      ol.line_id as ITEM_KEY

      FROM ont.oe_order_lines_all ol,

      ont.oe_order_headers_all oh

      WHERE ol.header_id = oh.header_id

      and ol.attribute4 <> 'COPY'

      and ol.line_id in

      (

      SELECT to_number(item_key)

      FROM APPS.WF_ITEM_ACTIVITY_STATUSES_V

      WHERE activity_label = 'XXNM_BLOCK_ORDER_APPR_BLOCK'

      and activity_status_code != 'COMPLETE'

      )

      and ol.flow_status_code <> 'CLOSED'

      and NVL(ol.cancelled_flag,'N') != 'Y'

      ORDER BY 1,2,3

       

      I am trying to create sample data to create a BI Publisher report. Our OBIEE version is 11.1.7.140715. Please provide assistance with what might be causing this error.

        • 1. Re: BI Publisher "Failed to Load XML" when creating sample data
          Violeta F-Oracle

          When you test the SQL in your data model,  select the Data Engine Log button on same page. It has the details of your error.

          • 2. Re: BI Publisher "Failed to Load XML" when creating sample data
            Tony Kirn-Oracle

            I have a similiar issue but my engine log reports 1 line as per:

            <?xml version="1.0" encoding="UTF-8"?><debugLog></debugLog>

             

            So not very helpful just yet.

             

            This is my sql that runs fine in sqlplus and works fine when I don't use 'host' as the member_target_type and I get the failed to load xml error. Using 'host' target type works just find in sqlplus.

             

            Code:

            SELECT member_target_name, exadata_host, availability_status,

              SUM(NVL(duration, 0)) "Days"

            FROM

              (

                SELECT a.member_target_name,

                a.member_target_type exadata_host,

                b.availability_status,

                round((SYSDATE - b.start_timestamp),10) duration

              FROM sysman.mgmt$target_flat_members a,

                sysman.mgmt$availability_current b

              WHERE a.member_target_name   =b.target_name

              AND a.member_target_type   ='host'

              AND a.aggregate_target_name IN (:para_exa_machine)

              union all

              SELECT a.member_target_name,

                a.member_target_type exadata_host,

                b.availability_status,

                round((NVL(b.end_timestamp, SYSDATE) - case when b.start_timestamp < :para_start_date

            then to_date(:para_start_date)  <----------------------------- used to have an issue here until I added the to_date but now just won't work for 'host' target

            else b.start_timestamp end),10) duration

              FROM sysman.mgmt$target_flat_members a,

                sysman.mgmt$availability_history b

              WHERE a.member_target_name=b.target_name

              AND a.member_target_type='host'

              AND a.aggregate_target_name in (:para_exa_machine)

              AND b.end_timestamp           > :para_start_date

              )

            GROUP BY member_target_name, exadata_host, availability_status