5 Replies Latest reply on Mar 19, 2019 4:23 PM by tsuji

    I have a CLOB field with XML data and we need to convert the XML to columns with value concatenation

    user8861986

      Hi there,

      I have an XML with data as below:

      <row>

      <c1>Name1></c1>

      <c2>Name2</c2>

      <c6>Addressline</c6>

      <c6 m=1 s=2>Addressline 2 </c6>

      <c6 m=1 s=1>Addressline 3 </c6>

       

      <c6 m=2 s=1>Addressline 4</c6>

      <c6 m=2 s=2>Addressline 5</c6>

      <c6 m=2 s=3>Addressline 6</c6>

      </row>.

       

      I require the following output, but unfortuantely I could not find any help anywhere.

       

      Fld 1        fld2        fld3

      Name1    Name2  Addressline$Addressline 2$Addressline 3£Addressline 4$Addressline 5$Addressline 6

       

      Means the values in "m" should be concatenated with $ and both "m" should be seprated with £

       

      Thanks

       

      Regards,

      Wasif Hassan

        • 1. Re: I have a CLOB field with XML data and we need to convert the XML to columns with value concatenation
          cormaco

          I made a few corrections to your xml example, here is a solution:

          with xmldata(xmlfile) as (
          select xmltype(
          '<row>
          <c1>Name1</c1>
          <c2>Name2</c2>
          <c6>Addressline</c6>
          <c6 m="1" s="2">Addressline 2</c6>
          <c6 m="1" s="1">Addressline 3</c6>
          
          <c6 m="2" s="1">Addressline 4</c6>
          <c6 m="2" s="2">Addressline 5</c6>
          <c6 m="2" s="3">Addressline 6</c6>
          </row>') from dual)
          select fld1,fld2,c61 || '£' || c62 as fld3
          from xmldata,xmltable(
              '/row'
              passing xmlfile
              columns
                  fld1 varchar2(20)  path 'c1',
                  fld2 varchar2(20)  path 'c2',
                  c61  varchar2(40) path 'string-join(c6[@m="1"],"$")',
                  c62  varchar2(40) path 'string-join(c6[@m="2"],"$")'
          )
          

           

          FLD1                 FLD2                 FLD3                                                                             
          -------------------- -------------------- ---------------------------------------------------------------------------------
          Name1                Name2                Addressline 2$Addressline 3£Addressline 4$Addressline 5$Addressline              
          
          
          • 2. Re: I have a CLOB field with XML data and we need to convert the XML to columns with value concatenation
            user8861986

            Thanks a lot for your help cormaco.

             

            Can this be done dynamically, I mean you have specified string join for m=1 & m=2 separately and then concatenated the output of both m in upper level. Unfortunately, we dont know how many m values we will have.

             

            Thanks.

            • 3. Re: I have a CLOB field with XML data and we need to convert the XML to columns with value concatenation
              cormaco

              Here is a solution for an unlimited number of m's:

              with xmldata(xmlfile) as (
              select xmltype(
              '<row>
              <c1>Name1</c1>
              <c2>Name2</c2>
              <c6>Addressline</c6>
              <c6 m="1" s="1">Addressline 2</c6>
              <c6 m="1" s="2">Addressline 3</c6>
              
              <c6 m="2" s="1">Addressline 4</c6>
              <c6 m="2" s="2">Addressline 5</c6>
              <c6 m="2" s="3">Addressline 6</c6>
              </row>') from dual)
              select fld1,fld2,c6 || '$' || listagg(l,'£') within group (order by m) as fld3 from (
                  select fld1,fld2,c6,m,listagg(adr,'$') within group (order by s) l
                  from xmldata,xmltable(
                      '/row'
                      passing xmlfile
                      columns
                          fld1 varchar2(20) path 'c1',
                          fld2 varchar2(20) path 'c2',
                          c6   varchar2(20) path 'c6[not(@m)]', 
                          c6m   xmltype      path 'c6[@m]'
                  ),
                  xmltable(
                      'c6'
                      passing c6m
                      columns
                          m    number(10)   path '@m',
                          s    number(10)   path '@s',
                          adr  varchar2(20) path '.'
                  )
                  group by fld1,fld2,c6,m
              )
              group by fld1,fld2,c6
              
              
              
              
              
              FLD1                 FLD2                 FLD3                                                                                      
              -------------------- -------------------- ------------------------------------------------------------------------------------------
              Name1                Name2                Addressline$Addressline 2$Addressline 3£Addressline 4$Addressline 5$Addressline 6         
              
              
              • 5. Re: I have a CLOB field with XML data and we need to convert the XML to columns with value concatenation
                tsuji

                I find it pretty impressive, if not overly dramatic, to blend 11gR2+ function listagg() into this context which can be taken as a view of an xmltype object... Allow me to propose an alternative approach which is based on the idea of 1) making the view rendering light-weight; 2) isolation of responsibility or centralizing the xml functionality into its own place using the xml technologies. This advantage of it is to build into it a kind of flexibility of the ever varying needs on the xml part... otherwise if the xmltype object needs some undetermined change, the impact on the view rendering will be amplified.

                 

                I would suggest that an xslt approach done according to the need before the sql viewing of the xmltype object is scripted provides such isolation and flexibility. Here is how I would approach the needed grouping...

                with gtt_(object_value) as (
                select xmltransform(
                xmltype('<row>
                    <c1>Name1</c1>
                    <c2>Name2</c2>
                    <c6>Address</c6>
                    <c6 m="1" s="2">Address2</c6>
                    <c6 m="1" s="1">Address3</c6>
                
                    <c6 m="2" s="1">Address4</c6>
                    <c6 m="2" s="2">Address5</c6>
                    <c6 m="2" s="3">Address6</c6>
                </row>'),
                xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                <xsl:output method="xml" encoding="utf-8" omit-xml-declaration="yes" />
                <xsl:strip-space elements="*" />
                <xsl:key name="c6m" match="c6" use="@m" />
                <xsl:template match="*|@*|text()">
                    <xsl:copy>
                        <xsl:apply-templates select="*|@*|text()" />
                    </xsl:copy>
                </xsl:template>
                <xsl:template match="/">
                    <xsl:apply-templates select="*" />
                </xsl:template>
                <xsl:template match="*[not(parent::*)]">
                    <xsl:copy>
                        <xsl:apply-templates select="*[name()!=''c6'']|@*|text()" />
                        <c6m>
                            <xsl:variable name="result">
                                <xsl:for-each select="c6[generate-id()=generate-id(key(''c6m'',@m)[1])]">
                                    <xsl:call-template name="listagg">
                                        <xsl:with-param name="m" select="@m" />
                                        <xsl:with-param name="sep" select="''$''" />
                                    </xsl:call-template>
                                    <xsl:if test="position()!=last()">
                                        <xsl:value-of select="''£''" />
                                    </xsl:if>
                                </xsl:for-each>
                            </xsl:variable>
                            <xsl:value-of select="$result" />
                        </c6m>
                    </xsl:copy>
                </xsl:template>
                <xsl:template name="listagg">
                    <xsl:param name="m" />
                    <xsl:param name="sep" select="''$''" />
                    <xsl:for-each select="key(''c6m'', @m)">
                        <xsl:value-of select="." />
                        <xsl:if test="position()!=last()">
                            <xsl:value-of select="$sep" />
                        </xsl:if>
                    </xsl:for-each>
                </xsl:template>
                </xsl:stylesheet>
                ')
                )
                from dual)
                select c1, c2, c6m
                from gtt_ x, xmltable(
                '/row'
                passing x.object_value
                columns
                    c1 varchar2(20)  path 'c1',
                    c2 varchar2(20) path 'c2',
                    c6m varchar2(60) path 'c6m'
                );
                /
                

                and the result, slightly edited the look, would essentially be the same, of course,

                C1                  C2                      C6M                                                  

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

                Name1          Name2               Address2$Address3£Address4$Address5$Address6