/* 
Demonstrates usage of ROW_NUMBER and FOR XML PATH to create 
pageable XML results queries.

In this case, the key is to page only on the Route objects.
*/

--// Mock Route table
DECLARE @Route TABLE (
    Id int,
    Title varchar(100)
)

--// Mock Step table
DECLARE @Step TABLE (
    Id int,
    RouteId int,
    Title varchar(100),
    Sequence int
)

--// Insert mock data
INSERT INTO @Route VALUES (1, 'Route 1')
INSERT INTO @Route VALUES (2, 'Route 2')
INSERT INTO @Route VALUES (3, 'Route 3')
INSERT INTO @Route VALUES (4, 'Route 4')
INSERT INTO @Route VALUES (5, 'Route 5')

--// Route 1 Steps
INSERT INTO @Step VALUES (1, 1, 'Step 1.1', 1)
INSERT INTO @Step VALUES (2, 1, 'Step 1.2', 2)
INSERT INTO @Step VALUES (3, 1, 'Step 1.3', 3)
INSERT INTO @Step VALUES (4, 1, 'Step 1.4', 4)
INSERT INTO @Step VALUES (5, 1, 'Step 1.5', 5)

--// Route 2 Steps
INSERT INTO @Step VALUES (6, 2, 'Step 2.1', 1)
INSERT INTO @Step VALUES (7, 2, 'Step 2.2', 2)
INSERT INTO @Step VALUES (8, 2, 'Step 2.3', 3)

--// Route 3 Steps
INSERT INTO @Step VALUES (9, 3, 'Step 3.1', 1)

--// Route 4 Steps
INSERT INTO @Step VALUES (10, 4, 'Step 4.1', 1)

--// Route 5 Steps
INSERT INTO @Step VALUES (11, 5, 'Step 5.1', 1)
INSERT INTO @Step VALUES (12, 5, 'Step 5.2', 2)
INSERT INTO @Step VALUES (13, 5, 'Step 5.3', 3)
INSERT INTO @Step VALUES (14, 5, 'Step 5.4', 4)

/*
Define the page size.
 -- Add sorting and ordering later
*/

DECLARE @PageSize int
DECLARE @CurrentPage int

SET @CurrentPage = 0
SET @PageSize = 3

/*
Calculate starting and ending row.
*/
DECLARE @StartIndex int
DECLARE @EndIndex int

SET @StartIndex = @CurrentPage * @PageSize
SET @EndIndex = @StartIndex + @PageSize

; --// Need to terminate with a semicolon for CTE 
/*
Perform core XML select
*/

WITH Routes AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
    FROM
        @Route
)
SELECT
    Routes.Id AS "@Id",
    Routes.Title AS "@Title",
    (
        SELECT
            Step.Id AS '@Id',
            Step.Title AS '@Title'
        FROM
            @Step AS Step
        WHERE
            Step.RouteId = Routes.Id
        ORDER BY 
            Step.Sequence ASC
        FOR XML PATH('Step'), TYPE            
    ) AS 'Steps'
FROM 
    Routes
WHERE
    RowNumber > @StartIndex AND RowNumber <= @EndIndex  --// BETWEEN Results in improper paging
FOR XML PATH('Route'), ROOT('Routes')

<Routes>
  <Route Id="1" Title="Route 1">
    <Steps>
      <Step Id="1" Title="Step 1.1" />
      <Step Id="2" Title="Step 1.2" />
      <Step Id="3" Title="Step 1.3" />
      <Step Id="4" Title="Step 1.4" />
      <Step Id="5" Title="Step 1.5" />
    </Steps>
  </Route>
  <Route Id="2" Title="Route 2">
    <Steps>
      <Step Id="6" Title="Step 2.1" />
      <Step Id="7" Title="Step 2.2" />
      <Step Id="8" Title="Step 2.3" />
    </Steps>
  </Route>
  <Route Id="3" Title="Route 3">
    <Steps>
      <Step Id="9" Title="Step 3.1" />
    </Steps>
  </Route>
</Routes>

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Xml.Serialization;

namespace XmlDeserialization {
    internal class Program {
        private static void Main(string[] args) {
            string xml = ...; // XML string here

            RouteList routes = new RouteList(xml);

            Console.Out.WriteLine(routes.Count);

            foreach(Route route in routes) {
                Console.Out.WriteLine(" + Route: {0}", route.Id);

                foreach(Step step in route.Steps) {
                    Console.Out.WriteLine("   + Step: {0}", step.Id);
                }
            }
        }
    }
    
    public abstract class XmlDeserializingList<Titem> : List<Titem> {
        protected XmlDeserializingList() { }

        protected XmlDeserializingList(string xml) {
            StringReader reader = new StringReader(xml);

            XmlSerializer serializer = new XmlSerializer(GetType());
            XmlDeserializingList<Titem> items = (XmlDeserializingList<Titem>)serializer.Deserialize(reader);

            AddRange(items);
        }
    }

    [XmlRoot("Routes")]
    public class RouteList : XmlDeserializingList<Route> {
        public RouteList() {}

        public RouteList(string xml) : base(xml) {
            
        }
    }

    [Serializable]
    public class Route {
        private int id;
        private string title;
        private Collection<Step> steps;

        [XmlAttribute]
        public int Id {
            get { return id; }
            set { id = value; }
        }

        [XmlAttribute]
        public string Title {
            get { return title; }
            set { title = value; }
        }

        [XmlArray("Steps"), XmlArrayItem("Step", typeof(Step))]
        public Collection<Step> Steps {
            get { return steps; }
            set { steps = value; }
        }
    }

    [Serializable]
    public class Step {
        private int id;
        private string title;

        [XmlAttribute]
        public int Id {
            get { return id; }
            set { id = value; }
        }

        [XmlAttribute]
        public string Title {
            get { return title; }
            set { title = value; }
        }
    }
}