Transforming XML structure - pointers needed

I need to transform an XML file.  There is just one "simple" change, but I have no idea how to do it.  What I need is to move the Brand attribute from the Item node to the Offer node.

It sounds simple at first, but the result has more Offer nodes than the original.  See the below example for details, but PLEASE copy it to an editor with color coding, it just makes it so much easier to read.

I am not expecting someone to come up with complete working code that does this for me - what I am hoping for is some pointers on how to go about it.  Is it even feasible?  Should I use XSLT, XQuery or something else?  What keywords in XSLT/XQuery/something else might I want to look at?

(I have never used XSLT nor XQuery before, but have done some programming in other areas.)
### Existing: ###

<File Name='Offers'>
	<Offer ID='1234' DT='2010-04-14 17:04:00' Location='Marple Road' Clerk='Joe' Customer ='Jane'>
		<Product Type='Hardware'>
			<Item Brand='Ikea' ItemType='1' PriceInstore='100' PriceDelivered='110' StockDT='2010-03-11 07:40:30'/>
			<Item Brand='Ikea' ItemType='1' PriceInstore='90' PriceDelivered='108' StockDT='2010-03-11 07:40:35'/>
			<Item Brand='Powell' ItemType='1' PriceInstore='90' PriceDelivered='102' StockDT='2010-03-15 10:33:05'/>
			<Item Brand='Powell' ItemType='2' PriceInstore='95' PriceDelivered='95' StockDT='2010-03-16 11:10:00'/>
		</Product>
		<Product Type='Service'>
			<Item Brand='BestValueInc' ItemType='1' PriceInstore='150' PriceDelivered='200' StockDT='2010-03-11 07:40:30'/>
			<Item Brand='Thinktank' ItemType='2' PriceInstore='0' PriceDelivered='100' StockDT='2010-03-11 07:40:30'/>
		</Product>
	</Offer>
	<Offer ID='1235' DT='2010-04-14 17:30:00' Location='Marple Road' Clerk='Joe' Customer ='Louise'>
		<!-- Similar -->
	</Offer>
</File>



### Desired: ###

<File Name='Offers'>
	<Offer ID='1234' Brand='Ikea' DT='2010-04-14 17:04:00' Location='Marple Road' Clerk='Joe' Customer='Jane'>
		<Product Type='Hardware'>
			<Item ItemType='1' PriceInstore='100' PriceDelivered='110' StockDT='2010-03-11 07:40:30'/>
			<Item ItemType='1' PriceInstore='90' PriceDelivered='108' StockDT='2010-03-11 07:40:35'/>
		</Product>
	</Offer>
	<Offer ID='1234' Brand='Powell' DT='2010-04-14 17:04:00' Location='Marple Road' Clerk='Joe' Customer='Jane'>
		<Product Type='Hardware'>
			<Item ItemType='1' PriceInstore='90' PriceDelivered='102' StockDT='2010-03-15 10:33:05'/>
			<Item ItemType='2' PriceInstore='95' PriceDelivered='95' StockDT='2010-03-16 11:10:00'/>
		</Product>
	</Offer>
	<Offer ID='1234' Brand='BestValueInc' DT='2010-04-14 17:04:00' Location='Marple Road' Clerk='Joe' Customer='Jane'>
		<Product Type='Service'>
			<Item ItemType='1' PriceInstore='150' PriceDelivered='200' StockDT='2010-03-11 07:40:30'/>
		</Product>
	</Offer>
	<Offer ID='1234' Brand='Thinktank' DT='2010-04-14 17:04:00' Location='Marple Road' Clerk='Joe' Customer='Jane'>
		<Product Type ='Service'>
			<Item ItemType='2' PriceInstore='0' PriceDelivered='100' StockDT='2010-03-11 07:40:30'/>
		</Product>
	</Offer>
	<Offer ID='1235' Brand='Powell' DT='2010-04-14 17:30:00' Location='Marple Road' Clerk='Joe' Customer='Louise'>
		<!-- Similar -->
	</Offer>
</File>

Open in new window

Joe5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gertone (Geert Bormans)Information ArchitectCommented:
Hi, I would not hesitate and go straight for XSLT on this task

For the grouping you can use Muenchian, explained here
http://www.jenitennison.com/xslt/grouping/muenchian.xml

It is not too much a task, so I did it for you,
the following XSLT will do exactly what you need

cheers

Geert
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:strip-space elements="*"/>
    <xsl:output indent="yes"/>
    <xsl:key name="brand" match="Item" use="concat(@Brand, '-', parent::Product/@Type, '-' , ancestor::Offer/@ID)"/>
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>
    <xsl:template match="Offer">
        <xsl:for-each select="Product/Item[generate-id() = generate-id(key('brand', concat(@Brand, '-', parent::Product/@Type, '-' , ancestor::Offer/@ID))[1])]">
            <Offer Brand="{@Brand}">
                <xsl:copy-of select="ancestor::Offer/@*"/>
                <Product>
                    <xsl:copy-of select="ancestor::Product/@*"/>
                    <xsl:for-each select="key('brand', concat(@Brand, '-', parent::Product/@Type, '-' , ancestor::Offer/@ID))">
                        <Item>
                            <xsl:copy-of select="@*[not(name() = 'Brand')]"/>
                        </Item>
                    </xsl:for-each>
                </Product>
            </Offer>
        </xsl:for-each>
        
    </xsl:template>
</xsl:stylesheet>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe5Author Commented:
WOW, amazing... complete working code!  Saved me a lot of time plus I can learn from examining the code and the link.  This is beyond perfect, thank you so much!
Gertone (Geert Bormans)Information ArchitectCommented:
welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.