XML Transform moving data around using xslt

BivsAi · February 23rd, 2012, 12:41 PM

HI.. I am trying to find out how to do a XML transform from a report result file that I recieve into the format that I need. Here is an excerpt of the original XML.

Code:

<?xml version="1.0"?><Report xsi:schemaLocation="" reportTimestamp="" name="" localizedName="Report" xmlns="" xmlns:xsd="" xmlns:xsi=""><HeaderData>
<Header name="Date/Time Generated" localizedName="Date/Time Generated">
<Val xsi:type="xsd:string">02/22/2012 15:51 Eastern Standard Time (GMT-0500)</Val></Header>
<Header name="Query" localizedName="Query"><Val xsi:type="xsd:string">220003</Val></Header>
<Header name="Creator" localizedName="Creator"><Val xsi:type="xsd:string"></Val></Header>
<Header name="Report Type" localizedName="Report Type"><Val xsi:type="xsd:string">Report</Val></Header>
<Header name="NetworkName" localizedName="Network Name"><Val xsi:type="xsd:string">Corp</Val></Header>
<Header name="Date Range" localizedName="Date Range"><Val xsi:type="xsd:string">02/01/12 - 04/30/12</Val></Header></HeaderData>
<FooterData></FooterData><ReportData name="By Site" localizedName="Report">

<ColumnHeaders>
<ColumnHeaderGroup name="ReportFields" localizedName="Report Fields">
<ColumnHeader columnDataType="xsd:string" name="Location" localizedName="Location"></ColumnHeader>
<ColumnHeader columnDataType="xsd:string" name="SalesName" localizedName="Sales Name"></ColumnHeader>
<ColumnHeader columnDataType="xsd:string" name="Category" localizedName="Category"></ColumnHeader>
<ColumnHeader columnDataType="xsd:string" name="Name" localizedName="Name"></ColumnHeader>
<ColumnHeader columnDataType="xsd:string" name="Sizes" localizedName="Sizes"></ColumnHeader>
<ColumnHeader columnDataType="xsd:string" name="Department" localizedName="Department"></ColumnHeader>
<ColumnHeader columnDataType="xsd:integer" name="Id" localizedName="ID"></ColumnHeader></ColumnHeaderGroup>
<ColumnHeaderGroup name="FEB" localizedName="February"><ColumnHeader columnDataType="xsd:float" name="GrossContractedRevenue" localizedName="Contracted Revenue(Gross)"></ColumnHeader></ColumnHeaderGroup>
<ColumnHeaderGroup name="MAR" localizedName="March"><ColumnHeader columnDataType="xsd:float" name="GrossContractedRevenue" localizedName="Contracted Revenue(Gross)"></ColumnHeader></ColumnHeaderGroup>
<ColumnHeaderGroup name="APR" localizedName="April"><ColumnHeader columnDataType="xsd:float" name="GrossContractedRevenue" localizedName="Contracted Revenue(Gross)"></ColumnHeader></ColumnHeaderGroup>
<ColumnHeaderGroup name="TotalValue" localizedName="Total"><ColumnHeader columnDataType="xsd:float" name="GrossContractedRevenue" localizedName="Contracted Revenue(Gross)"></ColumnHeader></ColumnHeaderGroup>
</ColumnHeaders>
<DataSet xmlns="http://www.doubleclick.net/DART/Report" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Row rowNum="1">
<Column name="Location"><Val xsi:type="xsd:string">Test Location 1</Val></Column>
<Column name="SalesName"><Val xsi:type="xsd:string">Test Sales Name 1</Val></Column>
<Column name="Category"><Val xsi:type="xsd:string">Photo</Val></Column>
<Column name="Name"><Val xsi:type="xsd:string">Test Company 1</Val></Column>
<Column name="Sizes"><Val xsi:type="xsd:string">800x600</Val></Column>
<Column name="Department"><Val xsi:type="xsd:string" xsi:nil="true"></Val></Column>
<Column name="Id"><Val xsi:type="xsd:integer">1000</Val></Column>
<Column name="FEB_GrossContractedRevenue"><Val xsi:type="xsd:float">350.00</Val></Column>
<Column name="MAR_GrossContractedRevenue"><Val xsi:type="xsd:float">450.00</Val></Column>
<Column name="APR_GrossContractedRevenue"><Val xsi:type="xsd:float">250.00</Val></Column>
<Column name="GrossContractedRevenue"><Val xsi:type="xsd:float">1100.00</Val></Column>
</Row>
<Row rowNum="2">
<Column name="Location"><Val xsi:type="xsd:string">Test Location 2</Val></Column>
<Column name="SalesName"><Val xsi:type="xsd:string">Test Sales Name 2</Val></Column>
<Column name="Category"><Val xsi:type="xsd:string">Photo</Val></Column>
<Column name="Name"><Val xsi:type="xsd:string">Test Company 2</Val></Column>
<Column name="Sizes"><Val xsi:type="xsd:string">400x600</Val></Column>
<Column name="Department"><Val xsi:type="xsd:string" xsi:nil="true"></Val></Column>
<Column name="Id"><Val xsi:type="xsd:integer">1500</Val></Column>
<Column name="FEB_GrossContractedRevenue"><Val xsi:type="xsd:float">100.00</Val></Column>
<Column name="MAR_GrossContractedRevenue"><Val xsi:type="xsd:float">200.00</Val></Column>
<Column name="APR_GrossContractedRevenue"><Val xsi:type="xsd:float">300.00</Val></Column>
<Column name="GrossContractedRevenue"><Val xsi:type="xsd:float">600.00</Val></Column>
</Row>
</DataSet>
</ReportData>
</Report>

My goal or objective is to transform this so that the Column Names of Location, Sales Name, Category, Name, Sizes, Department, ID, "Month" and Gross Contracted Revenue" are all headings but then below lists the actual data moving Feb, March and April's data as individual rows under the month column as for the moment it is listed with each month being its own column heading. So under Month you would see the actual month and then beside it under the Gross the actual value. This would cause the Location data to repeat as individual rows as long as there was a month associated with data.

If I could get pointed in the right direction on how I could accomplish this I would be greatly appreciative.

Thanks