How to convert my sql query to xslt?

EDN Admin

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
I have xml file<servicehotel></servicehotel>
<pre class="prettyprint <ServiceHotel>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>1</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Room Only</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>21.168</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Room Only</Board>
<RoomType>Twin Superior</RoomType>
<Price>
<Amount>34.195</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>1</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Room Only</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>25.618</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Room Only</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>38.645</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Room Only</Board>
<RoomType>Single Superior</RoomType>
<Price>
<Amount>25.618</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Room Only</Board>
<RoomType>Double Superior</RoomType>
<Price>
<Amount>19.840</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Bed and Breakfast</Board>
<RoomType>Twin Superior</RoomType>
<Price>
<Amount>34.195</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Bed and Breakfast</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>38.645</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Bed and Breakfast</Board>
<RoomType>Single Superior</RoomType>
<Price>
<Amount>25.618</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Bed and Breakfast</Board>
<RoomType>Double Superior</RoomType>
<Price>
<Amount>19.840</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Full Board</Board>
<RoomType>Twin Superior</RoomType>
<Price>
<Amount>35.195</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Full Board</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>32.645</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Half Board</Board>
<RoomType>Twin Superior</RoomType>
<Price>
<Amount>35.195</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>3</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Half Board</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>32.645</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Half Board</Board>
<RoomType>Single Superior</RoomType>
<Price>
<Amount>28.618</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>0</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Half Board</Board>
<RoomType>Double Superior</RoomType>
<Price>
<Amount>20.840</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
<AvailableRoom>
<HotelOccupancy>
<RoomCount>1</RoomCount>
<Occupancy>
<AdultCount>1</AdultCount>
<ChildCount>1</ChildCount>
</Occupancy>
</HotelOccupancy>
<HotelRoom>
<Board>Half Board</Board>
<RoomType>Double/twin Superior</RoomType>
<Price>
<Amount>30.168</Amount>
</Price>
</HotelRoom>
</AvailableRoom>
</ServiceHotel>
[/code]
<br/>

<servicehotel><availableroom><hotelroom></hotelroom></availableroom> </servicehotel>
I convert the above xml to sql query like below
<pre class="prettyprint lang-sql SET NOCOUNT ON DECLARE @Product TABLE (
Total_Adults VarChar(50) ,
Board_Type VarChar(100) ,
Room_Type VarChar(150) ,
Room_Count VarChar(10) ,
Adults VarChar(10) ,
Child VarChar(10) ,
Price Float(53)
);
INSERT INTO @Product Values (111,Room Only,Double/twin Superior,1,1,1,21.168);
INSERT INTO @Product Values (130,Room Only,Twin Superior,1,3,0,34.195);
INSERT INTO @Product Values (111,Room Only,Double/twin Superior,1,1,1,25.618);
INSERT INTO @Product Values (130,Room Only,Double/twin Superior,1,3,0,38.645);
INSERT INTO @Product Values (110,Room Only,Single Superior,1,1,0,25.618);
INSERT INTO @Product Values (110,Room Only,Double Superior,1,1,0,19.840);

INSERT INTO @Product Values (130,Bed and Breakfast,Twin Superior,1,3,0,34.195);
INSERT INTO @Product Values (130,Bed and Breakfast,Double/twin Superior,1,3,0,38.645);
INSERT INTO @Product Values (110,Bed and Breakfast,Single Superior,1,1,0,25.618);
INSERT INTO @Product Values (110,Bed and Breakfast,Double Superior,1,1,0,19.840);

INSERT INTO @Product Values (130,Full Board,Twin Superior,1,3,0,35.195);
INSERT INTO @Product Values (130,Full Board,Double/twin Superior,1,3,0,32.645);

INSERT INTO @Product Values (130,Half Board,Twin Superior,1,3,0,35.195);
INSERT INTO @Product Values (130,Half Board,Double/twin Superior,1,3,0,32.645);
INSERT INTO @Product Values (110,Half Board,Single Superior,1,1,0,28.618);
INSERT INTO @Product Values (110,Half Board,Double Superior,1,1,0,20.840);
INSERT INTO @Product Values (111,Half Board,Double/twin Superior,1,1,1,30.168);

WITH CTE1 AS (
SELECT ROW_NUMBER() OVER(PARTITION BY Total_Adults, Board_Type, Room_Type ORDER BY Price ASC
) AS RN, * FROM @Product)
,CTE2 AS ( SELECT * FROM CTE1 WHERE RN = 1)
,CTE3 As (Select *,Count(*) over()as Frequency
, row_number()over(partition by Total_Adults, Board_Type order by Price ASC) as RNN from CTE2)
,CTE4 As (
select *, Count(Board_Type) over (partition by Board_Type) as RC from CTE3 where RNN=1)
Select SUM(Price) over (partition by Board_Type) as Grand_Total,* from CTE4 where RC=3
ORDER BY Grand_Total ASC, MIN(Price) OVER(PARTITION BY Board_Type),Board_Type,Price;[/code]
the output of my sql query is
<pre class="prettyprint Grand_Total, Total_Adults, Board_Type, Room_Type, Room_Count, Adults, Child, Price
75.203 110 Room Only Double Superior 1 1 0 19.84
75.203 111 Room Only Double/twin Superior 1 1 1 21.168
75.203 130 Room Only Twin Superior 1 3 0 34.195
83.653 110 Half Board Double Superior 1 1 0 20.84
83.653 111 Half Board Double/twin Superior 1 1 1 30.168
83.653 130 Half Board Double/twin Superior 1 3 0 32.645 [/code]
I need to create XSL 1.0 file for taking this output. (equivalent to SQL query)<br/>

<br/>

View the full article
 
Back
Top