Help with XSLT Transformation

I was hoping I could get a little assistance with an XSLT transform. I can't seem to get it right.

Here is a sample of the source xml document:

    <?xml version="1.0" encoding="UTF-8"?>
    <Locations>
    <header>
       <location>Location Field</location>
       <job_function>Job Function Field</job_function>
       <count>Count</count>
    </header>
   <data>
      <location>2177</location>
      <job_function>ADM</job_function>
      <count>1</count>
   </data>
   <data>
        <location>2177</location>
      <job_function>OPS</job_function>
      <count>1</count>
   </data>
   <data>
      <location>2177</location>
      <job_function>SLS</job_function>
      <count>5</count>
   </data>
   <data>
      <location>2179</location>
      <job_function>ADM</job_function>
      <count>1</count>
   </data>
   <data>
      <location>2179</location>
      <job_function>SEC</job_function>
      <count>1</count>
   </data>
  </Locations>

I want to transform it into the following format:

&开发者_运维知识库lt;Locations>
   <data>
    <PeopleSoftID>2177</PeopleSoftID>
    <ADM>1</ADM>
    <OPS>1</OPS>
    <SLS>5</SLS>
    <TotalCount>7</TotalCount>
   </data>
   <data>
    <PeopleSoftID>2179</PeopleSoftID>
    <ADM>1</ADM>
    <SEC>1</SEC>
    <TotalCount>2</TotalCount>
   </data>
 </Locations>

So basically, as you can see in the sample source document there are multiple elements that have the same value. In the destination document, there should now only be one record (<PeopleSoftID> element) per <location> element value in the source document. Since there were 3 <location> elements with the value of 2177, the destination document now has just 1 <PeopleSoftID> element that contains that value. The value of the <job_function> element in the source document becomes an element in the destination document. The value of that new element ends up being the sibling value of the <count> element from the source document. The <TotalCount> element in the destination document is the SUM of the values of all the new elements that are generated from the source <job_function> element.

I hope that explanation did not confuse anybody =).

I am a little new to XSLTs still so I am having trouble getting the logic right on this.

I can only use XSLT 1.0 too.

If I did not provide enough information let me know, and I will try to provide more as soon as I am able.

Thanks guys!

---

Read up on xsl:key and grouping with the Muenchian Method

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes" />

    <!--Group the data elements by their location values -->
    <xsl:key name="data-by-location" match="data" use="location" />

    <xsl:template match="Locations">
        <xsl:copy>
            <!--Get a distinct list of location values, 
                using the Muenchian Method -->
            <xsl:for-each
                select="data[generate-id() =
                        generate-id(key('data-by-location', location)[1])]">
                <xsl:copy>
                    <PeopleSoftID>
                        <xsl:value-of select="location"/>
                    </PeopleSoftID>

                    <!--For every data element matching this location... -->
                    <xsl:for-each select="key('data-by-location',location)">
                        <!--Create an element using the job_function 
                            as the element name -->
                        <xsl:element name="{job_function}">
                            <!--The value of the count element
                                as the value of the generated element-->
                            <xsl:value-of select="count"/>
                        </xsl:element>
                    </xsl:for-each>

                    <TotalCount>
                        <!--calculate the sum of all the count element values 
                            for this location -->
                        <xsl:value-of select="sum(key('data-by-location',
                                                          location)/count)"/>
                    </TotalCount>
                </xsl:copy>
            </xsl:for-each>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>