5 Replies Latest reply on Oct 8, 2018 3:55 AM by M Prasad-Oracle

    How to split comma separated values in multiple elements.

    M Prasad-Oracle

      Hi,

       

       

      I have below xml and wanted to split the comma separated values into xml elements.

      Any pointers would be helpful.

       

       

      <struct>

          <member>

              <name>tcp_destinations</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>10.244.202.35:1234</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

          <member>

              <name>udp_destinations</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>10.244.202.39,10.244.202.40</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

          <member>

              <name>categories</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>debug</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

          <member>

              <name>max_message_length</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>1024</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

      </struct>

       

       

       

       

      OUTPUT. -->

       

      <struct>

          <member>

              <name>tcp_destinations</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>10.244.202.35:1234</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

          <member>

              <name>udp_destinations</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>10.244.202.39</string>

                          </value>

                          <value>

                              <string>10.244.202.40</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

          <member>

              <name>categories</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>debug</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

          <member>

              <name>max_message_length</name>

              <value>

                  <array>

                      <data>

                          <value>

                              <string>1024</string>

                          </value>

                      </data>

                  </array>

              </value>

          </member>

      </struct>

        • 1. Re: How to split comma separated values in multiple elements.
          M Prasad-Oracle

          Basically , table data is as below and I need to generate output in above format if comma seperated list present

           

          POS      KEY                               value

          1           tcp_destinations           10.244.202.35:1234

          2           udp_destinations           10.244.202.40

          2           udp_destinations           10.244.202.39

          3           categories                      debug

          4           max_message_length     1024

          • 2. Re: How to split comma separated values in multiple elements.
            M Prasad-Oracle

            Original table data is as below , I have splitted it in rows to generate xml.


            1 tcp_destinations 10.244.202.35:1234

            2 udp_destinations 10.244.202.39,10.244.202.40

            3 categories debug

            4 max_message_length 1024

            • 3. Re: How to split comma separated values in multiple elements.
              mNem
              SQL> set echo on
              SQL> with t (pos, key, val) as 
              (  
              select 1 ,'tcp_destinations'  , '10.244.202.35:1234'          from dual union all
              select 2 ,'udp_destinations'  , '10.244.202.39,10.244.202.40' from dual union all
              select 3 ,'categories'        , 'debug'                       from dual union all
              select 4 ,'max_message_length', '1024'                        from dual
              )
              select 
              xmlserialize(content -- remove later, for debugging only
              xmlelement("struct",
                xmlagg
                (
                    xmlquery
                    ( 
                      '
                      let $k := <member>
                                  <name>{$key}</name>
                                  <value>
                                    <array>
                                      <data>{
                                         for $i in if(contains($val, ",")) then ora:tokenize($val, ",") else $val 
                                         return 
                                          <value>
                                            <string>{$i}</string>
                                          </value>
                                     }
                                     </data>
                                    </array>
                                  </value>
                                </member>
                      return $k
                      '
                      passing key as "key", val as "val"
                      returning content
                    ) 
                ) 
              )
              as clob indent ) --  remover later, for debugging only
              
              xmloutput
              
              from t
              
              XMLOUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
              ---------------------------------------------------------
              <struct>
                <member>
                  <name>tcp_destinations</name>
                  <value>
                    <array>
                      <data>
                        <value>
                          <string>10.244.202.35:1234</string>
                        </value>
                      </data>
                    </array>
                  </value>
                </member>
                <member>
                  <name>udp_destinations</name>
                  <value>
                    <array>
                      <data>
                        <value>
                          <string>10.244.202.39</string>
                        </value>
                        <value>
                          <string>10.244.202.40</string>
                        </value>
                      </data>
                    </array>
                  </value>
                </member>
                <member>
                  <name>categories</name>
                  <value>
                    <array>
                      <data>
                        <value>
                          <string>debug</string>
                        </value>
                      </data>
                    </array>
                  </value>
                </member>
                <member>
                  <name>max_message_length</name>
                  <value>
                    <array>
                      <data>
                        <value>
                          <string>1024</string>
                        </value>
                      </data>
                    </array>
                  </value>
                </member>
              </struct>
              
              
              
              
              • 4. Re: How to split comma separated values in multiple elements.
                mNem

                Just to show the interim step, I have commented out the xmlagg function and the final root element.

                 

                SQL> with t (pos, key, val) as 
                (  
                select 1 ,'tcp_destinations'  , '10.244.202.35:1234'          from dual union all
                select 2 ,'udp_destinations'  , '10.244.202.39,10.244.202.40' from dual union all
                select 3 ,'categories'        , 'debug'                       from dual union all
                select 4 ,'max_message_length', '1024'                        from dual
                )
                select t.*,
                xmlserialize(content -- remove later, for debugging only
                --xmlelement("struct",
                --  xmlagg
                --  (
                      xmlquery
                      ( 
                        '
                        let $k := <member>
                                    <name>{$key}</name>
                                    <value>
                                      <array>
                                        <data>{
                                           for $i in if(contains($val, ",")) then ora:tokenize($val, ",") else $val 
                                           return 
                                            <value>
                                              <string>{$i}</string>
                                            </value>
                                       }
                                       </data>
                                      </array>
                                    </value>
                                  </member>
                        return $k
                        '
                        passing key as "key", val as "val"
                        returning content
                      ) 
                --  ) 
                --)
                as clob indent ) --  remove later, for debugging only
                
                xmloutput
                
                from t
                
                       POS KEY                VAL                         XMLOUTPUT                                        
                ---------- ------------------ --------------------------- --------------------------------------------------
                         1 tcp_destinations   10.244.202.35:1234          <member>
                                                                            <name>tcp_destinations</name>
                                                                            <value>
                                                                              <array>
                                                                                <data>
                                                                                  <value>
                                                                                    <string>10.244.202.35:1234</string>
                                                                                  </value>
                                                                                </data>
                                                                              </array>
                                                                            </value>
                                                                          </member>
                                                                          
                
                         2 udp_destinations   10.244.202.39,10.244.202.40 <member>
                                                                            <name>udp_destinations</name>
                                                                            <value>
                                                                              <array>
                                                                                <data>
                                                                                  <value>
                                                                                    <string>10.244.202.39</string>
                                                                                  </value>
                                                                                  <value>
                                                                                    <string>10.244.202.40</string>
                                                                                  </value>
                                                                                </data>
                                                                              </array>
                                                                            </value>
                                                                          </member>
                                                                          
                
                         3 categories         debug                       <member>
                                                                            <name>categories</name>
                                                                            <value>
                                                                              <array>
                                                                                <data>
                                                                                  <value>
                                                                                    <string>debug</string>
                                                                                  </value>
                                                                                </data>
                                                                              </array>
                                                                            </value>
                                                                          </member>
                                                                          
                
                         4 max_message_length 1024                        <member>
                                                                            <name>max_message_length</name>
                                                                            <value>
                                                                              <array>
                                                                                <data>
                                                                                  <value>
                                                                                    <string>1024</string>
                                                                                  </value>
                                                                                </data>
                                                                              </array>
                                                                            </value>
                                                                          </member>
                                                                          
                
                
                

                 

                and then, aggregating the member elements followed by including them under the struct root element.

                • 5. Re: How to split comma separated values in multiple elements.
                  M Prasad-Oracle

                  Thanks mNem. You are great!.

                  i was trying for some time and it was not working as expected.