xsl render children of different parent nodes and sort

Question

I'm a newbie to XML/XSL and could use some need help displaying and sorting values that are stored within the child nodes of two different parents (i.e., the data are "cousins").

The XML file includes a schedule of events happening today in different rooms of our building. Each event is represented by the <event> tag, which contains <space> and <event_details> nodes, each of which has child nodes where the values I want to display and sort are stored. It basically has the following structure (using an example with two records below, with closing tags removed for readability):

<schedule>

    <event>
        <space>
            <space_name>Room 210
        <event_details>
            <event_name>English Literature
            <event_startandendtime>9:20am to 10:30am
    <event>
        <space>
            <space_name>Room 103
        <event_details>
            <event_name>Advanced Calculus
            <event_startandendtime>8:00am to 9:10am 

Using XSL, I am trying to output the schedule with the events listed in order by date in the following format:

event_startandendtime, event_name, space_name
event_startandendtime, event_name, space_name

For example:

8:00am to 9:10am, Advanced Calculus, Room 103
9:20am to 10:30am, English Literature, Room 210

So far, I've been unsuccessful. In one attempt to code it, I can generate a sorted list but I can't get the space_name to appear. In another attempt, I can get the space_name, but can't get a sorted list, nor do I have much control over where to place the space_name.

Is this a straightforward coding task or a much more complicated one for a newbie? Thanks a lot for any directions you can point me in!

BeiJi

UPDATE: I think I got it with all your help! Here's some of the code...

Screenshot of XML Code

Screenshot of XSL Code

BROWSER VIEW:

08:00 - 09:10 : Advanced Calculus, Room 103
09:20 - 10:30 : English Literature, Room 210
13:00 - 15:20 : Beginning Calculus, Room 305

Conversion of date from 24 hours to 12 hours not working yet, but thanks again!

Answer 1

UPDATE: I think I got it with all your help! Here's some of the code...

XML: Screenshot of XML Code

XSL: Screenshot of XSL Code

BROWSER VIEW:

08:00 - 09:10 : Advanced Calculus, Room 103 09:20 - 10:30 : English Literature, Room 210 13:00 - 15:20 : Beginning Calculus, Room 305

Conversion of date from 24 hours to 12 hours not working yet, but thanks again!

Answer 2

I fixed your xml file

xml

<Root>
<event>
    <space>
        <space_name>Room 210</space_name>
    </space>
    <event_details>
        <event_name>English Literature</event_name>
        <event_startandendtime>9:20am to 10:30am</event_startandendtime>
    </event_details>
</event>
<event>
    <space>
        <space_name>Room 103</space_name>
    </space>
    <event_details>
        <event_name>Advanced Calculus</event_name>
        <event_startandendtime>8:00am to 9:10am</event_startandendtime>
    </event_details>
</event>
</Root> 

Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Linq;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        const string FILENAME = @"c:\temp\test.xml";
        public Form1()
        {
            InitializeComponent();

            DataTable dt = new DataTable();
            dt.Columns.Add("Room", typeof(string));
            dt.Columns.Add("Event", typeof(string));
            dt.Columns.Add("Time", typeof(string));

            XDocument doc = XDocument.Load(FILENAME);

            foreach(XElement _event in doc.Descendants("event"))
            {
                dt.Rows.Add(new object[] {
                   (string)_event.Descendants("space_name").FirstOrDefault(),
                   (string)_event.Descendants("event_name").FirstOrDefault(),
                   (string)_event.Descendants("event_startandendtime").FirstOrDefault()
               });
            }

            dt = dt.AsEnumerable()
                .OrderBy(x => x.Field<string>("Room"))
                .ThenBy(x => x.Field<string>("Time"))
                .ThenBy(x => x.Field<string>("Event"))
                .CopyToDataTable();

            dataGridView1.DataSource = dt;

        }
    }
}

Answer 3

Your data provider is being unkind to you by not structuring the data properly - and you are being unkind to us by not providing an example we could use for testing as is.

Still, here's a way you could look at it:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/root">
    <body>
        <xsl:for-each select="event">
            <!-- 1. AM before PM -->
            <xsl:sort select="contains(substring-before(event_details/event_startandendtime, ' '), 'pm')" data-type="text" order="ascending"/>
            <!-- 2. Sort by hour (convert 12 to 0) -->
            <xsl:sort select="substring-before(event_details/event_startandendtime, ':') mod 12" data-type="number" order="ascending"/>
            <!-- 3. Sort by minute -->
            <xsl:sort select="substring(substring-after(event_details/event_startandendtime, ':'), 1, 2)" data-type="number" order="ascending"/>
             <!-- output -->           
            <xsl:value-of select="event_details/event_startandendtime"/>
            <xsl:text>, </xsl:text>
            <xsl:value-of select="event_details/event_name"/>
            <xsl:text>, </xsl:text>
            <xsl:value-of select="space/space_name" disable-output-escaping="yes"/>
            <br/>
        </xsl:for-each>
    </body>
</xsl:template>

</xsl:stylesheet>

Applied to the following test input:

XML

<root>
  <event>
    <space>
      <space_name>Room 210</space_name>
    </space>
    <event_details>
      <event_name>English Literature</event_name>
      <event_startandendtime>9:20am to 10:30am</event_startandendtime>
    </event_details>
  </event>
   <event>
    <space>
      <space_name>Room 305</space_name>
    </space>
    <event_details>
      <event_name>Beginning Calculus</event_name>
      <event_startandendtime>1:00pm to 3:20pm</event_startandendtime>
    </event_details>
  </event>
 <event>
    <space>
      <space_name>Room 103</space_name>
    </space>
    <event_details>
      <event_name>Advanced Calculus</event_name>
      <event_startandendtime>8:00am to 9:10am</event_startandendtime>
    </event_details>
  </event>
</root>

the (rendered) result will be:

8:00am to 9:10am, Advanced Calculus, Room 103
9:20am to 10:30am, English Literature, Room 210
1:00pm to 3:20pm, Beginning Calculus, Room 305