1 Reply Latest reply on Jun 3, 2019 2:20 PM by cormaco

    Generating the XML using already XML tags/nodes values in column

    user6314670

      Hi All,

      Need your help to generate the XML. I have to generate the XML but two columns already have in XML tags/nodes in varchar2. For me text2 column is causing the issue as it has multiple <Text></Text> tags and unable to use the XMLTYPE.

      Need your help to generate the XML in below format.

       

      --Trying this query to parse.

      select XMLELEMENT("Attribute", XMLATTRIBUTES(attr_value_id as "Id"),

             XMLELEMENT("Name", xmltype(text1)),

             XMLELEMENT("Value", xmltype(text2)))

        FROM MULTI_ATTR MA

       

       

      -- Create table

      create table MULTI_ATTR

      (

        attr_id       NUMBER(9) not null,

        attr_value_id NUMBER(9) not null,

        start_date         DATE not null,

        end_date           DATE not null,

        text1              varchar2(2000),

        text2              varchar2(4000)

      );

       

      insert into MULTI_ATTR (attr_id, attr_value_id, start_date, end_date, text1, text2)

      values (45120, 8828, to_date('01-01-1800', 'dd-mm-yyyy'), to_date('01-01-4000', 'dd-mm-yyyy'), '<Text Language="ENG">Canadian Restricted in Province</Text>',

      '<Text Language="ENG">New</Text><Text Language="SPN">New</Text><Text Language="ITL">New</Text><Text Language="GER">New</Text><Text Language="ZHS">??????</Text><Text Language="JPN">???·?????????</Text><Text Language="ZHT">??????</Text><Text Language="FRE">New</Text>');

       

      insert into MULTI_ATTR (attr_id, attr_value_id, start_date, end_date, text1, text2)

      values (45120, 8832, to_date('01-01-1800', 'dd-mm-yyyy'), to_date('01-01-4000', 'dd-mm-yyyy'), '<Text Language="ENG">Canadian Restricted in Province</Text>',

      '<Text Language="ENG">Newfoundland</Text><Text Language="SPN">Newfoundland</Text><Text Language="ITL">Terranova</Text><Text Language="GER">Neufundland</Text><Text Language="ZHS">纽芬兰</Text><Text Language="JPN">ニューファウンドランド州</Text><Text Language="ZHT">紐芬蘭</Text><Text Language="FRE">Newfoundland</Text>');

       

      insert into MULTI_ATTR (attr_id, attr_value_id, start_date, end_date, text1, text2)

      values (45120, 8836, to_date('01-01-1800', 'dd-mm-yyyy'), to_date('01-01-4000', 'dd-mm-yyyy'), '<Text Language="ENG">Canadian Restricted in Province</Text>',

      '<Text Language="ENG">Scotia</Text><Text Language="SPN">Escocia</Text><Text Language="ITL">Scozia</Text><Text Language="GER">Neuschottland</Text><Text Language="ZHS">新斯科细亚省</Text><Text Language="JPN">ノバスコシア州</Text><Text Language="ZHT">新斯科細亞省</Text><Text Language="FRE">Scotia</Text>');

       

      insert into MULTI_ATTR (attr_id, attr_value_id, start_date, end_date, text1, text2)

      values (45120, 8845, to_date('01-01-1800', 'dd-mm-yyyy'), to_date('01-01-4000', 'dd-mm-yyyy'), '<Text Language="ENG">Canadian Restricted in Province</Text>',

      '<Text Language="ENG">Prince</Text><Text Language="SPN">Prince</Text><Text Language="ITL">Prince</Text><Text Language="GER">Prinz</Text><Text Language="ZHS">爱德华岛</Text><Text Language="JPN">プリンスエドワード島</Text><Text Language="ZHT">愛德華島</Text><Text Language="FRE">Prince</Text>');

       

      Test.PNG

       

      Thanks

        • 1. Re: Generating the XML using already XML tags/nodes values in column
          cormaco

          XMLTYPE only works for XML files with one single root element. In text2 you have XML fragments.

          You can process these XML fragments using XMLPARSE(CONTENT text2):

          SELECT XMLELEMENT ("Attribute",
                             XMLATTRIBUTES (attr_value_id AS "Id"),
                             XMLELEMENT ("Name", xmltype(text1)),
                             XMLELEMENT ("Value",XMLPARSE (CONTENT text2)))
            FROM MULTI_ATTR MA
          

           

          <Attribute Id="8828">
              <Name>
                  <Text Language="ENG">Canadian Restricted in Province</Text>
              </Name>
              <Value>
                  <Text Language="ENG">New</Text>
                  <Text Language="SPN">New</Text>
                  <Text Language="ITL">New</Text>
                  <Text Language="GER">New</Text>
                  <Text Language="ZHS">??????</Text>
                  <Text Language="JPN">???·?????????</Text>
                  <Text Language="ZHT">??????</Text>
                  <Text Language="FRE">New</Text>
              </Value>
          </Attribute>
          

           

          Your chinese and japanese characters didn't survive the copy, but you can see it works like this