8 Replies Latest reply: Oct 31, 2012 9:11 AM by 880554 RSS

    XML encoding to UTF-8 charset - Oracle 9i

    880554
      Hi Masters

      Database Version : 9i

      Can you please help me here.. I am in a process of writing an Inventory Adjustment tool that will generate the XML and encode it to utf-8 charset…
      I have successfully written the code to generate the XML in this format
      <?xml version="1.0" encoding="ISO-8859-1" ?>
      <InvAdjustDesc>
      <dc_dest_id>323</dc_dest_id>
      <InvAdjustDtl>
      <item_id>12345678</item_id>
      <adjustment_reason_code>383</adjustment_reason_code>
      <unit_qty>4</unit_qty>
      <from_disposition>ATS</from_disposition>
      <to_disposition/>
      <user_id>e7062159</user_id>
      <create_date>
      <year>2012</year>
      <month>10</month>
      <day>29</day>
      <hour>14</hour>
      <minute>59</minute>
      <second>25</second>
      </create_date>
      <ww_liability_code>353</ww_liability_code>
      <ww_ref_1/>
      <ww_ref_2/>
      <ww_tran_id>25863399875</ww_tran_id>
      <ww_alloc_no/>
      <ww_final_store>353</ww_final_store>
      </InvAdjustDtl>
      </InvAdjustDesc>

      And now as part of the AIT requirement this XML needs to be encoded to utf-8 and look like this

      <?xml version="1.0" encoding="UTF-8"?><RibMessages><ribMessage><family>InvAdjust</family><type>INVADJUSTCRE</type><id>54601557</id><ribmessageID>3</ribmessageID><publishTime>2012-10-29 15:03:12.000 SAST</publishTime><messageData>&lt;?xml version=&quot;1.0&quot; encoding=&quot;ISO-8859-1&quot; ?&gt;&lt;InvAdjustDesc&gt;&lt;dc_dest_id&gt;323&lt;/dc_dest_id&gt;&lt;InvAdjustDtl&gt;&lt;item_id&gt;12345678&lt;/item_id&gt;&lt;adjustment_reason_code&gt;383&lt;/adjustment_reason_code&gt;&lt;unit_qty&gt;4&lt;/unit_qty&gt;&lt;from_disposition&gt;ATS&lt;/from_disposition&gt;&lt;to_disposition/&gt;&lt;user_id&gt;e7062159&lt;/user_id&gt;&lt;create_date&gt;&lt;year&gt;2012&lt;/year&gt;&lt;month&gt;10&lt;/month&gt;&lt;day&gt;29&lt;/day&gt;&lt;hour&gt;14&lt;/hour&gt;&lt;minute&gt;59&lt;/minute&gt;&lt;second&gt;25&lt;/second&gt;&lt;/create_date&gt;&lt;ww_liability_code&gt;353&lt;/ww_liability_code&gt;&lt;ww_ref_1/&gt;&lt;ww_ref_2/&gt;&lt;ww_tran_id&gt;25863399875&lt;/ww_tran_id&gt;&lt;ww_alloc_no/&gt;&lt;ww_final_store&gt;353&lt;/ww_final_store&gt;&lt;/InvAdjustDtl&gt;&lt;/InvAdjustDesc&gt;</messageData><customFlag>F</customFlag></ribMessage></RibMessages>

      I am not quite familiar with xml encoding do you have any suggestion on how i can accomplish this?
      Thanks
        • 1. Re: XML encoding to UTF-8 charset - Oracle 9i
          odie_63
          Hi,

          What's your database character set and exact version?

          From what I understand, your question is two-fold :

          1) Embed an XML payload (in its escaped form) into another XML
          2) Write the result somewhere in UTF-8 encoding

          Part 1 can be achieved by simply wrapping a serialized version of the message with an enclosing XMLElement.
          Assuming the message resides in a CLOB or VARCHAR2 variable v_xml_message :
          SELECT XMLElement("RibMessages",
                   XMLElement("ribMessage",
                     XMLForest(
                       'InvAdjust' as "family"
                     , 'INVADJUSTCRE' as "type"
                     , ...
                     )
                   , XMLElement("messageData", v_xml_message)
                   )
                 )
          FROM ...
          Part 2 only makes sense if you're writing the resulting document in a binary object (BLOB) or writing it to a file, otherwise the content will stay in the database character set.
          Could you clarify what you need for this part?
          • 2. Re: XML encoding to UTF-8 charset - Oracle 9i
            880554
            Hi Sir

            here is my code to create the xml
            DECLARE
            i_DC_DEST_ID VARCHAR2(10) := '323';
            i_ITEM_ID VARCHAR2(30) := '12345678';
            i_UNIT_QTY NUMBER := 4;
            i_ADJUSTMENT_REASON_CODE VARCHAR2(30) := '383';
            i_FROM_DISPOSITION VARCHAR2(5) := 'ATS';
            i_TO_DISPOSITION VARCHAR2(5) := NULL;
            i_USER_ID VARCHAR2(30) := 'e7062159';
            i_CREATE_DATE DATE := SYSDATE;
            i_AUX_REASON_CODE VARCHAR2(5) := NULL;
            i_WW_LIABILITY_CODE VARCHAR2(10) := '353';
            i_WW_REF_1 VARCHAR2(30) := NULL;
            i_WW_REF_2 VARCHAR2(30) := NULL;
            i_WW_TRAN_ID VARCHAR2(100) := '25863399875';
            i_WW_ALLOC_NO VARCHAR2(12) := NULL;
            i_WW_FINAL_STORE VARCHAR2(10) := '353';
            v_final_xml XMLTYPE;
            BEGIN
            SELECT XMLELEMENT
            ("InvAdjustDesc"
            ,XMLFOREST
            (i_dc_dest_id AS "dc_dest_id"
            ,XMLFOREST
            (NVL(i_item_id, ' ') AS "item_id"
            ,NVL(i_adjustment_reason_code, ' ') AS "adjustment_reason_code"
            ,NVL(i_unit_qty, 0) AS "unit_qty"
            ,NVL(i_from_disposition, ' ') AS "from_disposition"
            ,NVL(i_to_disposition, ' ') AS "to_disposition"
            ,NVL(i_user_id, ' ') AS "user_id"
            ,XMLFOREST(TO_CHAR(i_CREATE_DATE, 'yyyy') AS "year"
            ,TO_CHAR(i_CREATE_DATE, 'mm') AS "month"
            ,TO_CHAR(i_CREATE_DATE, 'dd') AS "day"
            ,TO_CHAR(i_CREATE_DATE, 'hh') AS "hour"
            ,TO_CHAR(i_CREATE_DATE, 'mi') AS "minute"
            ,TO_CHAR(i_CREATE_DATE, 'ss') AS "second"
            ) AS create_date
            ,NVL(i_ww_liability_code, ' ') AS "ww_liability_code"
            ,NVL(i_ww_ref_1, ' ') AS "ww_ref_1"
            ,NVL(i_ww_ref_2, ' ') AS "ww_ref_2"
            ,NVL(i_ww_tran_id, ' ') AS "ww_tran_id"
            ,NVL(i_ww_alloc_no, ' ') AS "ww_alloc_no"
            ,NVL(i_ww_final_store, ' ') AS "ww_final_store"
            ) AS InvAdjustDtl)) AS InvAdjustDesc
            INTO v_final_xml
            FROM DUAL;

            INSERT INTO xml_test
            VALUES (v_final_xml);

            COMMIT;
            END;
            And the results of this is :-
            *<InvAdjustDesc>*
            *<dc_dest_id>323</dc_dest_id>*
            *<INVADJUSTDTL>*
            *<item_id>12345678</item_id>*
            *<adjustment_reason_code>383</adjustment_reason_code>*
            *<unit_qty>4</unit_qty>*
            *<from_disposition>ATS</from_disposition>*
            *<to_disposition />*
            *<user_id>e7062159</user_id>*
            *<CREATE_DATE>*
            *<year>2012</year>*
            *<month>10</month>*
            *<day>30</day>*
            *<hour>09</hour>*
            *<minute>32</minute>*
            *<second>30</second>*
            *</CREATE_DATE>*
            *<ww_liability_code>353</ww_liability_code>*
            *<ww_ref_1 />*
            *<ww_ref_2 />*
            *<ww_tran_id>25863399875</ww_tran_id>*
            *<ww_alloc_no />*
            *<ww_final_store>353</ww_final_store>*
            *</INVADJUSTDTL>*
            *</InvAdjustDesc>*


            But Now I need to encode this to look like this
            <?xml version="1.0" encoding="UTF-8"?><RibMessages><ribMessage><family>InvAdjust</family><type>INVADJUSTCRE</type><id>54601557</id><ribmessageID>3</ribmessageID><publishTime>2012-10-29 15:03:12.000 SAST</publishTime><messageData>&lt;?xml version=&quot;1.0&quot; encoding=&quot;ISO-8859-1&quot; ?&gt;&lt;InvAdjustDesc&gt;&lt;dc_dest_id&gt;323&lt;/dc_dest_id&gt;&lt;InvAdjustDtl&gt;&lt;item_id&gt;12345678&lt;/item_id&gt;&lt;adjustment_reason_code&gt;383&lt;/adjustment_reason_code&gt;&lt;unit_qty&gt;4&lt;/unit_qty&gt;&lt;from_disposition&gt;ATS&lt;/from_disposition&gt;&lt;to_disposition/&gt;&lt;user_id&gt;e7062159&lt;/user_id&gt;&lt;create_date&gt;&lt;year&gt;2012&lt;/year&gt;&lt;month&gt;10&lt;/month&gt;&lt;day&gt;29&lt;/day&gt;&lt;hour&gt;14&lt;/hour&gt;&lt;minute&gt;59&lt;/minute&gt;&lt;second&gt;25&lt;/second&gt;&lt;/create_date&gt;&lt;ww_liability_code&gt;353&lt;/ww_liability_code&gt;&lt;ww_ref_1/&gt;&lt;ww_ref_2/&gt;&lt;ww_tran_id&gt;25863399875&lt;/ww_tran_id&gt;&lt;ww_alloc_no/&gt;&lt;ww_final_store&gt;353&lt;/ww_final_store&gt;&lt;/InvAdjustDtl&gt;&lt;/InvAdjustDesc&gt;</messageData><customFlag>F</customFlag></ribMessage></RibMessages>
            • 3. Re: XML encoding to UTF-8 charset - Oracle 9i
              odie_63
              But Now I need to encode this to look like this
              I know, and I already showed you how to do it. Did you try it?

              If you don't understand, please ask specific questions.

              I also asked three other questions that you didn't care to answer...
              • 4. Re: XML encoding to UTF-8 charset - Oracle 9i
                880554
                Hi Odie

                I found a way of writing the encoded xml thanks for your help my man, much appreciated...
                But now can you help me here this xml I am generating needs to be like the one at the bottom...
                Here is my SQL I am using....
                SELECT XMLELEMENT
                ("InvAdjustDesc"
                ,XMLFOREST
                (inv.dc_dest_id AS "dc_dest_id"
                ,XMLFOREST
                (NVL(inv.item_id, ' ') AS "item_id"
                ,NVL(inv.adjustment_reason_code, ' ') AS "adjustment_reason_code"
                ,NVL(inv.unit_qty, 0) AS "unit_qty"
                ,NVL(inv.from_disposition, ' ') AS "from_disposition"
                ,NVL(inv.to_disposition, ' ') AS "to_disposition"
                ,NVL(inv.user_id, ' ') AS "user_id"
                ,XMLFOREST(TO_CHAR(SYSDATE, 'yyyy') AS "year"
                ,TO_CHAR(SYSDATE, 'mm') AS "month"
                ,TO_CHAR(SYSDATE, 'dd') AS "day"
                ,TO_CHAR(SYSDATE, 'hh') AS "hour"
                ,TO_CHAR(SYSDATE, 'mi') AS "minute"
                ,TO_CHAR(SYSDATE, 'ss') AS "second"
                ) AS create_date
                ,NVL(inv.ww_liability_code, ' ') AS "ww_liability_code"
                ,NVL(inv.ww_ref_1, ' ') AS "ww_ref_1"
                ,NVL(inv.ww_ref_2, ' ') AS "ww_ref_2"
                ,NVL(inv.ww_tran_id, ' ') AS "ww_tran_id"
                ,NVL(inv.ww_alloc_no, ' ') AS "ww_alloc_no"
                ,NVL(inv.ww_final_store, ' ') AS "ww_final_store"
                ) AS InvAdjustDtl)) AS InvAdjustDesc
                FROM invadjust inv
                WHERE inv.dc_dest_id = 342
                and rownum <= 3;

                I need to have a leading <InvAdjustDesc> with a node <dc_dest_id> with repeating <InvAdjustDtl>
                I did try to put XMLAGG to group all of my <InvAdjustDtl> nodes but the output I get is two entries of <InvAdjustDtl> as follows
                <InvAdjustDesc>
                <dc_dest_id>323</dc_dest_id>
                <INVADJUSTDTL>
                <InvAdjustDtl>
                <item_id>20144791</item_id>
                <adjustment_reason_code>6</adjustment_reason_code>
                <unit_qty>-4</unit_qty>
                <from_disposition>ATS</from_disposition>
                <to_disposition />
                <user_id>r7052891</user_id>
                <CREATE_DATE>
                <year>2012</year>
                <month>10</month>
                <day>31</day>
                <hour>10</hour>
                <minute>15</minute>
                <second>44</second>
                </CREATE_DATE>
                <ww_liability_code>342</ww_liability_code>
                <ww_ref_1 />
                <ww_ref_2 />
                <ww_tran_id>342021751178</ww_tran_id>
                <ww_alloc_no />
                <ww_final_store>342</ww_final_store>
                </InvAdjustDtl>
                <InvAdjustDtl>
                <item_id>6009173222220</item_id>
                <adjustment_reason_code>6</adjustment_reason_code>
                <unit_qty>-1</unit_qty>
                <from_disposition>ATS</from_disposition>
                <to_disposition />
                <user_id>r7052891</user_id>
                <CREATE_DATE>
                <year>2012</year>
                <month>10</month>
                <day>31</day>
                <hour>10</hour>
                <minute>15</minute>
                <second>44</second>
                </CREATE_DATE>
                <ww_liability_code>342</ww_liability_code>
                <ww_ref_1 />
                <ww_ref_2 />
                <ww_tran_id>342021751179</ww_tran_id>
                <ww_alloc_no />
                <ww_final_store>342</ww_final_store>
                </InvAdjustDtl>
                <InvAdjustDtl>
                <item_id>2034180000008</item_id>
                <adjustment_reason_code>6</adjustment_reason_code>
                <unit_qty>-1</unit_qty>
                <from_disposition>ATS</from_disposition>
                <to_disposition />
                <user_id>r7052891</user_id>
                <CREATE_DATE>
                <year>2012</year>
                <month>10</month>
                <day>31</day>
                <hour>10</hour>
                <minute>15</minute>
                <second>44</second>
                </CREATE_DATE>
                <ww_liability_code>342</ww_liability_code>
                <ww_ref_1 />
                <ww_ref_2 />
                <ww_tran_id>342021751180</ww_tran_id>
                <ww_alloc_no />
                <ww_final_store>342</ww_final_store>
                </InvAdjustDtl>
                </INVADJUSTDTL>
                </InvAdjustDesc>cond>11</second>
                </CREATE_DATE>
                <ww_liability_code>342</ww_liability_code>
                <ww_ref_1 />
                <ww_ref_2 />
                <ww_tran_id>342021751180</ww_tran_id>
                <ww_alloc_no />
                <ww_final_store>342</ww_final_store>
                </INVADJUSTDTL>
                </InvAdjustDesc>cond>11</second>
                </CREATE_DATE>
                <ww_liability_code>342</ww_liability_code>
                <ww_ref_1 />
                <ww_ref_2 />
                <ww_tran_id>342021751180</ww_tran_id>
                <ww_alloc_no />
                <ww_final_store>342</ww_final_store>
                </INVADJUSTDTL>
                </InvAdjustDesc>


                --------------------------------------Desired Output___________________

                <?xml version="1.0" encoding="ISO-8859-1" ?>
                <InvAdjustDesc>
                     <dc_dest_id>852</dc_dest_id>
                     <InvAdjustDtl>
                          <item_id>12345</item_id>
                          <adjustment_reason_code>989</adjustment_reason_code>
                          <unit_qty>4</unit_qty>
                          <from_disposition>ats</from_disposition>
                          <to_disposition>tst</to_disposition>
                          <user_id>w759862</user_id>
                          <create_date>
                               <year>2012</year>
                               <month>10</month>
                               <day>31</day>
                               <hour>09</hour>
                               <minute>14</minute>
                               <second>23</second>
                          </create_date>
                          <ww_liability_code>852</ww_liability_code>
                          <ww_ref_1/>
                          <ww_ref_2/>
                          <ww_tran_id>12358965</ww_tran_id>
                          <ww_alloc_no/>
                          <ww_final_store>323</ww_final_store>
                     </InvAdjustDtl>
                     <InvAdjustDtl>
                          <item_id>78952675</item_id>
                          <adjustment_reason_code>987</adjustment_reason_code>
                          <unit_qty>5</unit_qty>
                          <from_disposition>ats</from_disposition>
                          <to_disposition>asr</to_disposition>
                          <user_id>w7889526</user_id>
                          <create_date>
                               <year>2012</year>
                               <month>10</month>
                               <day>31</day>
                               <hour>09</hour>
                               <minute>15</minute>
                               <second>02</second>
                          </create_date>
                          <ww_liability_code>456</ww_liability_code>
                          <ww_ref_1/>
                          <ww_ref_2/>
                          <ww_tran_id>482665226</ww_tran_id>
                          <ww_alloc_no/>
                          <ww_final_store>456</ww_final_store>
                     </InvAdjustDtl>
                </InvAdjustDesc>
                • 5. Re: XML encoding to UTF-8 charset - Oracle 9i
                  880554
                  Here is my query that I used with xmlagg
                  SELECT XMLELEMENT
                  ("InvAdjustDesc"
                  ,XMLFOREST
                  (i_DC_DEST_ID AS "dc_dest_id"
                  ,XMLAGG
                  (XMLELEMENT
                  ("InvAdjustDtl"
                  ,XMLFOREST
                  (NVL(inv.item_id, ' ') AS "item_id"
                  ,NVL(inv.adjustment_reason_code, ' ') AS "adjustment_reason_code"
                  ,NVL(inv.unit_qty, 0) AS "unit_qty"
                  ,NVL(inv.from_disposition, ' ') AS "from_disposition"
                  ,NVL(inv.to_disposition, ' ') AS "to_disposition"
                  ,NVL(inv.user_id, ' ') AS "user_id"
                  ,XMLFOREST(TO_CHAR(SYSDATE, 'yyyy') AS "year"
                  ,TO_CHAR(SYSDATE, 'mm') AS "month"
                  ,TO_CHAR(SYSDATE, 'dd') AS "day"
                  ,TO_CHAR(SYSDATE, 'hh') AS "hour"
                  ,TO_CHAR(SYSDATE, 'mi') AS "minute"
                  ,TO_CHAR(SYSDATE, 'ss') AS "second"
                  ) AS create_date
                  ,NVL(inv.ww_liability_code, ' ') AS "ww_liability_code"
                  ,NVL(inv.ww_ref_1, ' ') AS "ww_ref_1"
                  ,NVL(inv.ww_ref_2, ' ') AS "ww_ref_2"
                  ,NVL(inv.ww_tran_id, ' ') AS "ww_tran_id"
                  ,NVL(inv.ww_alloc_no, ' ') AS "ww_alloc_no"
                  ,NVL(inv.ww_final_store, ' ') AS "ww_final_store"
                  ))) AS InvAdjustDtl)) AS InvAdjustDesc
                  INTO v_final_xml
                  FROM invadjust inv
                  WHERE inv.dc_dest_id = 342
                  AND ROWNUM <= 3;
                  • 6. Re: XML encoding to UTF-8 charset - Oracle 9i
                    odie_63
                    I did try to put XMLAGG to group all of my <InvAdjustDtl> nodes but the output I get is two entries of <InvAdjustDtl>
                    That's because you're overusing XMLForest where you don't need to.

                    XMLForest is great to generate a bunch of optional elements, but to have more control over the structure it's better to use XMLElement :
                    SELECT XMLElement("InvAdjustDesc",
                             XMLElement("dc_dest_id", i_DC_DEST_ID)
                           , XMLAgg(
                               XMLElement("InvAdjustDtl",
                                 XMLForest(
                                   nvl(inv.item_id, ' ') as "item_id"
                                 , nvl(inv.adjustment_reason_code, ' ') as "adjustment_reason_code"
                                 , nvl(inv.unit_qty, 0) as "unit_qty"
                                 , nvl(inv.from_disposition, ' ') as "from_disposition"
                                 , nvl(inv.to_disposition, ' ') as "to_disposition"
                                 , nvl(inv.user_id, ' ') as "user_id"
                                 , XMLForest(
                                     to_char(SYSDATE, 'yyyy') as "year"
                                   , to_char(SYSDATE, 'mm') as "month"
                                   , to_char(SYSDATE, 'dd') as "day"
                                   , to_char(SYSDATE, 'hh') as "hour"
                                   , to_char(SYSDATE, 'mi') as "minute"
                                   , to_char(SYSDATE, 'ss') as "second"
                                   ) as "create_date"
                                 , nvl(inv.ww_liability_code, ' ') as "ww_liability_code"
                                 , nvl(inv.ww_ref_1, ' ') as "ww_ref_1"
                                 , nvl(inv.ww_ref_2, ' ') as "ww_ref_2"
                                 , nvl(inv.ww_tran_id, ' ') as "ww_tran_id"
                                 , nvl(inv.ww_alloc_no, ' ') as "ww_alloc_no"
                                 , nvl(inv.ww_final_store, ' ') as "ww_final_store"
                                 )
                               )
                             )
                           ) as InvAdjustDesc
                    INTO v_final_xml
                    FROM invadjust inv
                    WHERE inv.dc_dest_id = 342
                    AND rownum <= 3;
                    Besides, I suppose you're using NVL because some of the columns may be NULL?
                    If you want to generate elements for NULL values then use XMLElement instead of (XMLForest + NVL).
                    • 7. Re: XML encoding to UTF-8 charset - Oracle 9i
                      880554
                      Thank you very much Odie you are a lifesaver!
                      • 8. Re: XML encoding to UTF-8 charset - Oracle 9i
                        880554
                        Thanks to Odie for helping me everything is working now... as expected :-)