3 Replies Latest reply on Jul 9, 2019 5:35 PM by mNem

    Can I separate column values with new line character instead of comma?

    Subramanyam Yalla-Oracle

      I wrote the below query to get data from xml data file.

       

      select   mpditemnbr,threshold

          from  

              temp_tab , 

              xmltable( 

                  '/mpd/mpd-sys-maints[@chapter-id]'

                  passing xmltype(file_data,1) 

                  columns 

                      ata varchar2(100) path 'ata-header',

                      Record xmltype path 'mpd-sys-maint' 

          ), 

          xmltable( 

              'mpd-sys-maint' 

              passing Record 

              columns 

                  RecordTasknbr varchar2(100) path '@tasknbr', 

                  mpditemnbr    varchar2(100) path 'mpd-item-nbr',

                  threshold     VARCHAR2(30)  path 'string-join(interval-thresholds/threshold,",")' 

          )

       

      Output is given below:

      -----------------------------------

      But I want output of threshold values to be separated by new line character instead of comma.

       

      Then threshold should be as per given below instead of 6000 FH,560 DY

      mpditemnbr          threshold

      12-002-01           6000 FH

                                 560 DY

       

      Then this one would be useful in display the values in OAF page.

       

      Please advise how the below line of code changes for new line character instead of comma.

      threshold     VARCHAR2(30)  path 'string-join(interval-thresholds/threshold,","