Mapping and grouping XML to XML

Question

0.00/5 (No votes)

See more:

Hi. I am trying to map the following XML

XML

<order display="2204">
  <productgroup display="Kitchen Units">
    <supplier display="Chippendale">
      <product display="Base End Support Panel  Natural Oak" what="1" pos="1" ordercat="C" />
      <product display="600MM Highline Integrated Dishwasher Fascia" what="1" pos="2" ordercat="C" />
      <product display="600MM Highline Base Unit" what="1" pos="3" ordercat="C" />
      <product display="600MM Highline Integrated Fridge/Freeezer Fascia" what="1" pos="4" ordercat="C" />
      <product display="600MM 3 Drawer Pan Unit" what="1" pos="5" ordercat="S" />
      <product display="600MM Highline Base Unit" what="1" pos="6" ordercat="C" />
      <product display="500MM Highline Base Unit" what="1" pos="7" ordercat="C" />
      <product display="600MM Fridge/Freezer Housing Unit - Type 6" what="1" pos="8" ordercat="C" />
      <product display="600MM Double Oven Housing Unit - Type 2" what="1" pos="9" ordercat="C" />
      <product display="2650 Plinth Natural Oak Inc Seal Strip" what="1" pos="10" ordercat="C" />
      <product display="Worktop 600 Single P/F 4000 Black Brazil" what="1" pos="11" ordercat="C" />
      <product display="30MM Bullnose Sq Cornice 3600MM Natural" what="1" pos="12" ordercat="C" />
      <product display="10Mtr Roll Edging Tape Natural Oak" what="1" pos="13" ordercat="C" />
      <product display="Schock Stnd Inset 1.5 Bowl  Drainer Sink" what="1" pos="14" ordercat="C" />
      <product display="Schock Universal Plumbing Kit 1.5 Bowl" what="1" pos="15" ordercat="C" />
    </supplier>
  </productgroup>
  <productgroup display="Appliances">
    <supplier display="Supplier to be defined">
      <product display="CDA - Integrated Combination Fridge/freezer 70/30 A+ Rated" what="3" pos="1" ordercat="N" />
      <product display="CDA - Integrated Washing Machine" what="3" pos="2" ordercat="N" />
      <product display="CDA - Double Oven Bi Aa Rated Main Oven Touch Control Clock S/steel" what="3" pos="3" ordercat="N" />
      <product display="CDA - Five Burner Front Control 70cm Gas Hob Wok Ffd Stainless Steel" what="3" pos="4" ordercat="N" />
      <product display="CDA - 70cm Chimney Extractor Hood - Stainless Steel" what="3" pos="5" ordercat="N" />
      <product display="CDA - Fully Integrated Dishwasher 60 Cm A++Aa Rated" what="3" pos="6" ordercat="N" />
    </supplier>
  </productgroup>
</order>

Into:-

XML

<?xml version="1.0" encoding="iso-8859-1"?>
<data type="K8 order creation control file">
	<process>3</process>
	<order number="1">
		<orderCategory>C</orderCategory>
		<orderLines>
			<line>
				<what>1</what>
				<pos>2</pos>
				<what>1</what>
				<pos>3</pos>
				<what>1</what>
				<pos>4</pos>
				<what>1</what>
				<pos>6</pos>
				<what>1</what>
				<pos>7</pos>
				<what>1</what>
				<pos>8</pos>
				<what>1</what>
				<pos>9</pos>
				<what>1</what>
				<pos>10</pos>
				<what>1</what>
				<pos>11</pos>
				<what>1</what>
				<pos>12</pos>
				<what>1</what>
				<pos>13</pos>
				<what>1</what>
				<pos>14</pos>
				<what>1</what>
				<pos>15</pos>
			</line>
		</orderLines>
	</order>
	<order number="2">
		<orderCategory>S</orderCategory>
		<orderLines>
			<line>
				<what>1</what>
				<pos>5</pos>
			</line>
		</orderLines>
	</order>
	<order number="3">
		<orderCategory>N</orderCategory>
		<orderLines>
			<line>
				<what>3</what>
				<pos>1</pos>
				<what>3</what>
				<pos>2</pos>
				<what>3</what>
				<pos>3</pos>
				<what>3</what>
				<pos>4</pos>
				<what>3</what>
				<pos>5</pos>
				<what>3</what>
				<pos>6</pos>
			</line>
		</orderLines>
	</order>
</data>

The xsl I have written gets the ordercat part grouped and the counts, but I am failing to get the what and pos parts mapped to the line sections. Any assistance appreciated. I have tried a few different things, but can't seem to get my head around grouping by ordercat and only displaying the what and pos for them.

XML

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:output method="text" encoding="iso-8859-1"/>
	<xsl:output method="xml" indent="yes" omit-xml-declaration="no"/>
	<xsl:key name="level0" match="//product" use="@ordercat"/>  <!-- group -->
	<xsl:key name="level1" match="//product" use="concat(@ordercat, '|', @what)"/>  <!-- group what-->
	<xsl:key name="level2" match="//product" use="concat(@ordercat, '|', @what, '|', @pos)"/>  <!-- group what pos-->
	
	<xsl:template match="/">

		<data>
			<xsl:attribute name="type">K8 order creation control file</xsl:attribute>
			<process><xsl:value-of select="count(/order/productgroup/supplier/product[count(. | key('level0', @ordercat)[1]) = 1])"/></process>
			<xsl:for-each select="/order/productgroup/supplier/product[count(. | key('level0', @ordercat)[1]) = 1]">

				<order>
					<xsl:attribute name="number"><xsl:number format="1" value="position()"/></xsl:attribute>
				
				<orderCategory><xsl:value-of select="@ordercat"/></orderCategory>
				<xsl:variable name="onrOrdCat" select="@ordercat" />
				<xsl:variable name="onrWhat" select="@what" />

<orderLines>
</orderLines>
</order>


			</xsl:for-each>
		</data>
	</xsl:template>
</xsl:stylesheet>

What I have tried:

Various attempts at trying to group and only get the relevant ordercat, but failed miserably...

Posted 16-May-16 23:37pm

MKM_Matt

Updated 17-May-16 6:00am

George Jonsson

v2

Add a Solution

Comments

George Jonsson 17-May-16 6:01am

Not sure if you are forced into this format, but wouldn't it be easier to do
<orderlines>
<line what="1" pos="2" />
<line what="1" pos="3" />
...

MKM_Matt 17-May-16 6:05am

Hi George, thanks for the reply.
Not forced into a particular format at moment, just need to ensure that the grouping is correct. So yes I could make them attributes, but I still can't get the grouping part working.
Matt

MKM_Matt 17-May-16 12:07pm

Hi George. That worked a treat, need to do more testing but all looks brilliant at the moment. You deserve a beer....

George Jonsson 17-May-16 12:11pm

Time to watch Russia - Sweden in the World Cup.
And too late for more beer now here.
Can I have a rain check? :)

George Jonsson 17-May-16 12:13pm

Sheit. One more hour to wait. Oh, well.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Accepted Answer · 2016-05-17T06:00:00

Solution 1

It's a bit late and I had a beer or two, but I think this will work for you.

My additions to your XSL is in bold.

XML

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="text" encoding="iso-8859-1"/>
    <xsl:output method="xml" indent="yes" omit-xml-declaration="no"/>
    <!-- group -->
    <xsl:key name="level0" match="//product" use="@ordercat"/> 
    	
    <xsl:template match="/">
        <data>
            <xsl:attribute name="type">K8 order creation control file</xsl:attribute>
            <process><xsl:value-of select="count(/order/productgroup/supplier/product[count(. | key('level0', @ordercat)[1]) = 1])"/></process>
            <xsl:for-each select="/order/productgroup/supplier/product[count(. | key('level0', @ordercat)[1]) = 1]">
 
                <order>
                <xsl:attribute name="number"><xsl:number format="1" value="position()"/></xsl:attribute>
				
                <xsl:variable name="onrOrdCat" select="@ordercat" />
                <orderCategory><xsl:value-of select="$onrOrdCat"/></orderCategory>
 
                    <orderLines>
                    <xsl:apply-templates select="../product[@ordercat = $onrOrdCat]" />
                    </orderLines>
                </order>
            </xsl:for-each>
        </data>
    </xsl:template>
	
    <xsl:template match="product">
        <line>
        <xsl:attribute name="what"><xsl:value-of select="@what" /></xsl:attribute>
        <xsl:attribute name="pos"><xsl:value-of select="@pos" /></xsl:attribute>
        </line>
    </xsl:template>
</xsl:stylesheet>

The output is as follows:

XML

<data type="K8 order creation control file">
  <process>3</process>
  <order number="1">
    <ordercategory>C</ordercategory>
    <orderlines>
      <line what="1" pos="1" />
      <line what="1" pos="2" />
      <line what="1" pos="3" />
      <line what="1" pos="4" />
      <line what="1" pos="6" />
      <line what="1" pos="7" />
      <line what="1" pos="8" />
      <line what="1" pos="9" />
      <line what="1" pos="10" />
      <line what="1" pos="11" />
      <line what="1" pos="12" />
      <line what="1" pos="13" />
      <line what="1" pos="14" />
      <line what="1" pos="15" />
    </orderlines>
  </order>
  <order number="2">
    <ordercategory>S</ordercategory>
    <orderlines>
      <line what="1" pos="5" />
    </orderlines>
  </order>
  <order number="3">
    <ordercategory>N</ordercategory>
    <orderlines>
      <line what="3" pos="1" />
      <line what="3" pos="2" />
      <line what="3" pos="3" />
      <line what="3" pos="4" />
      <line what="3" pos="5" />
      <line what="3" pos="6" />
    </orderlines>
  </order>
</data>

You can of course change the output to your preference, but I think I got the information right.

Posted 17-May-16 6:00am

George Jonsson

Comments

Maciej Los 17-May-16 12:46pm

Another beer (5) from me to you, George!

George Jonsson 17-May-16 12:54pm

Dziękuję Ci, Maciej :)

Maciej Los 17-May-16 12:57pm

Proszę bardzo (You're very welcome).

George Jonsson 17-May-16 13:01pm

So Google translate worked!
I have mostly tried it with Chinese and that doesn't come out so well.
Kind of makes people laugh. ;P

Maciej Los 17-May-16 13:27pm

Google translator is helpful tool, but sometimes it translates a "set of words" instead the sentence (loosing the meaning of sentence).

MKM_Matt 23-May-16 10:34am

Hi George.
Just done a bit more testing and have noticed an issue.
XML:-

<order display="2204">
<productgroup display="Kitchen Units" lookup="1">

<supplier display="Chippendale" lookup="90">

<product display="Base End Support Panel Natural Oak" what="1" pos="1" ordercat="D" />
<product display="600MM Highline Integrated Dishwasher Fascia" what="1" pos="2" ordercat="D" />
<product display="600MM Highline Base Unit" what="1" pos="3" ordercat="D" />
<product display="600MM Highline Integrated Fridge/Freeezer Fascia" what="1" pos="4" ordercat="D" />
<product display="600MM 3 Drawer Pan Unit" what="1" pos="5" ordercat="D" />
<product display="600MM Highline Base Unit" what="1" pos="6" ordercat="D" />
<product display="500MM Highline Base Unit" what="1" pos="7" ordercat="D" />
<product display="600MM Fridge/Freezer Housing Unit - Type 6" what="1" pos="8" ordercat="D" />
<product display="600MM Double Oven Housing Unit - Type 2" what="1" pos="9" ordercat="D" />
<product display="2650 Plinth Natural Oak Inc Seal Strip" what="1" pos="10" ordercat="D" />
<product display="Worktop 600 Single P/F 4000 Black Brazil" what="1" pos="11" ordercat="D" />
<product display="30MM Bullnose Sq Cornice 3600MM Natural" what="1" pos="12" ordercat="D" />
<product display="10Mtr Roll Edging Tape Natural Oak" what="1" pos="13" ordercat="D" />
<product display="Schock Stnd Inset 1.5 Bowl Drainer Sink" what="1" pos="14" ordercat="D" />
<product display="Schock Universal Plumbing Kit 1.5 Bowl" what="1" pos="15" ordercat="D" />

</productgroup>
<productgroup display="Appliances" lookup="3">
<supplier display="N/A*******N/A" lookup="130">
<product display="CDA - Integrated Combination Fridge/freezer 70/30 A+ Rated" what="3" pos="1" ordercat="D" />
<product display="CDA - Integrated Washing Machine" what="3" pos="2" ordercat="D" />
<product display="CDA - Double Oven Bi Aa Rated Main Oven Touch Control Clock S/steel" what="3" pos="3" ordercat="D" />
<product display="CDA - Five Burner Front Control 70cm Gas Hob Wok Ffd Stainless Steel" what="3" pos="4" ordercat="D" />
<product display="CDA - 70cm Chimney Extractor Hood - Stainless Steel" what="3" pos="5" ordercat="D" />
<product display="CDA - Fully Integrated Dishwasher 60 Cm A++Aa Rated" what="3" pos="6" ordercat="D" />

</productgroup>

XSL:-

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:output encoding="iso-8859-1">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes">

<xsl:key name="level0" match="//product" use="@ordercat">

<xsl:template match="/">

<xsl:attribute name="type">K8 order creation control file
<process><xsl:value-of select="count(/order/productgroup/supplier/product[count(. | key('level0', @ordercat)[1]) = 1])"></process>
<xsl:for-each select="/order/productgroup/supplier/product[count(. | key('level0', @ordercat)[1]) = 1]">

<order>
<xsl:attribute name="number"><xsl:number format="1" value="position()">

<xsl:variable name="onrOrdCat" select="@ordercat">
<ordercategory><xsl:value-of select="$onrOrdCat">

<orderlines>
<xsl:apply-templates select="../product[@ordercat = $onrOrdCat]">

<xsl:template match="product">
<line>
<xsl:attribute name="what"><xsl:value-of select="@what">
<xsl:attribute name="pos"><xsl:value-of select="@pos">
</line>

OUTPUT is:-

<

George Jonsson 23-May-16 10:52am

I think you need to post a new question. The hamsters have eaten most of the XML tags.

MKM_Matt 23-May-16 11:09am

Will do.
Thanks again.
Matt

MKM_Matt 23-May-16 11:24am

Hi.
Pesky hamsters.
Have just logged a call:- Mapping and Grouping secondary. and also XML to XML Grouping and Selecting as the site didn't refresh...
As always thanks in advance.
Matt

MKM_Matt 24-May-16 4:43am

Have looked on web site and can't see my new posted questions.... Will try to patch it all on to this one again... Matt

George Jonsson 24-May-16 4:48am

The hamsters are still against you.

MKM_Matt 24-May-16 4:51am

Yes, they sit there with there puffed out cheeks taunting me, saying go on I can eat more than you can type...

MKM_Matt 24-May-16 5:06am

Hah, did it. I got a question on... named it XML Grouping secondary query.

Really appreciate you looking into this for me. Matt