XML file flat to tree structure grouping fields

0
votes

I am new to XSL transformations and I want to transform the XML A into XML B .Any suggestions to achieve this in XSLT or xquery. I tried using for-each-group on msisdn but still unable to achieve it.Any sample code to start with

XML A:

<Output>
<response>
    <msisdn>27832007509</msisdn>
    <bearer>DATA</bearer>
    <comb_charged_units>4223777792</comb_charged_units>
    <comb_cnt>288</comb_cnt>
    <shared_voice_duration>0</shared_voice_duration>
    <shared_data_volume>8728127</shared_data_volume>
    <shared_voice_cnt>0</shared_voice_cnt>
    <shared_data_cnt>89</shared_data_cnt>
</response>
<response>
    <msisdn>27832007509</msisdn>
    <bearer>VOICE</bearer> 
    <comb_charged_units>477792</comb_charged_units>
    <comb_cnt>281</comb_cnt>
    <shared_voice_duration>17268127877</shared_voice_duration>
    <shared_data_volume>0</shared_data_volume>
    <shared_voice_cnt>87887</shared_voice_cnt>
    <shared_data_cnt>0</shared_data_cnt>
</response>
<response>
    <msisdn>27832229588</msisdn>
    <bearer>DATA</bearer>
    <comb_charged_units>11898</comb_charged_units>
    <comb_cnt>33</comb_cnt>
    <shared_voice_duration>0</shared_voice_duration>
    <shared_data_volume>3445</shared_data_volume>
    <shared_voice_cnt>0</shared_voice_cnt>
    <shared_data_cnt>78</shared_data_cnt>
</response>
<response>
    <msisdn>27832229588</msisdn>
    <bearer>VOICE</bearer>
    <comb_charged_units>45</comb_charged_units>
    <comb_cnt>12</comb_cnt>
    <shared_voice_duration>789</shared_voice_duration>
    <shared_data_volume>0</shared_data_volume>
    <shared_voice_cnt>23</shared_voice_cnt>
    <shared_data_cnt>0</shared_data_cnt>
</response>

XML B:

<UsageHistoryResponse>
<usage>
    <MSISDN>27832007509</MSISDN>
    <CombinedUsage>
        <usage_info>
            <bearer>DATA</bearer>
            <bearer_units>288</bearer_units>
            <bearer_units_uom>Sessions</bearer_units_uom>
            <bearer_usage>4223777792</bearer_usage>
            <bearer_usage_uom>bytes</bearer_usage_uom>
        </usage_info>
        <usage_info>
            <bearer>VOICE</bearer>
            <bearer_units>281</bearer_units>
            <bearer_units_uom>Calls</bearer_units_uom>
            <bearer_usage>477792</bearer_usage>
            <bearer_usage_uom>Seconds</bearer_usage_uom>
        </usage_info>
    </CombinedUsage>
    <SharedUsage>
        <usage_info>
            <bearer>DATA</bearer>
            <bearer_units>89</bearer_units>
            <bearer_units_uom>Sessions</bearer_units_uom>
            <bearer_usage>8728127</bearer_usage>
            <bearer_usage_uom>bytes</bearer_usage_uom>
        </usage_info>
        <usage_info>
            <bearer>VOICE</bearer>
            <bearer_units>87887</bearer_units>
            <bearer_units_uom>Calls</bearer_units_uom>
            <bearer_usage>17268127877</bearer_usage>
            <bearer_usage_uom>Seconds</bearer_usage_uom>
        </usage_info>
    </SharedUsage>
</usage>
<usage>
    <MSISDN>27832229588</MSISDN>
    <CombinedUsage>
        <usage_info>
            <bearer>DATA</bearer>
            <bearer_units>33</bearer_units>
            <bearer_units_uom>Sessions</bearer_units_uom>
            <bearer_usage>11898</bearer_usage>
            <bearer_usage_uom>bytes</bearer_usage_uom>
        </usage_info>
        <usage_info>
            <bearer>VOICE</bearer>
            <bearer_units>12</bearer_units>
            <bearer_units_uom>Calls</bearer_units_uom>
            <bearer_usage>45</bearer_usage>
            <bearer_usage_uom>Seconds</bearer_usage_uom>
        </usage_info>
    </CombinedUsage>
    <SharedUsage>
        <usage_info>
            <bearer>DATA</bearer>
            <bearer_units>78</bearer_units>
            <bearer_units_uom>Sessions</bearer_units_uom>
            <bearer_usage>3445</bearer_usage>
            <bearer_usage_uom>bytes</bearer_usage_uom>
        </usage_info>
        <usage_info>
            <bearer>VOICE</bearer>
            <bearer_units>23</bearer_units>
            <bearer_units_uom>Calls</bearer_units_uom>
            <bearer_usage>789</bearer_usage>
            <bearer_usage_uom>Seconds</bearer_usage_uom>
        </usage_info>
    </SharedUsage>
</usage>

My idea is to group msisdn first and then traverse into fields of it. Below is my attempt

<xsl:template match="/">
    <tns:UsageHistoryResponse>
        <xsl:for-each select="/Output/response">
            <tns:usage>
                <xsl:for-each-group select="." group-by="ns0:msisdn">
                    <tns:MSISDN>
                    <xsl:value-of select="current-grouping-key()"/></tns:MSISDN>
                </xsl:for-each-group>
            </tns:usage>
        </xsl:for-each>
    </tns:UsageHistoryResponse>
</xsl:template>
1
xmlxsltxqueryxslt-2.0
"I tried using for-each-group on msisdn" Post your attempt so we can fix it, instead of having to write your code for you from scratch. - michael.hor257k
Edited the question - Naveenz Rock

1 Answers

0
votes

Here is a solution that shows how to achieve the grouping and addition of two wrapper elements:

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="@* | node()" mode="#all">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()" mode="#current"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Output">
        <UsageHistoryResponse>         
            <xsl:for-each-group select="response" group-by="msisdn">
              <usage>
                <MSISDN>
                    <xsl:value-of select="current-grouping-key()"/>
                </MSISDN>  
                <CombinedUsage>
                    <xsl:apply-templates select="current-group()"/>
                </CombinedUsage>
                <SharedUsage>
                    <xsl:apply-templates select="current-group()" mode="shared"/>
                </SharedUsage>
              </usage>
            </xsl:for-each-group>
        </UsageHistoryResponse> 
    </xsl:template>

    <xsl:template match="response" mode="#default shared">
        <usage_info>
            <xsl:apply-templates mode="#current"/>
        </usage_info>
    </xsl:template>

    <xsl:template match="msisdn" mode="#all"/>

    <xsl:template match="response/*[starts-with(local-name(), 'shared')]"/>

    <xsl:template match="response/*[starts-with(local-name(), 'comb')]" mode="shared"/>

    <xsl:template match="comb_cnt">
        <bearer_units>
            <xsl:apply-templates/>
        </bearer_units>
    </xsl:template>

</xsl:transform>

http://xsltransform.net/bEzjRKb/1 shows it gives 

<?xml version="1.0" encoding="UTF-8"?>
<UsageHistoryResponse>
   <usage>
      <MSISDN>27832007509</MSISDN>
      <CombinedUsage>
         <usage_info>
            <bearer>DATA</bearer>
            <comb_charged_units>4223777792</comb_charged_units>
            <bearer_units>288</bearer_units>
         </usage_info>
         <usage_info>
            <bearer>VOICE</bearer>
            <comb_charged_units>477792</comb_charged_units>
            <bearer_units>281</bearer_units>
         </usage_info>
      </CombinedUsage>
      <SharedUsage>
         <usage_info>
            <bearer>DATA</bearer>
            <shared_voice_duration>0</shared_voice_duration>
            <shared_data_volume>8728127</shared_data_volume>
            <shared_voice_cnt>0</shared_voice_cnt>
            <shared_data_cnt>89</shared_data_cnt>
         </usage_info>
         <usage_info>
            <bearer>VOICE</bearer>
            <shared_voice_duration>17268127877</shared_voice_duration>
            <shared_data_volume>0</shared_data_volume>
            <shared_voice_cnt>87887</shared_voice_cnt>
            <shared_data_cnt>0</shared_data_cnt>
         </usage_info>
      </SharedUsage>
   </usage>
   <usage>
      <MSISDN>27832229588</MSISDN>
      <CombinedUsage>
         <usage_info>
            <bearer>DATA</bearer>
            <comb_charged_units>11898</comb_charged_units>
            <bearer_units>33</bearer_units>
         </usage_info>
         <usage_info>
            <bearer>VOICE</bearer>
            <comb_charged_units>45</comb_charged_units>
            <bearer_units>12</bearer_units>
         </usage_info>
      </CombinedUsage>
      <SharedUsage>
         <usage_info>
            <bearer>DATA</bearer>
            <shared_voice_duration>0</shared_voice_duration>
            <shared_data_volume>3445</shared_data_volume>
            <shared_voice_cnt>0</shared_voice_cnt>
            <shared_data_cnt>78</shared_data_cnt>
         </usage_info>
         <usage_info>
            <bearer>VOICE</bearer>
            <shared_voice_duration>789</shared_voice_duration>
            <shared_data_volume>0</shared_data_volume>
            <shared_voice_cnt>23</shared_voice_cnt>
            <shared_data_cnt>0</shared_data_cnt>
         </usage_info>
      </SharedUsage>
   </usage>
</UsageHistoryResponse>

so some work is left to rename elements which can be simply achieve by adding templates doing that and then you also seem to want to add some data, I am not sure where that data like 

<bearer_usage_uom>Seconds</bearer_usage_uom>

is supposed to be taken from.

As for an explanation: I have used for-each-group as you wanted to and as the tool that XSLT 2.0 offers for grouping, only I have used the right population (select="response") in the right context (match="Output"). If you struggle to understand that then try a tutorial on XSLT 2.0 grouping. As you seem to want to process each item in a group twice, I have then used modes to do that, so to build the CombinedUsage I have used the default mode, to build the SharedUsaged I have used a different mode. The rest is then writing templates for the elements in the input to be mapped to the elements in the output and making sure the elements you don't want in the result for a certain mode are not copied by using empty templates for them.