1 2 Previous Next 21 Replies Latest reply: May 8, 2013 9:43 AM by Jason_(A_Non) RSS

    XML in plsql

    894936
      Hi,
      For this i used one normal xml element tagging. when i created the below select statement i am able to get the correct output as xml.
      this xml tagging i need to use in plsql block which i mentioned .

      Edited by: 891933 on 22-Apr-2013 21:28
        • 1. Re: XML in plsql
          odie_63
          here the i need to use this above select statement in my pl sql block. in above select statement i used "B2B_CUSTOMER_INVOICE_V". which i should not use.
          instead of that i need to use "I_0470002_log_t" as mentioned below in plsql block.
          What prevents you from building the query over "I_0470002_log_t" instead of the view ?

          You already know how to generate XML in the format you want, there's no difference if the data comes from a view or a table.
          • 2. Re: XML in plsql
            894936
            yes that is correct but the when we look into plsql block the column names which i mentioned should be implemented.

            Edited by: 891933 on 22-Apr-2013 21:29
            • 3. Re: XML in plsql
              odie_63
              for my understanding purpose i created a view for which i created the xml select statament which is my correct output.
              But you're not able to apply the same rules on the base table?

              Why do you think someone else - who doesn't even know the data model or the business rules - might do it better?
              so can you please help me out how to put my xml tagging select statement in my plsql block to get the same output.
              Sorry I don't see where the problem is.

              Let's start again with this simple question :
              Do you know how to use SQL/XML functions or not?
              • 4. Re: XML in plsql
                894936
                Hi,
                Below is my plsql block for which i need to generate xml file.

                Edited by: 891933 on 22-Apr-2013 21:30
                • 5. Re: XML in plsql
                  odie_63
                  Below is my plsql block for which i need to generate xml file.
                  Can you please tell me now how i need to achieve the below 3 mentioned points.
                  •     The data shall be sent in XML format
                  •     The XML schema is defined in DeclaredFunds.xsd
                  •     The XML text for a file shall be written into a CLOB
                  Repeating the same things over and over again won't help anyone, you least of all.

                  Start by answering all my previous questions and maybe we'll be able to move forward.
                  • 6. Re: XML in plsql
                    894936
                    Sorry,
                    ok fine. i tried to implement and used the concept of xml tagging and xml element.
                    otherthan these i do not have knowledge on this.
                    • 7. Re: XML in plsql
                      Jason_(A_Non)
                      Since it appears you do not fully understand the first SQL statement you posted, the one with XMLElement in it, I would suggest to read this
                      [url http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB1600]Generating XML Data from the Database

                      You will also need this,[url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xslpro.htm#ARPLS70151]DBMS_XSLPROCESSOR.CLOB2FILE, after you convert the XML into a CLOB, via [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions252.htm#SQLRF06231]XMLSERIALIZE  so you can write it to disk.

                      Edited by: A_Non on Apr 18, 2013 8:30 AM
                      Forgot to add, what is your version of Oracle?
                      select * from v$version
                      • 8. Re: XML in plsql
                        894936
                        hey i understood the first post of mine generating xml by using xml element and xml tagging concept.
                        i was able to create .
                        my concern was that in my xml select statement i used different view where i found my direct data.
                        but in my plsql block my requirement is difrenet like different table different functionality i used.
                        so in my plsql block the data which i am fetching for that i want to generate xml with the output i mentioned.
                        so, i want to know how to generate the xml content in my plsql block.
                        atleast can you tell me how to add the select xml statement in my plsql block.
                        • 9. Re: XML in plsql
                          Jason_(A_Non)
                          Then if you understand the SQL/XML functionality provided by Oracle (what you call "xml element and xml tagging concept") then you should be able to easily write a similar SQL/XML statement that reads from the I_0470002_log_t table. If you get stuck, you can post a question that says "hey, I'm stuck, I have this SQL and this input and it produces this output, but I really want this output, what do I need to change"

                          The output from a SQL/XML is an XMLType data type, but you can have it return a CLOB with using XMLSerialize. I have no clue if that helps. An SQL statement is a SQL statement and what it does depends upon how you write it. What have you tried for a SQL/XML statement for what you need?
                          • 10. Re: XML in plsql
                            894936
                            HI
                            this is my requirement :

                            Consider data from I_0470002_log_t only

                            · Sum the sold amount by store, sales date, till, receipt

                            · Consider only sums greater than 0

                            · Every record identified by store, sales date, till, receipt shall only be sent once to SAREC+

                            · The sums must be multiplied with the factor given by this call:

                            o curr_02_pck.corrfact_fct (comp_code, 'I0470001')

                            · The comp_code must be derived from the store number:

                            o batch_01_pck.setcompfromstore_prc (pi_str_stono => sto_no);

                            o comp_code := glob_01_pck.getcompcode_fct;

                            · The data shall be sent in XML format

                            for this i tried with plsql block like below:
                            DECLARE
                               CURSOR Cur_st
                               IS
                                                      
                            SELECT DISTINCT
                                        CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
                                        CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
                                        CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
                                        CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
                                        TO_DATE (A.SALES_DATE, 'YYMMDD'),
                                        CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
                                        CAST (A.CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
                                        CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
                                        sold_amount AS INVOICE_TOTAL,
                                        CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
                                        CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
                                        CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
                                        CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                                        'N' AS ON_HOLD_FLAG
                                   FROM I_0470002_log_t A
                               WHERE A.SOLD_AMOUNT != 0
                               GROUP BY A.STO_NO,
                                        A.CUR_CODE,
                                        A.RECEIPT_NO,
                                        A.CASH_NO,
                                        A.SALES_DATE,
                                        A.SOLD_AMOUNT;
                               v_comp_code   VARCHAR2 (10);
                               v_sum         NUMBER;
                               v_factor      NUMBER;
                               v_large LONG;
                               v_sto_no      NUMBER;
                               BEGIN
                               FOR Rec_st IN Cur_st
                               LOOP
                            
                                  batch_01_pck.setcompfromstore_prc (pi_str_stono => Rec_st.BU_CODE);
                                  /*dbms_output.put_line('Rec_st.BU_CODE-->'||Rec_st.BU_CODE);*/
                                  /*dbms_output.put_line('Rec_st.INVOICE_TOTAL-->'||Rec_st.INVOICE_TOTAL);*/
                            
                                  v_comp_code := glob_01_pck.getcompcode_fct;
                                  /*dbms_output.put_line('v_comp_code-->'||v_comp_code);*/
                            
                                  v_factor := curr_02_pck.corrfact_fct (v_comp_code, 'I0470001');
                                  /*dbms_output.put_line('v_factor-->'||v_factor);*/
                            
                                  v_sum := rec_st.INVOICE_TOTAL* v_factor;
                                  /*dbms_output.put_line('v_sum-->'||v_sum);*/
                            
                                  v_large :=
                                        'select sum(sold_amount)*'
                                     || v_factor
                                     || ' "INVOICE_TOTAL" ,sto_no as bu_code,"STO" as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (A.SALES_DATE,"YYMMDD")as sales_date,
                            cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,"N" AS ON_HOLD_FLAG from I_0470002_log_t  where BU_CODE ='
                                     || rec_st.BU_CODE
                                     || '  group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount';
                            
                               END LOOP;
                            END;
                            expected output: for my above plsql block which i have written , i want to generate the below output. so can you please help me out where i need to use xml elment or tagging concept inorder to get below output.

                            <tendermanagement xmlns="http://www.ikea.com/sarec/declaredfunds" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd" buType="STO" buCode="001" sourceSystem="RIMS">
                            <tendermovement date="2013-04-03T00:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="2725.55" tillNo="28" transactionNo="3"/>
                            <tendermovement date="2013-04-02T00:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="238.97" tillNo="29" transactionNo="2"/>
                            <tendermovement date="2013-03-07T00:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="683.1" tillNo="29" transactionNo="7"/>
                            <tendermovement date="2013-03-08T00:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="40" tillNo="28" transactionNo="6"/>
                            </tendermanagement>

                            Edited by: 891933 on 22-Apr-2013 21:47
                            • 11. Re: XML in plsql
                              odie_63
                              Apparently, XML is not your only problem in this task. Using a deprecated datatype and dynamic SQL says it all.

                              Could you give some sample data from I_0470002_LOG_T ?
                              • 12. Re: XML in plsql
                                894936
                                This is the sample data.

                                CREATE TABLE RIMS.I_0470002_LOG_T ( REG_DATE VARCHAR2(6 BYTE), STO_NO VARCHAR2(3 BYTE), SALES_DATE VARCHAR2(6 BYTE), CASH_NO NUMBER(5), RECEIPT_NO NUMBER(10), ORDER_NO NUMBER(10), TOT_CUST_NO VARCHAR2(13 BYTE), CONTROL_NO NUMBER(10), ART_NO VARCHAR2(8 BYTE), SOLD_QTY NUMBER(10), SOLD_AMOUNT NUMBER(10), INV_ADM_COST_FR VARCHAR2(1 BYTE), VAT_OBLIGED VARCHAR2(1 BYTE), CUR_CODE VARCHAR2(3 BYTE), VAT_RATE VARCHAR2(6 BYTE), VAT_CODE VARCHAR2(10 BYTE), BU_CODE VARCHAR2(15 BYTE), BU_TYPE VARCHAR2(9 BYTE), TIMESTAMP DATE, INS_USER VARCHAR2(30 BYTE), MACHINE VARCHAR2(64 BYTE) )
                                SET DEFINE OFF
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '221', '081208', 8, 92, 
                                    0, '   1753136033', 196, '40089477', 4, 
                                    24000, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '421', '081208', 7, 147, 
                                    0, '   2064491042', 0, '50122339', 2, 
                                    5998, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '421', '081208', 7, 147, 
                                    0, '   2064491042', 0, '50131386', 2, 
                                    5998, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '221', '081208', 8, 92, 
                                    0, '   1753136033', 196, '40125550', 5, 
                                    7950, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '421', '081208', 7, 147, 
                                    0, '   2064491042', 0, '60064211', 1, 
                                    259, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '221', '081208', 8, 92, 
                                    0, '   1753136033', 196, '40129666', 5, 
                                    2495, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '421', '081208', 7, 147, 
                                    0, '   2064491042', 0, '60140762', 1, 
                                    5500, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '221', '081208', 8, 92, 
                                    0, '   1753136033', 196, '40130170', 6, 
                                    1794, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                Insert into RIMS.I_0470002_LOG_T
                                   (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
                                 Values
                                   ('081208', '421', '081208', 7, 147, 
                                    0, '   2064491042', 0, '70000087', 1, 
                                    0, '0', '1', 'EUR', '001900', 
                                    'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS', 
                                    'hummer');
                                COMMIT;
                                Edited by: 891933 on 23-Apr-2013 04:51
                                • 13. Re: XML in plsql
                                  Jason_(A_Non)
                                  I'm guessing that your sample table rows have no relation to the XML you listed above. If they do, I am not seeing it. You should really consider storing the date in a DATE column instead of a VARCHAR2 column and only storing a two-digit year.

                                  Based on everything we don't know, here is some SQL/XML that does not do everything you need but shows you the basics
                                  SELECT XMLElement("tendermanagement",
                                           XMLAttributes('RIMS' AS "sourceSystem",
                                                         'STO' AS "buType"),
                                           XMLAgg(XMLElement("tendermovement",
                                                    XMLAttributes(to_char(to_date(sales_date,'YYMMDD'), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "date",
                                                                  cash_no AS "tillNo",
                                                                  sum(sold_amount) AS "valueTendered"))))
                                   from I_0470002_LOG_T
                                  GROUP BY sto_no, sales_date, cash_no, receipt_no;
                                  produces (formatted for human readability)
                                  <tendermanagement sourceSystem="RIMS" buType="STO">
                                    <tendermovement date="2008-12-08T00:00:00Z" tillNo="8" valueTendered="36239"></tendermovement>
                                    <tendermovement date="2008-12-08T00:00:00Z" tillNo="7" valueTendered="17755"></tendermovement>
                                  </tendermanagement>
                                  • 14. Re: XML in plsql
                                    894936
                                    Hi Team,
                                    can you help me out for the below mentioned errors.
                                    where i need to chagne this
                                    Ererors : ORA-00937: not a single-group group function
                                    select xmlelement("tendermanagement",
                                    xmlattributes(
                                    'http://www.ikea.com/sarec/declaredfunds' as "xmlns"
                                    , 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                                    , 'http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd' as "xsi:schemaLocation"
                                    , bu_type as "buType"
                                    , bu_code as "buCode"
                                    , 'RIMS' as "sourceSystem"
                                    )
                                    , xmlagg(
                                    xmlelement("tendermovement",
                                    xmlattributes(
                                    to_char(to_date(sales_date,'YYMMDD'), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "date"
                                    , 'DROP' as "bookingType"
                                    , 'B2B Invoice' as "tenderType"
                                    , 'STORE' as "salesAreaGroup"
                                    , SUM(SOLD_AMOUNT) as "valueTendered"
                                    , CASH_no as "tillNo"
                                    , receipt_no as "transactionNo"
                                    )
                                    )
                                    )
                                    ).extract('/*').getclobval() as CUSTOMER
                                    from  I_0470002_log_t  WHERE ROWNUM <10
                                    group by bu_code, bu_type,SOLD_AMOUNT
                                    Edited by: 891933 on 28-Apr-2013 23:28
                                    1 2 Previous Next