This discussion is archived
7 Replies Latest reply: Nov 27, 2012 1:56 AM by odie_63 RSS

Need to read a XML file and store multilingual data

RameshkumarT Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points