FOR XML Needs More Love

by Charles Chen · April 24, 2008

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:

<FONT face="Lucida Console"><FONT color=#5f5f5f>/* </FONT>
<FONT color=#5f5f5f>Demonstrates usage of ROW_NUMBER and FOR XML PATH to create </FONT>
<FONT color=#5f5f5f>pageable XML results queries.</FONT>

<FONT color=#5f5f5f>In this case, the key is to page only on the Route objects.</FONT>
<FONT color=#5f5f5f>*/</FONT>

<FONT color=#5f5f5f>--// Mock Route table</FONT>
<FONT color=#008000>DECLARE </FONT><FONT color=#000000>@Route </FONT><FONT color=#008000>TABLE </FONT><FONT color=#000000>(</FONT>
    <FONT color=#000000>Id </FONT><FONT color=#0000ff>int</FONT><FONT color=#000000>,</FONT>
    <FONT color=#000000>Title </FONT><FONT color=#0000ff>varchar</FONT><FONT color=#000000>(100)</FONT>
<FONT color=#000000>)</FONT>

<FONT color=#5f5f5f>--// Mock Step table</FONT>
<FONT color=#008000>DECLARE </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>TABLE </FONT><FONT color=#000000>(</FONT>
    <FONT color=#000000>Id </FONT><FONT color=#0000ff>int</FONT><FONT color=#000000>,</FONT>
    <FONT color=#000000>RouteId </FONT><FONT color=#0000ff>int</FONT><FONT color=#000000>,</FONT>
    <FONT color=#000000>Title </FONT><FONT color=#0000ff>varchar</FONT><FONT color=#000000>(100),</FONT>
    <FONT color=#000000>Sequence </FONT><FONT color=#0000ff>int</FONT>
<FONT color=#000000>)</FONT>

<FONT color=#5f5f5f>--// Insert mock data</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Route </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(1, </FONT><FONT color=#ff00ff>'Route 1'</FONT><FONT color=#000000>)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Route </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(2, </FONT><FONT color=#ff00ff>'Route 2'</FONT><FONT color=#000000>)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Route </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(3, </FONT><FONT color=#ff00ff>'Route 3'</FONT><FONT color=#000000>)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Route </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(4, </FONT><FONT color=#ff00ff>'Route 4'</FONT><FONT color=#000000>)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Route </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(5, </FONT><FONT color=#ff00ff>'Route 5'</FONT><FONT color=#000000>)</FONT>

<FONT color=#5f5f5f>--// Route 1 Steps</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(1, 1, </FONT><FONT color=#ff00ff>'Step 1.1'</FONT><FONT color=#000000>, 1)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(2, 1, </FONT><FONT color=#ff00ff>'Step 1.2'</FONT><FONT color=#000000>, 2)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(3, 1, </FONT><FONT color=#ff00ff>'Step 1.3'</FONT><FONT color=#000000>, 3)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(4, 1, </FONT><FONT color=#ff00ff>'Step 1.4'</FONT><FONT color=#000000>, 4)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(5, 1, </FONT><FONT color=#ff00ff>'Step 1.5'</FONT><FONT color=#000000>, 5)</FONT>

<FONT color=#5f5f5f>--// Route 2 Steps</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(6, 2, </FONT><FONT color=#ff00ff>'Step 2.1'</FONT><FONT color=#000000>, 1)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(7, 2, </FONT><FONT color=#ff00ff>'Step 2.2'</FONT><FONT color=#000000>, 2)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(8, 2, </FONT><FONT color=#ff00ff>'Step 2.3'</FONT><FONT color=#000000>, 3)</FONT>

<FONT color=#5f5f5f>--// Route 3 Steps</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(9, 3, </FONT><FONT color=#ff00ff>'Step 3.1'</FONT><FONT color=#000000>, 1)</FONT>

<FONT color=#5f5f5f>--// Route 4 Steps</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(10, 4, </FONT><FONT color=#ff00ff>'Step 4.1'</FONT><FONT color=#000000>, 1)</FONT>

<FONT color=#5f5f5f>--// Route 5 Steps</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(11, 5, </FONT><FONT color=#ff00ff>'Step 5.1'</FONT><FONT color=#000000>, 1)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(12, 5, </FONT><FONT color=#ff00ff>'Step 5.2'</FONT><FONT color=#000000>, 2)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(13, 5, </FONT><FONT color=#ff00ff>'Step 5.3'</FONT><FONT color=#000000>, 3)</FONT>
<FONT color=#008000>INSERT INTO </FONT><FONT color=#000000>@Step </FONT><FONT color=#008000>VALUES </FONT><FONT color=#000000>(14, 5, </FONT><FONT color=#ff00ff>'Step 5.4'</FONT><FONT color=#000000>, 4)</FONT>

<FONT color=#5f5f5f>/*</FONT>
<FONT color=#5f5f5f>Define the page size.</FONT>
<FONT color=#5f5f5f> -- Add sorting and ordering later</FONT>
<FONT color=#5f5f5f>*/</FONT>

<FONT color=#008000>DECLARE </FONT><FONT color=#000000>@PageSize </FONT><FONT color=#0000ff>int</FONT>
<FONT color=#008000>DECLARE </FONT><FONT color=#000000>@CurrentPage </FONT><FONT color=#0000ff>int</FONT>

<FONT color=#008000>SET </FONT><FONT color=#000000>@CurrentPage = 0</FONT>
<FONT color=#008000>SET </FONT><FONT color=#000000>@PageSize = 3</FONT>

<FONT color=#5f5f5f>/*</FONT>
<FONT color=#5f5f5f>Calculate starting and ending row.</FONT>
<FONT color=#5f5f5f>*/</FONT>
<FONT color=#008000>DECLARE </FONT><FONT color=#000000>@StartIndex </FONT><FONT color=#0000ff>int</FONT>
<FONT color=#008000>DECLARE </FONT><FONT color=#000000>@EndIndex </FONT><FONT color=#0000ff>int</FONT>

<FONT color=#008000>SET </FONT><FONT color=#000000>@StartIndex = @CurrentPage * @PageSize</FONT>
<FONT color=#008000>SET </FONT><FONT color=#000000>@EndIndex = @StartIndex + @PageSize</FONT>

<FONT color=#000000>; </FONT><FONT color=#5f5f5f>--// Need to terminate with a semicolon for CTE </FONT>
<FONT color=#5f5f5f>/*</FONT>
<FONT color=#5f5f5f>Perform core XML select</FONT>
<FONT color=#5f5f5f>*/</FONT>

<FONT color=#008000>WITH </FONT><FONT color=#000000>Routes </FONT><FONT color=#008000>AS </FONT><FONT color=#000000>(</FONT>
    <FONT color=#008000>SELECT </FONT>
        <FONT color=#000000>*,</FONT>
        <FONT color=#000000>ROW_NUMBER() OVER (</FONT><FONT color=#008000>ORDER BY </FONT><FONT color=#000000>Id) </FONT><FONT color=#008000>AS </FONT><FONT color=#ff00ff>'RowNumber'</FONT>
    <FONT color=#008000>FROM</FONT>
        <FONT color=#000000>@Route</FONT>
<FONT color=#000000>)</FONT>
<FONT color=#008000>SELECT</FONT>
    <FONT color=#000000>Routes.Id </FONT><FONT color=#008000>AS </FONT><FONT color=#ff00ff>"@Id"</FONT><FONT color=#000000>,</FONT>
    <FONT color=#000000>Routes.Title </FONT><FONT color=#008000>AS </FONT><FONT color=#ff00ff>"@Title"</FONT><FONT color=#000000>,</FONT>
    <FONT color=#000000>(</FONT>
        <FONT color=#008000>SELECT</FONT>
            <FONT color=#000000>Step.Id </FONT><FONT color=#008000>AS </FONT><FONT color=#ff00ff>'@Id'</FONT><FONT color=#000000>,</FONT>
            <FONT color=#000000>Step.Title </FONT><FONT color=#008000>AS </FONT><FONT color=#ff00ff>'@Title'</FONT>
        <FONT color=#008000>FROM</FONT>
            <FONT color=#000000>@Step </FONT><FONT color=#008000>AS </FONT><FONT color=#000000>Step</FONT>
        <FONT color=#008000>WHERE</FONT>
            <FONT color=#000000>Step.RouteId = Routes.Id</FONT>
        <FONT color=#008000>ORDER BY </FONT>
            <FONT color=#000000>Step.Sequence </FONT><FONT color=#008000>ASC</FONT>
        <FONT color=#008000>FOR </FONT><FONT color=#000000>XML PATH(</FONT><FONT color=#ff00ff>'Step'</FONT><FONT color=#000000>), TYPE            </FONT>
    <FONT color=#000000>) </FONT><FONT color=#008000>AS </FONT><FONT color=#ff00ff>'Steps'</FONT>
<FONT color=#008000>FROM </FONT>
    <FONT color=#000000>Routes</FONT>
<FONT color=#008000>WHERE</FONT>
    <FONT color=#000000>RowNumber &gt; @StartIndex </FONT><FONT color=#008000>AND </FONT><FONT color=#000000>RowNumber &lt;= @EndIndex  </FONT><FONT color=#5f5f5f>--// BETWEEN Results in improper paging</FONT>
<FONT color=#008000>FOR </FONT><FONT color=#000000>XML PATH(</FONT><FONT color=#ff00ff>'Route'</FONT><FONT color=#000000>), ROOT(</FONT><FONT color=#ff00ff>'Routes'</FONT><FONT color=#000000>)</FONT></FONT>

100

101

102

103

104

/*

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:

<FONT face="Lucida Console"><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Routes</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>  </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Route Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"1" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Route 1"</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>    </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Steps</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"1" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 1.1" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"2" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 1.2" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"3" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 1.3" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"4" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 1.4" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"5" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 1.5" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>    </FONT><FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Steps</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>  </FONT><FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Route</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>  </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Route Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"2" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Route 2"</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>    </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Steps</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"6" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 2.1" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"7" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 2.2" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"8" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 2.3" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>    </FONT><FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Steps</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>  </FONT><FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Route</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>  </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Route Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"3" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Route 3"</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>    </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Steps</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>      </FONT><FONT color=#3040fe>&lt;</FONT><FONT color=#ff0000>Step Id</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"9" </FONT><FONT color=#ff0000>Title</FONT><FONT color=#3040fe>=</FONT><FONT color=#ff00ff>"Step 3.1" </FONT><FONT color=#3040fe>/&gt;</FONT>
<FONT color=#000000>    </FONT><FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Steps</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#000000>  </FONT><FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Route</FONT><FONT color=#3040fe>&gt;</FONT>
<FONT color=#3040fe>&lt;/</FONT><FONT color=#ff0000>Routes</FONT><FONT color=#3040fe>&gt;</FONT></FONT>

<Steps>

</Steps>

</Route>

<Steps>

</Steps>

</Route>

<Steps>

</Steps>

</Route>

</Routes>

Next, you’ll need some simple code to deserialize the XML to make it useful:

<FONT face="Lucida Console,monospace"><FONT color=#0000ff>using </FONT><FONT color=#008080>System</FONT><FONT color=#000000>;</FONT>
<FONT color=#0000ff>using </FONT><FONT color=#008080>System</FONT><FONT color=#000000>.</FONT><FONT color=#008080>Collections</FONT><FONT color=#000000>.Generic;</FONT>
<FONT color=#0000ff>using </FONT><FONT color=#008080>System</FONT><FONT color=#000000>.</FONT><FONT color=#008080>Collections</FONT><FONT color=#000000>.ObjectModel;</FONT>
<FONT color=#0000ff>using </FONT><FONT color=#008080>System</FONT><FONT color=#000000>.</FONT><FONT color=#008080>IO</FONT><FONT color=#000000>;</FONT>
<FONT color=#0000ff>using </FONT><FONT color=#008080>System</FONT><FONT color=#000000>.</FONT><FONT color=#008080>Xml</FONT><FONT color=#000000>.</FONT><FONT color=#008080>Serialization</FONT><FONT color=#000000>;</FONT>

<FONT color=#0000ff>namespace </FONT><FONT color=#000000>XmlDeserialization {</FONT>
    <FONT color=#0000ff>internal class </FONT><FONT color=#000000>Program {</FONT>
        <FONT color=#0000ff>private static void </FONT><FONT color=#000000>Main(</FONT><FONT color=#0000ff>string</FONT><FONT color=#000000>[] args) {</FONT>
            <FONT color=#0000ff>string </FONT><FONT color=#000000>xml = ...; </FONT><FONT color=#008000>// XML string here</FONT>

            <FONT color=#000000>RouteList routes = </FONT><FONT color=#0000ff>new </FONT><FONT color=#000000>RouteList(xml);</FONT>

            <FONT color=#808000>Console</FONT><FONT color=#000000>.Out.WriteLine(routes.Count);</FONT>

            <FONT color=#0000ff>foreach</FONT><FONT color=#000000>(Route route </FONT><FONT color=#0000ff>in </FONT><FONT color=#000000>routes) {</FONT>
                <FONT color=#808000>Console</FONT><FONT color=#000000>.Out.WriteLine(</FONT><FONT color=#ff00ff>" + Route: {0}"</FONT><FONT color=#000000>, route.Id);</FONT>

                <FONT color=#0000ff>foreach</FONT><FONT color=#000000>(Step step </FONT><FONT color=#0000ff>in </FONT><FONT color=#000000>route.Steps) {</FONT>
                    <FONT color=#808000>Console</FONT><FONT color=#000000>.Out.WriteLine(</FONT><FONT color=#ff00ff>"   + Step: {0}"</FONT><FONT color=#000000>, step.Id);</FONT>
                <FONT color=#000000>}</FONT>
            <FONT color=#000000>}</FONT>
        <FONT color=#000000>}</FONT>
    <FONT color=#000000>}</FONT>
    
    <FONT color=#0000ff>public abstract class </FONT><FONT color=#000000>XmlDeserializingList&lt;Titem&gt; : List&lt;Titem&gt; {</FONT>
        <FONT color=#0000ff>protected </FONT><FONT color=#000000>XmlDeserializingList() { }</FONT>

        <FONT color=#0000ff>protected </FONT><FONT color=#000000>XmlDeserializingList(</FONT><FONT color=#0000ff>string </FONT><FONT color=#000000>xml) {</FONT>
            <FONT color=#808000>StringReader </FONT><FONT color=#000000>reader = </FONT><FONT color=#0000ff>new </FONT><FONT color=#808000>StringReader</FONT><FONT color=#000000>(xml);</FONT>

            <FONT color=#808000>XmlSerializer </FONT><FONT color=#000000>serializer = </FONT><FONT color=#0000ff>new </FONT><FONT color=#808000>XmlSerializer</FONT><FONT color=#000000>(GetType());</FONT>
            <FONT color=#000000>XmlDeserializingList&lt;Titem&gt; items = (XmlDeserializingList&lt;Titem&gt;)serializer.Deserialize(reader);</FONT>

            <FONT color=#000000>AddRange(items);</FONT>
        <FONT color=#000000>}</FONT>
    <FONT color=#000000>}</FONT>

    <FONT color=#000000>[XmlRoot(</FONT><FONT color=#ff00ff>"Routes"</FONT><FONT color=#000000>)]</FONT>
    <FONT color=#0000ff>public class </FONT><FONT color=#000000>RouteList : XmlDeserializingList&lt;Route&gt; {</FONT>
        <FONT color=#0000ff>public </FONT><FONT color=#000000>RouteList() {}</FONT>

        <FONT color=#0000ff>public </FONT><FONT color=#000000>RouteList(</FONT><FONT color=#0000ff>string </FONT><FONT color=#000000>xml) : </FONT><FONT color=#0000ff>base</FONT><FONT color=#000000>(xml) {</FONT>
            
        <FONT color=#000000>}</FONT>
    <FONT color=#000000>}</FONT>

    <FONT color=#000000>[Serializable]</FONT>
    <FONT color=#0000ff>public class </FONT><FONT color=#000000>Route {</FONT>
        <FONT color=#0000ff>private int </FONT><FONT color=#000000>id;</FONT>
        <FONT color=#0000ff>private string </FONT><FONT color=#000000>title;</FONT>
        <FONT color=#0000ff>private </FONT><FONT color=#000000>Collection&lt;Step&gt; steps;</FONT>

        <FONT color=#000000>[</FONT><FONT color=#808000>XmlAttribute</FONT><FONT color=#000000>]</FONT>
        <FONT color=#0000ff>public int </FONT><FONT color=#000000>Id {</FONT>
            <FONT color=#000000>get { </FONT><FONT color=#0000ff>return </FONT><FONT color=#000000>id; }</FONT>
            <FONT color=#000000>set { id = value; }</FONT>
        <FONT color=#000000>}</FONT>

        <FONT color=#000000>[</FONT><FONT color=#808000>XmlAttribute</FONT><FONT color=#000000>]</FONT>
        <FONT color=#0000ff>public string </FONT><FONT color=#000000>Title {</FONT>
            <FONT color=#000000>get { </FONT><FONT color=#0000ff>return </FONT><FONT color=#000000>title; }</FONT>
            <FONT color=#000000>set { title = value; }</FONT>
        <FONT color=#000000>}</FONT>

        <FONT color=#000000>[XmlArray(</FONT><FONT color=#ff00ff>"Steps"</FONT><FONT color=#000000>), XmlArrayItem(</FONT><FONT color=#ff00ff>"Step"</FONT><FONT color=#000000>, </FONT><FONT color=#0000ff>typeof</FONT><FONT color=#000000>(Step))]</FONT>
        <FONT color=#0000ff>public </FONT><FONT color=#000000>Collection&lt;Step&gt; Steps {</FONT>
            <FONT color=#000000>get { </FONT><FONT color=#0000ff>return </FONT><FONT color=#000000>steps; }</FONT>
            <FONT color=#000000>set { steps = value; }</FONT>
        <FONT color=#000000>}</FONT>
    <FONT color=#000000>}</FONT>

    <FONT color=#000000>[Serializable]</FONT>
    <FONT color=#0000ff>public class </FONT><FONT color=#000000>Step {</FONT>
        <FONT color=#0000ff>private int </FONT><FONT color=#000000>id;</FONT>
        <FONT color=#0000ff>private string </FONT><FONT color=#000000>title;</FONT>

        <FONT color=#000000>[</FONT><FONT color=#808000>XmlAttribute</FONT><FONT color=#000000>]</FONT>
        <FONT color=#0000ff>public int </FONT><FONT color=#000000>Id {</FONT>
            <FONT color=#000000>get { </FONT><FONT color=#0000ff>return </FONT><FONT color=#000000>id; }</FONT>
            <FONT color=#000000>set { id = value; }</FONT>
        <FONT color=#000000>}</FONT>

        <FONT color=#000000>[</FONT><FONT color=#808000>XmlAttribute</FONT><FONT color=#000000>]</FONT>
        <FONT color=#0000ff>public string </FONT><FONT color=#000000>Title {</FONT>
            <FONT color=#000000>get { </FONT><FONT color=#0000ff>return </FONT><FONT color=#000000>title; }</FONT>
            <FONT color=#000000>set { title = value; }</FONT>
        <FONT color=#000000>}</FONT>
    <FONT color=#000000>}</FONT>
<FONT color=#000000>}</FONT></FONT>

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()

M	T	W	T	F	S	S
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

FOR XML Needs More Love

You may also like...

Recent Posts

Recommended

Calendar

Browse by Category

Browse Archives

Meta

FOR XML Needs More Love

SHARE

You may also like...

Fun in Philly

Workshop : Configurable DTS Packages

Updating Reporting Services 2000 Connection Info

Recent Posts

Recommended

Calendar

Browse by Category

Browse Archives

Meta