3 Replies Latest reply on Nov 14, 2018 9:02 AM by sahar.zd

    XML Query

    sahar.zd

      Dear All,

      im trying to write this query in XML but it gives me error.

       

      can you please check -  From_GL_Date  is a dynamic variable will be given when i run the report. (the error is the report isnt take it

       

       

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

      <dataTemplate name="INV"

      description="Supplier Balance XML" version="1.0"

      defaultPackage="">

      <properties></properties>

      <dataQuery>

      <sqlStatement name="Q1">

      <![CDATA[

      select

      SUP.VENDOR_ID,

      SUP.VENDOR_NAME,

      SUP.SEGMENT1 VENDOR_NUMBER,

      sup.VAT_REGISTRATION_NUM VAT,

      ( nvl(sum(dist.amount) ,0))  balance

      from

      AP_INVOICE_DISTRIBUTIONS_ALL dist,

      AP_INVOICES_ALL INV,

      AP_SUPPLIERS SUP,

      AP_SUPPLIER_SITES_ALL SUPA,

      gl_code_combinations acct

      where

      And INV.accts_pay_code_combination_id=acct.code_combination_id

      and  SUP.VENDOR_ID = SUPA.VENDOR_ID

      AND dist.INVOICE_ID(+) = INV.INVOICE_ID

      AND UPPER(inv.INVOICE_TYPE_LOOKUP_CODE) <> UPPER('Prepayment')

      AND UPPER(dist.LINE_TYPE_LOOKUP_CODE) <> UPPER('PREPAY')

      and INV.VENDOR_SITE_ID = SUPA.VENDOR_SITE_ID

      and inv.gl_date between TO_DATE($From_GL_Date ,'YYYY-MM-DD')   and TO_DATE('2018/09/30','YYYY-MM-DD') 

      and SUPA.org_id =105

      and sup.enabled_flag = 'Y'

      and ( sup.pay_group_lookup_code   not in ('EMPLOYEE' )  or sup.pay_group_lookup_code is null )

      GROUP BY

      SUP.VENDOR_ID,

      SUP.VENDOR_NAME,

      SUP.SEGMENT1 ,

      sup.VAT_REGISTRATION_NUM

      ]]>

      </sqlStatement>

      </dataQuery>

      <dataStructure>

      <group name="INV" source="Q1">

      <element name="VENDOR_NAME" value="VENDOR_NAME" />

      <element name="VENDOR_NUMBER" value="VENDOR_NUMBER"/>

      <element name="VAT" value="VAT"/>

      <element name="balance" value="balance"/>

      </group>

      </dataStructure>

       

       

      </dataTemplate>

       

       

      thank you in advance ..

        • 1. Re: XML Query
          mNem

          Not sure about what is being referred to as the report ... but here are few things I found while testing it ...

           

          The following examples assume that you are invoking some features of xml/xquery available within oracle.

           

          Additionally, xmlserialize() function is used for pretty printing purposes only.

           

          1. If a variable needs to be used within an xml element in xquery, it needs to be enclosed within {} as shown below.

           

          SQL> set long 500000;
          SQL> set pagesize 0;
          SQL>
          SQL> set echo on;
          SQL>
          SQL>
          SQL> select 
            2  xmlserialize(content
            3  xmlquery(
            4  '
            5  <example>
            6  <sqlStatement name="Q1">... {$From_GL_Date} ...</sqlStatement>
            7  </example>
            8  '
            9  passing '2018-10-12' as "From_GL_Date"
          10  returning content
          11  )
          12  indent ) output
          13  from dual
          14  ;
          <example>
            <sqlStatement name="Q1">... 2018-10-12 ...</sqlStatement>
          </example>

          SQL>

           

           

           

           

          2. <![CDATA[ ... ]]> doesn't differentiate a variable from the rest within it.


          SQL> select 
            2  xmlserialize(content
            3  xmlquery(
            4  '
            5  <example>
            6  <sqlStatement name="Q1">
            7  <![CDATA[... {$From_GL_Date} ...]]></sqlStatement>
            8  </example>
            9  '
          10  passing '2018-10-12' as "From_GL_Date"
          11  returning content
          12  )
          13  indent ) output
          14  from dual
          15  ;
          <example>
            <sqlStatement name="Q1"><![CDATA[... {$From_GL_Date} ...]]></sqlStatement>
          </example>

           

           

          3. Additionally, please be aware that an xml element named as <element> with any attribute is causing problems. An <element>1</element> does not show that issue.

           

          SQL>
          SQL> select xmlserialize(content
            2  xmlquery(
            3  '
            4  <example>
            5  <element name="VENDOR_NAME">3</element>
            6  </example>
            7  '
            8  passing '2018-10-12' as "From_GL_Date"
            9  returning content
          10  )
          11  indent )
          12  from dual
          13  ;

          Error starting at line : 38 in command -
          select xmlserialize(content
          xmlquery(
          '
          <example>
          <element name="VENDOR_NAME">3</element>
          </example>
          '
          passing '2018-10-12' as "From_GL_Date"
          returning content
          )
          indent )
          from dual

          Error at Command Line : 49 Column : 6
          Error report -
          SQL Error: ORA-19114: XPST0003 - error during parsing the XQuery expression:
          LPX-00801: XQuery syntax error at 'element'
          4   <element name="VENDOR_NAME">3</element>
          -   ^
          19114. 00000 -  "XPST0003 - error during parsing the XQuery expression: %s"
          *Cause:    An error occurred during the parsing of the XQuery expression.
          *Action:   Check the detailed error message for the possible causes.
          SQL>

           

           

           

          4. Just in case, showing the hint that is used as a workaround for the problem #3.
          SQL>
          SQL> select /*+ no_xml_query_rewrite */ xmlserialize(content
            2  xmlquery(
            3  '
            4  <example>
            5  <element>1</element>
            6  <element>2</element>
            7  <element name="VENDOR_NAME">3</element>
            8  <sql>... ''{$From_GL_Date}'' ...</sql>
            9  </example>
          10  '
          11  passing '2018-10-12' as "From_GL_Date"
          12  returning content
          13  )
          14  indent )
          15  from dual
          16  ;
          <example>
            <element>1</element>
            <element>2</element>
            <element name="VENDOR_NAME">3</element>
            <sql>... &apos;2018-10-12&apos; ...</sql>
          </example>

           

          5. FYI, if the sql statement within the cdata to be invoked at some time later, then the date input needs to be enclosed within single quotes. And each single quote needs to be escaped by another single quote as shown below.

          <sql>... ''{$From_GL_Date}'' ...</sql>

          1 person found this helpful
          • 2. Re: XML Query
            cormaco

            It would have helped if you had named the product and version you are using here.

            It seems to be BI Publisher Report Designer.

            Here is a page that describes how to properly pass a parameter to a sql query in a data template:

            https://docs.oracle.com/cd/E10415_01/doc/bi.1013/e12187/T421739T434255.htm

             

            the picture is described in the document text

            1 person found this helpful
            • 3. Re: XML Query
              sahar.zd

              thank you.

              when i added the parameters it worked perfectly