2 Replies Latest reply: Jun 11, 2014 5:07 AM by j.vkleeff RSS

    Concatenate nested XML tags

    j.vkleeff

      Hello,

       

      I'm looking for a solution to concatenate duplicate tags in a xml.

      I need only the tags 'tag' of 'type=keyword' in one field in a table.

      The sql statement I use I'm getting six records and I just need one record.

       

      Hope my English writing is not to bad to understand what I mean.

       

      This is a part of the XML.

       

      <?xml version='1.0' encoding='UTF-8'?>

      <list>

      <artikel id='25230245' title='st' publish='true' sender=''>

        <categorieen>

          <categorie naam='Staking' externalid='Economie/Staking' />

        </categorieen>

        <geoinfo latitude='52.3936' longitude='6.142065' zoom='15' />

        <tags>

          <tag name='Overijssel/Raalte/Raalte' type='location' />

          <tag name='Overijssel/Deventer/Deventer' type='location' />

          <tag name='Overijssel/Olst-Wijhe/Wijhe' type='location' />

          <tag name='cao' type='keyword' />

          <tag name='vleeswarensector' type='keyword' />

          <tag name='seniorenregeling' type='keyword' />

          <tag name='fnv bondgenoten' type='keyword' />

          <tag name='werkonderbreking' type='keyword' />

          <tag name='Zwanenburg' type='keyword' />

          <tag name='staking' type='keyword' />

          <tag name='Stegeman' type='keyword' />

          <tag name='Lüpack' type='keyword' />

          <tag name='cnv vakmensen' type='keyword' />

        </tags>

        <publicatiedatum datum='1400663902374' datumstr='2014-05-21T11:18:00+0200' />

        <wijzigingsdatum datum='1400663902374' datumstr='2014-05-21T11:18:00+0200' />

        <printdatum datumstr='22-05-2014' />

        <bestemmingen>

          <bestemming channel='web' site='ST' page='Regio/Deventer/' primary='false' />

          <bestemming channel='web' site='ST' page='Regio/Olst-Wijhe/' primary='false' />

          <bestemming channel='web' site='ST' page='Regio/Raalte/' primary='true' />

        </bestemmingen>

        <bron naam='Benny Koerhuis' />

        <beelden />

      </artikel>

      </list>

       

      And this is my sql statement (part of a plsql procedure).

       

      SELECT decode(upper(t1.publish), 'TRUE', 'Y', 'N')

                   , trunc(to_date(substr(replace(t1.pdate, 'T', ' '), 1, 19), 'YYYY-MM-DD HH24:MI:SS')) pdate

                   , to_date(substr(replace(t1.flitstijd, 'T', ' '), 1, 19), 'YYYY-MM-DD HH24:MI:SS') flitstijd

                   , t1.naam_flitser

                   , t1.sectie

                   , t1.titel

                   , t1.soort

                   , t2.trefwoorden

              FROM flits_berichten_clob t1

              , XMLTABLE('//test/artikel'

                  PASSING XMLTYPE (t1.xml_file)

                  COLUMNS publish         VARCHAR2(10)  PATH '@publish'

                         ,pdate           VARCHAR2(25)  PATH 'publicatiedatum/@datumstr'

                         ,flitstijd       VARCHAR2(25)  PATH 'wijzigingsdatum/@datumstr'

                         ,naam_flitser    VARCHAR2(25)  PATH 'bron/@naam'

                         ,sectie          VARCHAR2(255) PATH 'bestemmingen/bestemming[@primary="true"]/@page'

                         ,titel           VARCHAR2(10)  PATH 'bestemmingen/bestemming[@primary="true"]/@site'

                         ,soort           VARCHAR2(10)  PATH 'bestemmingen/bestemming[@primary="true"]/@channel'

                         ,tags            XMLTYPE       PATH 'tags') t1

              , XMLTABLE('/tags/tag[@type="keyword"]'

                  PASSING t1.tags

                  COLUMNS trefwoorden     VARCHAR2(255) PATH '@name') t2

              WHERE id = 15;

       

      This is the result.

       

      Y21-05-201421-05-2014 11:18:00Benny KoerhuisRegio/Raalte/STwebwerkonderbreking
      Y21-05-201421-05-2014 11:18:00Benny KoerhuisRegio/Raalte/STwebZwanenburg
      Y21-05-201421-05-2014 11:18:00Benny KoerhuisRegio/Raalte/STwebstaking
      Y21-05-201421-05-2014 11:18:00Benny KoerhuisRegio/Raalte/STwebStegeman
      Y21-05-201421-05-2014 11:18:00Benny KoerhuisRegio/Raalte/STwebLüpack
      Y21-05-201421-05-2014 11:18:00Benny KoerhuisRegio/Raalte/STwebcnv vakmensen

       

      Is there a way to concatenate the results from the last column?

      It result should be 'werkonderbreking, Zwanenburg, staking, Stegeman, Lüpack, cnv vakmensen' so I get one record instead of six.

       

      Thanks in advance.