Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Luke Johnson 61 posts 80 karma points
    Oct 25, 2011 @ 19:37
    Luke Johnson
    0

    Embedded for-each, or apply-template to display 'batches' of SQL data

    I am building a course list for a college and seminary, and need to be able to display courses by study areas.

    For example:

    Biblical Studies (BLST)

    • BLST 103 Gospels
    • BLST 104 Acts of the Apostles
    • BLST 300 Romans

    Greek (GRK)

    • GRK 246 Introductory Greek I
    • GRK 247 Introductory Greek II
    • GRK 363 Greek Syntax I

    (ect, ect...)

    I have a course index page that lists areas of study, and when clicked on, the studyareaID is passed to a page that receives it, and displays only courses with that same studyareaID.

    I am having trouble building a "list all courses" page that displays all the courses grouped by study areas (e.g., under the headings "Biblical Studies" or "Greek").

    When I insert the study area heading into the for-each, the heading prints every time a course listing is printed. Example:

     

     

    Biblical Studies

    • BLST 103 Gospels

     

    Biblical Studies

    • BLST 104 Acts of the Apostles

     

    Biblical Studies

    • BLST 300 Romans

     What is the best way to clump SQL data into groups? Is there a way to embed for-each statements? Something like:

    <xsl:for-each ...>
      <span>Area of Study Title</span>
      <!-- embedded for-each -->
      <xsl:for-each ...>
        <xsl:value-of select = "studyareacode" />
        <xsl:value-of select = "coursenumber" />
        <xsl:value-of select = "coursetitle" />
      </xsl:for-each>
    </xsl:for-each>

    Or are apply-templates better to use here? I haven't set those up before, but perhaps that is the better option in this case?

    Are there any pitfalls to be aware of?

    Thanks,

    Luke

  • Chriztian Steinmeier 2706 posts 8117 karma points MVP 3x admin c-trib
    Oct 25, 2011 @ 20:22
    Chriztian Steinmeier
    1

    Hi Luke,

    I was with you all the way until you said "SQL" :-)

    OK, jokes aside, using apply-templates will usually force you to think about the units right away (e.g. "Course", "Study" etc.) - so you'd have a match template for those, and just apply templates as needed.

    I would *always* recommend starting with match templates and apply-templates - it's pretty easy to switch between the two, if needed.

    But, I'd say that the XML format is pretty important, because it could be badly structured (if often is, when exported directly from a relational database).

    Do you have a rough structure of the XML we could have a look at?

    /Chriztian 

  • Chriztian Steinmeier 2706 posts 8117 karma points MVP 3x admin c-trib
    Oct 25, 2011 @ 20:26
    Chriztian Steinmeier
    1

    - and regarding the grouping: If you're using SQL you should *definitely* do the grouping there, before getting the XML (I'm assuming you're using some kind of SQL2XML extension) - grouping is tricky to do (and even harder to understand) in XSLT, whereas in SQL it's just a GROUP BY away...

    /Chriztian

  • Luke Johnson 61 posts 80 karma points
    Oct 25, 2011 @ 23:04
    Luke Johnson
    0

    I tried to set up the apply-templates, but I'm not confident that I've done it right. Nothing prints to the page. Here is the XSLT. Any glaring mistakes?

     

      <xsl:variable name = "courselist" select="SQL:GetDataSet('DBname', 'SELECT course_StudyArea, course_Name, course_College, course_Description, course_Prerequisite, course_Number, course_Hours, course_Display, area_Id, area_Title, area_Code FROM web_Courses INNER JOIN web_Areas_of_Study ON web_Courses.course_StudyArea=web_Areas_of_Study.area_Id WHERE web_Courses.course_College = 1 AND web_Courses.course_Display = 1 AND web_Areas_of_Study.area_Display = 1', 'courselist')"/>

      <xsl:template match="/">
          <xsl:apply-templates />
      </xsl:template>
        
      <xsl:template match = "studyarea">
        
          <section id = "coursecontent">
            <xsl:for-each select="$courselist//courselist">
              <xsl:sort select = "area_Code" order = "ascending"/>
              <h2><xsl:value-of select = "area_Title" /></h2>
              
              <xsl:apply-templates select = "course" />
             
            </xsl:for-each>
          </section><!-- courselist -->
            
        </xsl:template>
        
        <xsl:template match = "course">
    <!-- I didn't know what to put for this for-each's select value -->
          <xsl:for-each select = "studyarea">
          <xsl:sort select = "course_Number" order = "ascending"/>
          <article class = "course">
            <h3><!-- print "BLST 103 Gospels (3)" -->
              <xsl:value-of select = "area_Code" />&nbsp;
              <xsl:value-of select = "course_Number" />&nbsp;
              <xsl:value-of select = "course_Name" />&nbsp;
              (<xsl:value-of select = "course_Hours" />)
            </h3>
            <p><xsl:value-of select = "course_Description" /></p>
            <xsl:if test = "course_Prerequisite != ''">
              <p><strong>Prerequisite(s): <xsl:value-of select = "course_Prerequisite" /></strong></p>
            </xsl:if>
          </article>
          </xsl:for-each>
        </xsl:template>

     

  • Chriztian Steinmeier 2706 posts 8117 karma points MVP 3x admin c-trib
    Oct 25, 2011 @ 23:19
    Chriztian Steinmeier
    1

    Hi Luke,

    Just dump the XML from the SQL call in the root template and we'll take it from there:

    <xsl:template match="/">
        <textarea rows="8" cols="40"><xsl:copy-of select="$courselist" /></textarea>
    </xsl:template>

    - and we just need the basic structure, i.e., something like this:

    <courselist>
        <course_StudyArea></course_StudyArea>
        <course_Name></course_Name>
        <!-- etc. -->
    </courselist>
    
    (and that's just my guess, based on the SQL expression)

    /Chriztian

  • Luke Johnson 61 posts 80 karma points
    Oct 25, 2011 @ 23:47
    Luke Johnson
    0

    Here is the resulting XML (for one record):

    <courselists>
      <courselist>
        <course_StudyArea>4</course_StudyArea>
        <course_Name>Bible Origins: Text, Transmission, and Canon</course_Name>
        <course_College>1</course_College>
        <course_Description>A study of the origin . . .</course_Description>
        <course_Prerequisite>BLST 111 Old Testament Literature</course_Prerequisite>
        <course_Number>206</course_Number>
        <course_Hours>3</course_Hours><course_Display>1</course_Display>
        <area_Id>4</area_Id>
        <area_Title>Biblical Studies</area_Title>
        <area_Code>BLST</area_Code>
      </courselist>
    </courselists>
  • Chriztian Steinmeier 2706 posts 8117 karma points MVP 3x admin c-trib
    Oct 26, 2011 @ 00:32
    Chriztian Steinmeier
    2

    Hi Luke,

    Well, that's kind of what I meant with "directly from a relational database" - but it's possible to work with - here's a big fat chunk - but notice that it's neatly organized:

    <?xml version="1.0" encoding="utf-8" ?>
    <xsl:stylesheet
        version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:umb="urn:umbraco.library"
    xmlns:SQL="urn:SOMETHING-SQL-HERE"
        exclude-result-prefixes="umb SQL"
    >
    
        <xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />
    
        <xsl:param name="currentPage" />
    
        <xsl:variable name="courselist" select="SQL:GetDataSet('DBname',
            'SELECT course_StudyArea, course_Name, course_College,
                course_Description, course_Prerequisite, course_Number,
                course_Hours, course_Display, area_Id, area_Title, area_Code
            FROM web_Courses
            INNER JOIN web_Areas_of_Study
            ON web_Courses.course_StudyArea = web_Areas_of_Study.area_Id
            WHERE web_Courses.course_College = 1
                AND web_Courses.course_Display = 1
                AND web_Areas_of_Study.area_Display = 1',
            'courselist')"
        />
    
        <!-- Index courses by area_Code -->
        <xsl:key name="course-by-area_Code" match="courselist" use="area_Code" />
    
        <xsl:template match="/">
            <!-- This is the grouping part... take a leap of faith :-) -->
            <xsl:for-each select="$courselist//courselist[count(. | key('course-by-area_Code', area_Code)[1]) = 1]">
                <xsl:sort select="area_Code" order="ascending" />
                <h2>
                    <xsl:value-of select="area_Title" />
                </h2>
    
                <xsl:apply-templates select="key('course-by-area_Code', area_Code)" />
            </xsl:for-each>
        </xsl:template>
    
        <!-- Template for a course -->
        <xsl:template match="courselist">
            <article class="course">
                <h3>
                    <!-- print "BLST 103 Gospels (3)" -->
                    <xsl:value-of select="concat(area_Code, ' ', course_Number, ' ', course_Name, ' (', course_Hours, ')')" />
                </h3>
    
                <xsl:apply-templates select="course_Description" />
    
                <xsl:apply-templates select="course_Prerequisite" />
    
            </article>
        </xsl:template>
    
        <xsl:template match="course_Description">
            <p>
                <xsl:value-of select="." />
            </p>
        </xsl:template>
    
        <xsl:template match="course_Prerequisite">
            <p>
                <strong>Prerequisite(s): <xsl:value-of select="." /></strong>
            </p>
        </xsl:template>
    
        <xsl:template match="courselist/*[not(normalize-space())]">
            <!-- No output for empty elements -->
        </xsl:template>
    
    </xsl:stylesheet>

    Ask away, if you want to know how it works (if it does at all :-)

    /Chriztian

  • Luke Johnson 61 posts 80 karma points
    Oct 26, 2011 @ 17:20
    Luke Johnson
    0

    A million thank-you's! That worked like a charm! And, yes, I do have some questions:

    • I haven't used an xsl:key before. How do these work?
      - is the "name" value arbitrary (semantic rather than functional)?
      - "match" declares which template this information will be applied to?
      - "use", I assume, declares which table column acts as a 'parent' for the group?
    • It looks like the "." in <xsl:value-of select="." /> tells the server to look at the match="" value, rather than having to redundantly restate value-of statements.
    • <xsl:template match="courselist/*[not(normalize-space())]"> is an alternative to <xsl:if test = "courselist != ''">? I assume, since this whole structure is built using applied templates, this is the template way to know if there is content to be displayed.
    • for this line: <xsl:for-each select="$courselist//courselist[count(. | key('course-by-area_Code', area_Code)[1]) = 1]">
      - I recognize the $courselist//courselist part
      - I see that key('course-by....) corresponds to the xsl:key line.
      - Looking at the "count" bit, I see that it's bracketed off to include count(info from the xsl:key) with [1] = 1. What do these 1's look for? By referencing "key", it must load 1 area_Code, and then process all the for-each stuff on all the courses that have the same area_Code. Is that right? 
    This is cool stuff. I learned my new thing for the day in my first 15 minutes of work! :)
    Thanks again for your help!
  • Luke Johnson 61 posts 80 karma points
    Oct 26, 2011 @ 18:16
    Luke Johnson
    0

    I edited it a bit to sort by course number within the study area:

    <xsl:apply-templates select="key('course-by-area_Code', area_Code)">
     <xsl:sort select="course_Number"/>
    </xsl:apply-templates>

    Works great. There is so much more flexibility with apply-templates! Thanks for your help.

  • Chriztian Steinmeier 2706 posts 8117 karma points MVP 3x admin c-trib
    Oct 26, 2011 @ 19:45
    Chriztian Steinmeier
    5

     

    Hi Luke,

    I'm glad you're hooked on templates - please tell all your friends :-)

    I'll try to address your questions:

    * Yes, the name is totally arbitrary (can even be namespaced)

    * The match is like any match attribute - you specify a pattern which is (in essence) matched against all nodes in the source XML. It's NOT a reference to any existing template - rather a criterion for a node to be indexed. (Makes sense?)

    * The use attribute is the value that gets stored in the index for that particular node, and thus, the value that you will be able to access nodes by.

    So the <xsl:key> instruction creates a named index of all nodes that match the expression in the match attribute, and stores them in a kind of simple key/value table. The key() function will return all the nodes in the named index that has been stored with a given value... example:

    <!-- XML Data -->
    <people>
        <person>
            <firstname>Luke</firstname>
            <lastname>Johnson</lastname>
        </person>
        <person>
            <firstname>Don</firstname>
            <lastname>Johnson</lastname>
        </person>
        <person>
            <firstname>Luke</firstname>
            <lastname>Skywalker</lastname>
        </person>
        <person>
            <lastname>Johnson</lastname>
        </person>
    </people>
    
    <!-- Key definition -->
    <xsl:key name="people-by-lastname" match="person" use="lastname" />
    
    <!-- Select all the <person> nodes with lastname Johnson -->
    <xsl:variable name="theJohnsons" select="key('people-by-lastname', 'Johnson')" />
    
    <!-- To exclude the last one with no firstname, you could define the key like this: -->
    <xsl:key name="people-by-lastname" match="person[../lastname]" use="lastname" />
    

    The "." always refers to the "current context node" which can be a little confusing at first, but basically, if you're inside a for-each it will refer to the node being processed, otherwise it will likely be the matched node of the template being processed.

     

    Regarding the template for empty elements: In XML, the following are all "empty" elements:

    <p></p>
    <p>      </p>
    <p />
    <p>
    
    
    
    </p>

    - but an expression like test="p != '' " will only match 2 of them! The normalize-space() function collapses all leading and trailing whitespace, so test="p[not(normalize-space())]" will catch all of them. (a test="expression" will perform a boolean() on the resulting nodeset from the expression - an empty nodeset returns false()).

    The magic part is that when the processor stands in the door to the big room with all the defined templates, holding the next element to be processed (forgive the metaphorics :-) and more than one template would match the node - it will take the one that's closest to the door (i.e., defined last in the stylesheet). There are of course rules of being more specific and priority that you can google on this, but stick with this for now, ok? :-)

     

    Lastly, the grouping stuff:

    You should really google "Muenchian Grouping", but that cryptic selection revolves around getting distinct values...

     

    For every <courselist> node in the $courselist variable it will take its <area_Code> and use the key() function to get all the <courselist> nodes that has the same <area_Code> as the current one, but it will only take the first ( [1] ) from the set returned by the key. The current node of the for-each and the returned first node of the key() are then put into a set (the pipe "|" character concatenates sets/nodes). Because the same node can only exist once in a set, the count() of nodes in that set will be exactly 1 if (and _only_ if) the current node of the for-each is exactly the same node as the first node returned from the key(), thus the for-each will in effect iterate over all the distinct values of <area_Code>s... Jeeeez Louise :-)

    Grouping is definitely where people say --- "Hmm... maybe I should take a look at that new hot Razor thing?" :-)

    Cheers,

    /Chriztian

     

  • Luke Johnson 61 posts 80 karma points
    Oct 26, 2011 @ 22:46
    Luke Johnson
    0

    Wow, thanks! This has been a lot of help. I definitely have lots to learn! But this has really got me moving along.

    Thanks.

    Luke

Please Sign in or register to post replies

Write your reply to:

Draft