user475464
Reputation: 1763
how to filter xml with group wise?
This is my xml file
<hotels>
<item>
<hotelId>1001</hotelId>
<boardType>Room Only</boardType>
<rooms>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
</paxes>
<totalRoomRate>10</totalRoomRate>
</item>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Child</paxType>
<age>1</age>
</item>
</paxes>
<totalRoomRate>15</totalRoomRate>
</item>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Child</paxType>
<age>14</age>
</item>
</paxes>
<totalRoomRate>20</totalRoomRate>
</item>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
</paxes>
<totalRoomRate>20</totalRoomRate>
</item>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Child</paxType>
<age>14</age>
</item>
<item>
<paxType>Child</paxType>
<age>10</age>
</item>
</paxes>
<totalRoomRate>25</totalRoomRate>
</item>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Child</paxType>
<age>14</age>
</item>
<item>
<paxType>Child</paxType>
<age>10</age>
</item>
</paxes>
<totalRoomRate>20</totalRoomRate>
</item>
<item>
<roomCategory>Standard Single Room</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
</paxes>
<totalRoomRate>20</totalRoomRate>
</item>
</rooms>
</item>
<item>
<hotelId>1002</hotelId>
<boardType>Room Only</boardType>
<rooms>
<item>
<roomCategory>Double/twin Deluxe</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
</paxes>
<totalRoomRate>10</totalRoomRate>
</item>
<item>
<roomCategory>Double/twin Deluxe</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
</paxes>
<totalRoomRate>12</totalRoomRate>
</item>
</rooms>
</item>
<item>
<hotelId>1003</hotelId>
<boardType>Bed And Breakfast</boardType>
<rooms>
<item>
<roomCategory>Double/twin Deluxe</roomCategory>
<paxes>
<item>
<paxType>Adult</paxType>
<age>30</age>
</item>
<item>
<paxType>Child</paxType>
<age>3</age>
</item>
</paxes>
<totalRoomRate>20</totalRoomRate>
</item>
</rooms>
</item>
</hotels>
XML output i showing below
Hotel ID: 1001
Room Type Board Type Quantity Adults Child Price
Standard Single Room Room Only 1 1 0 10
Standard Single Room Room Only 1 1 1 15
Standard Single Room Room Only 1 1 1 20
Standard Single Room Room Only 1 2 0 20
Standard Single Room Room Only 1 1 2 25
Standard Single Room Room Only 1 1 2 20
Standard Single Room Room Only 1 2 0 20
Hotel ID: 1002
Room Type Board Type Quantity Adults Child Price
Double/twin Deluxe Room Only 1 1 0 10
Double/twin Deluxe Room Only 1 1 0 12
Hotel ID: 1003
Room Type Board Type Quantity Adults Child Price
Double/twin Deluxe Bed And Breakfast 1 1 1 20
But i need to filter the above results with xsl like below
Hotel ID: 1001
Room Type Board Type Quantity Adults Child Price
Standard Single Room Room Only 1 1 0 10
Standard Single Room Room Only 2 1 1 35
Standard Single Room Room Only 2 2 0 40
Standard Single Room Room Only 2 1 2 45
Hotel ID: 1002
Room Type Board Type Quantity Adults Child Price
Double/twin Deluxe Room Only 2 1 0 22
Hotel ID: 1003
Room Type Board Type Quantity Adults Child Price
Double/twin Deluxe Bed And Breakfast 1 1 1 20
if you take total quantity it should be on same "Room Type", "Board Type", "Adults" and "Child"
if you take total Price it should be the total of that group
How i do this in xsl file
hoping your help
Upvotes: 0
Views: 343
Answers (2)
Tim C
Reputation: 70648
If you want to do this in XSLT1.0, you will need to use a technique called Meunchian Grouping.
In your case you are gouping by Hotel ID, Board Type, Category, Adult and Children, which means defined quite a complicated key
<xsl:key
name="items"
match="rooms/item"
use="concat(../../hotelId,
'|', ../../boardType,
'|', roomCategory,
'|', count(paxes/item[paxType='Adult']),
'|', count(paxes/item[paxType='Child']))" />
Do note use of the 'pipe' character to separate the various fields in the key. It is important this character does not actually occur in any of the fields itself.
Next (for a given hotel id), you start off by finding the first element in each group, like so:
<xsl:apply-templates
select="rooms/item[generate-id()
= generate-id(key('items',
concat(../../hotelId,
'|', ../../boardType,
'|', roomCategory,
'|', count(paxes/item[paxType='Adult']),
'|', count(paxes/item[paxType='Child'])))[1])]"
mode="group"/>
Then, once you have matched the first element in a group, you can then loop through all the elements in the group, or in your case, aggregate them together
So, given the following XSLT
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="yes"/>
<xsl:key name="items" match="rooms/item" use="concat(../../hotelId, '|', ../../boardType, '|', roomCategory, '|', count(paxes/item[paxType='Adult']), '|', count(paxes/item[paxType='Child']))"/>
<xsl:template match="/hotels">
<xsl:apply-templates select="item"/>
</xsl:template>
<xsl:template match="hotels/item">
<h1>
<xsl:value-of select="concat('Hotel ID: ', hotelId)"/>
</h1>
<table>
<thead>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
</thead>
<tbody>
<xsl:apply-templates select="rooms/item[generate-id() = generate-id(key('items', concat(../../hotelId, '|', ../../boardType, '|', roomCategory, '|', count(paxes/item[paxType='Adult']), '|', count(paxes/item[paxType='Child'])))[1])]" mode="group"/>
</tbody>
</table>
</xsl:template>
<xsl:template match="rooms/item" mode="group">
<xsl:variable name="groupitems" select="key('items', concat(../../hotelId, '|', ../../boardType, '|', roomCategory, '|', count(paxes/item[paxType='Adult']), '|', count(paxes/item[paxType='Child'])))"/>
<tr>
<td>
<xsl:value-of select="roomCategory"/>
</td>
<td>
<xsl:value-of select="../../boardType"/>
</td>
<td>
<xsl:value-of select="count($groupitems)"/>
</td>
<td>
<xsl:value-of select="count(paxes/item[paxType='Adult'])"/>
</td>
<td>
<xsl:value-of select="count(paxes/item[paxType='Child'])"/>
</td>
<td>
<xsl:value-of select="sum($groupitems/totalRoomRate)"/>
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
When applied to your sample XML, the following is output:
<h1>Hotel ID: 1001</h1>
<table>
<thead>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
</thead>
<tbody>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>1</td>
<td>1</td>
<td>0</td>
<td>10</td>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>2</td>
<td>1</td>
<td>1</td>
<td>35</td>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>2</td>
<td>2</td>
<td>0</td>
<td>40</td>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>2</td>
<td>1</td>
<td>2</td>
<td>45</td>
</tr>
</tbody>
</table>
<h1>Hotel ID: 1002</h1>
<table>
<thead>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
</thead>
<tbody>
<tr>
<td>Double/twin Deluxe</td>
<td>Room Only</td>
<td>2</td>
<td>1</td>
<td>0</td>
<td>22</td>
</tr>
</tbody>
</table>
<h1>Hotel ID: 1003</h1>
<table>
<thead>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
</thead>
<tbody>
<tr>
<td>Double/twin Deluxe</td>
<td>Bed And Breakfast</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>20</td>
</tr>
</tbody>
</table>
Upvotes: 1
Maestro13
Reputation: 3696
solution in XSLT 2.0:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
<xsl:output method="html" indent="yes"/>
<xsl:template match="/">
<xsl:for-each select="hotels/item">
<h1>Hotel ID: <xsl:value-of select="hotelId"/></h1>
<br/>
<table>
<tbody>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
<xsl:variable name="BT" select="boardType"/>
<xsl:for-each-group select="rooms/item" group-by="count(paxes/item[paxType='Adult']) * 1000 + count(paxes/item[paxType='Child'])">
<xsl:for-each select=".">
<tr>
<td><xsl:value-of select="roomCategory"/></td>
<td><xsl:value-of select="$BT"/></td>
<td><xsl:value-of select="count(current-group())"/></td>
<td><xsl:value-of select="count(./paxes/item[paxType='Adult'])"/></td>
<td><xsl:value-of select="count(./paxes/item[paxType='Child'])"/></td>
<td><xsl:value-of select="sum(current-group()/totalRoomRate)"/></td>
</tr>
</xsl:for-each>
</xsl:for-each-group>
</tbody>
</table>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
which gives as result
<h1>Hotel ID: 1001</h1><br><table>
<tbody>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>1</td>
<td>1</td>
<td>0</td>
<td>10</td>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>2</td>
<td>1</td>
<td>1</td>
<td>35</td>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>2</td>
<td>2</td>
<td>0</td>
<td>40</td>
</tr>
<tr>
<td>Standard Single Room</td>
<td>Room Only</td>
<td>2</td>
<td>1</td>
<td>2</td>
<td>45</td>
</tr>
</tbody>
</table>
<h1>Hotel ID: 1002</h1><br><table>
<tbody>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
<tr>
<td>Double/twin Deluxe</td>
<td>Room Only</td>
<td>2</td>
<td>1</td>
<td>0</td>
<td>22</td>
</tr>
</tbody>
</table>
<h1>Hotel ID: 1003</h1><br><table>
<tbody>
<tr>
<th>Room Type</th>
<th>Board Type</th>
<th>Quantity</th>
<th>Adults</th>
<th>Child</th>
<th>Price</th>
</tr>
<tr>
<td>Double/twin Deluxe</td>
<td>Bed And Breakfast</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>20</td>
</tr>
</tbody>
</table>
Upvotes: 0
Related Questions
- Filtering and sorting through XSL group
- xslt to group xml based on element values
- Filter the elements of an xml with xslt
- Filter and Group in XSLT
- Filtering XML based using xslt
- How to filter xml elements based on the value of different element?
- Filtering XML elements using XSLT
- XSLT to filter elements from XML
- Group by and filter XML data with XSL
- XSL grouping xml by element