7 Replies Latest reply: Nov 27, 2012 3:56 AM by odie_63 RSS

    Need to read a XML file and store multilingual data

    RameshkumarT
      I am having an XML file which contains multiple records and each maybe in a different language .identified EN -english ES-Spanish etc
      - <Document xmlns="http://www.xxxxx.com/ws/integration/toolkit/2011/05">
      - <Attributes>
      <Attribute name="duration">0:00:02.993</Attribute>
      <Attribute name="count">47</Attribute>
      <Attribute name="entity">Requisition</Attribute>
      <Attribute name="mode">XML</Attribute>
      <Attribute name="version">http://www.xxxx.com/ws/tee800/2009/01</Attribute>
      </Attributes>
      - <Content>
      - <ExportXML xmlns="http://www.xxxxx.com/ws/integration/toolkit/2005/07/action/export">
      - <record>
      <field name="ContestNumber">120000002O</field>
      <field name="JobInformation,JobType,Description">Standard</field>
      <field name="JobFamily">ACCOUNTING/FINANCE</field>
      <field name="JobInformation,Organization,Name">CHMS-DO NOT USE</field>
      <field name="Country">USA</field>
      <field name="State">Illinois</field>
      <field name="City">Lake County</field>
      <field name="Title_EN">TaTest1</field>
      <field name="Title_es">TaTest1 translated to Espanol</field>
      <field name="InternalDescription_EN">Humira marketing team - develop programs.</field>
      <field name="InternalDescription_zhCN" />
      <field name="InternalDescription_zhTW" />
      <field name="InternalDescription_es">Development of marketing programs for </field>
      <field name="ExternalDescription_EN">Marketing programs for core products</field>
      <field name="ExternalDescription_zhCN" />
      <field name="ExternalDescription_zhTW" />
      <field name="ExternalDescription_es">Marketing core products - Spain</field>
      <field name="URL_en">http://[ZONE]/careersection/[CAREER_SECTION]/jobdetail.ftl?lang=en&job=120000002O</field>
      <field name="URL_zhCN" />
      <field name="URL_zhTW" />
      <field name="URL_es">http://[ZONE]/careersection/[CAREER_SECTION]/jobdetail.ftl?lang=es&job=120000002O</field>
      <field name="PostedExternally">true</field>
      <field name="PostedInternally">true</field>
      <field name="EnglishActive">true</field>
      <field name="SimplifiedChineseActive">false</field>
      <field name="TraditionalChineseActive">false</field>
      <field name="SpanishActive">true</field>
      <field name="InternalBonusTracking" />
      <field name="ExternalBonusTracking" />
      <field name="POSTING_GRADE" />
      <field name="SAP_Schedule">Full-time</field>
      <field name="PostingDateExternal">2012-11-20</field>
      <field name="PostingDateInternal">2012-11-20</field>
      </record>
      </ExportXML>
      </Content>
      </Document>

      I need to store and retrieve data into a AL32UTF8 database without losing language attributed.

      I am using DBMS_XMLDOM.getnodevalue (); to fetch the XML stored in a CLOB column

      But the package returns VARCHAR2 .How do i return NVARCHAR2 so that i can store Multilingual data
      DBMS_XMLDOM.GETNODEVALUE(
      n IN DOMNode)
      RETURN VARCHAR2;


      Or is there an easier way around using XMLTYPE

      Edited by: Rameshkumar T on Nov 27, 2012 6:14 AM
        • 1. Re: Need to read a XML file and store multilingual data
          ascheffer
          If you have a XMLTYPE stored as a clob, it is in the database characterset, AL32UTF8.
          DBMS_XMLDOM.GETNODEVALUE returns a varchar2, also in the database characterset, AL32UTF8.
          And in AL32UTF8 you can store most (all?) languages and encodings, why do you want to store some values as a NVARCHAR2?
          • 2. Re: Need to read a XML file and store multilingual data
            RameshkumarT
            You mean to say If I have a database with AL32UTF8 characterset and I use a XMLTYPE as CLOB on the table.
            Then if I use DBMS_XMLDOM.GETNODEVALUE will store the multilingual data correctly .
            Because I am facing some issue in storing the data correctly
            • 3. Re: Need to read a XML file and store multilingual data
              odie_63
              Because I am facing some issue in storing the data correctly
              What issue? Be specific please.

              For example, given the sample (not wellformed) XML above, what do you want to store, and where?
              • 4. Re: Need to read a XML file and store multilingual data
                ascheffer
                If your original XML is encoded as UTF8 and you loaded the XML without any characteset conversions (which can depend on your client settings!) I would say, yes, that's possible.
                • 5. Re: Need to read a XML file and store multilingual data
                  RameshkumarT
                  dbms_lob.loadclobfromfile( v_xml_msg
                                                        , v_xml_bfile
                                                        , dbms_lob.getlength(v_xml_bfile)
                                                        , v_dest_offset
                                                        , v_src_offset
                                                        , NLS_CHARSET_ID('AL32UTF8')
                                                        , v_lang_context
                                                        , v_warning
                                                        );
                  SELECT message
                     INTO   v_xml_clob
                     FROM   gjb_xml_message gtmq
                     WHERE  message_id = v_msg_id;
                     v_line_no := 1;
                     v_doc     := dbms_xmldom.newDOMDocument(v_xml_clob);
                     v_line_no := 2;
                     v_nodes    := dbms_xmldom.getElementsByTagName(v_doc, '*');
                  
                     FOR i IN 0..dbms_xmldom.getlength(v_nodes)-1
                     LOOP
                  
                         BEGIN
                             --
                             v_line_no := 3.0;
                             v_element_x  := dbms_xmldom.makeelement(xmldom.item(v_nodes,i));
                             v_line_no := 3.1;
                             v_node       := dbms_xmldom.item(v_nodes,i);
                             v_line_no := 3.2;
                             v_tag        := dbms_xmldom.getNodeName(v_node);
                             v_line_no := 3.3;
                             v_node_2     := dbms_xmldom.getfirstchild(v_node);
                  
                             v_line_no := 4.0;
                             --
                             v_node_map     := DBMS_XMLDOM.getattributes (v_node);
                             FOR x in 0 ..DBMS_XMLDOM.getlength (v_node_map)- 1
                             LOOP
                                BEGIN
                                   v_line_no := 4.1;
                                   v_one_node := DBMS_XMLDOM.item (v_node_map, x);
                                   v_line_no := 4.2;
                                   v_attrname := DBMS_XMLDOM.getnodename (v_one_node);
                                   v_line_no := 4.3;
                                   v_attrval  := DBMS_XMLDOM.getnodevalue (v_one_node);
                                   IF  v_attrval = 'ContestNumber' THEN
                                        v_line_no := 4.4;
                                        v_contest_num := v_tag_value;
                  
                                        v_rec_no := NULL;
                  
                                        IF v_load_type = 'I' THEN
                                           v_rec_no := fn_get_job_id(v_contest_num);
                                        END IF;
                  
                                        IF v_rec_no IS NULL THEN
                                           SELECT seq_gjbjobid.NEXTVAL
                                           INTO   v_rec_no
                                           FROM   dual;
                                        END IF;
                  
                                   END IF;
                  
                  
                  IF UPPER(v_tag) = 'FIELD' THEN
                  
                           BEGIN
                              v_line_no := 5;
                              --
                              INSERT INTO gjb_xml_data
                                     ( message_id
                                     , job_id
                                     , tag_name
                                     , col_name
                                     , col_no
                                     , tag_level
                                     , tag_value
                                     , tag_lang
                                     , tag_value_clob
                                     )
                              SELECT v_msg_id
                                   , v_rec_no
                                   , tag_name
                                   , col_name
                                   , col_no
                                   , tag_level
                                   , v_tag_value
                                   , tag_lang
                                   , v_tag_value_clob
                              FROM   gjb_xml_tags gxt
                              WHERE  tag_name = v_attrval;
                              v_tag_value := NULL;
                              v_tag_value_clob := NULL;
                  Edited by: Rameshkumar T on Nov 27, 2012 8:27 AM
                  • 6. Re: Need to read a XML file and store multilingual data
                    sb92075
                    How do I ask a question on the forums?
                    SQL and PL/SQL FAQ
                    • 7. Re: Need to read a XML file and store multilingual data
                      odie_63
                      There's no point in posting code snippets we can't run, moreover without explaining what the issue is, what the expected result is etc.

                      So please, as explained in the link given above, post a clear test case, that is :

                      - database version
                      - valid sample XML with a few records
                      - expected output / business logic, e.g. which XML data goes to which column in the target table

                      Depending on your actual version, there's a good chance all your code may be simplified down to only a few statements.