I'm constantly amazed by the number of developers who have never worked with FOR XML EXPLICIT and the new FOR XML PATH. If I were designing data access, it would be my go-to commands for building queries for complex data structures (nested DataReaders? yuck!).
In the past, to support paging using FOR XML EXPLICIT queries took tons of lines to accomplish (although there is something about the whole explicitness that makes it surprisingly legible). Now with the fancy pants ROW_NUMBER function in SQL along with CTEs, a hundred line query can be written with maybe 15-20 lines.
Here's a simple example that you can copy+paste and run:
/*
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')What's great about this is that if your object model is properly designed, it's just a matter of deserializing the XML (using precompiled serialization binaries, of course) to rehydrate your data model.
In this case, the output XML looks like this:
<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>Next, you'll need some simple code to deserialize the XML to make it useful:
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; }
}
}
}It might even be useful to add some abstract methods (and properties to support it) to GetNextPage()