<CharlieDigital /> Programming, Politics, and uhh…pineapples

/* 

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; }

        }

    }

}