Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Since the input XML is in a rather bad shape, we must jump through some hoops to get what you want. </p> <p>As Mister Lucky already outlined in his answer, we must transform the document into a more useful temporary form first. The more useful temporary form would then be turned back into a node-set by an extension function, and processed again to produce the desired result.</p> <p>In my answer I'll use a modified identity transform to achieve the following temporary form:</p> <pre><code>&lt;root&gt; &lt;node id="1"&gt; &lt;data alias="Show"&gt;ShowName1&lt;/data&gt; &lt;data alias="Dates"&gt; &lt;date sort="20090421"&gt;21/04/2009&lt;/date&gt; &lt;date sort="20090423"&gt;23/04/2009&lt;/date&gt; &lt;date sort="20090427"&gt;27/04/2009&lt;/date&gt; &lt;/data&gt; &lt;/node&gt; &lt;node id="2"&gt; &lt;data alias="Show"&gt;ShowName2&lt;/data&gt; &lt;data alias="Dates"&gt; &lt;date sort="20090422"&gt;22/04/2009&lt;/date&gt; &lt;date sort="20090425"&gt;25/04/2009&lt;/date&gt; &lt;date sort="20090429"&gt;29/04/2009&lt;/date&gt; &lt;/data&gt; &lt;/node&gt; &lt;/root&gt; </code></pre> <p>With this input, sorting is straightforward, using the <code>@sort</code> attribute of the <code>&lt;date&gt;</code> elements.</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl " &gt; &lt;xsl:output method="xml" indent="yes" omit-xml-declaration="yes" /&gt; &lt;xsl:template match="/"&gt; &lt;!-- prepare our temporary form (a "result tree fragment") --&gt; &lt;xsl:variable name="rtf"&gt; &lt;xsl:apply-templates mode="rtf" /&gt; &lt;/xsl:variable&gt; &lt;!-- transform the result tree fragment back to a node-set --&gt; &lt;xsl:variable name="doc" select="msxsl:node-set($rtf)" /&gt; &lt;!-- transform the temporary node-set, sorted by date --&gt; &lt;shows&gt; &lt;xsl:apply-templates select="$doc//date"&gt; &lt;xsl:sort select="@sort" /&gt; &lt;/xsl:apply-templates&gt; &lt;/shows&gt; &lt;/xsl:template&gt; &lt;xsl:template match="date"&gt; &lt;show show_id="{ancestor::node/@id}"&gt; &lt;name&gt;&lt;xsl:value-of select="../../data[@alias='Show'][1]/text()" /&gt;&lt;/name&gt; &lt;date&gt;&lt;xsl:value-of select="." /&gt;&lt;/date&gt; &lt;/show&gt; &lt;/xsl:template&gt; &lt;!-- all following templates are for producing the temporary form only --&gt; &lt;xsl:template match="@*|node()" mode="rtf"&gt; &lt;xsl:copy&gt; &lt;xsl:apply-templates select="@*|node()" mode="rtf" /&gt; &lt;/xsl:copy&gt; &lt;/xsl:template&gt; &lt;xsl:template match="data[@alias='Dates']" mode="rtf"&gt; &lt;xsl:copy&gt; &lt;!-- copy all attributes --&gt; &lt;xsl:apply-templates select="@*" mode="rtf" /&gt; &lt;!-- this produces the &lt;date&gt; elements --&gt; &lt;xsl:call-template name="tokenize-datelist" /&gt; &lt;/xsl:copy&gt; &lt;/xsl:template&gt; &lt;xsl:template name="tokenize-datelist"&gt; &lt;xsl:param name="input" select="." /&gt; &lt;xsl:param name="delim" select="','" /&gt; &lt;xsl:variable name="temp" select="concat($input, $delim)" /&gt; &lt;xsl:variable name="head" select="substring-before($temp, $delim)" /&gt; &lt;xsl:variable name="tail" select="substring-after($input, $delim)" /&gt; &lt;xsl:if test="$head != ''"&gt; &lt;date&gt; &lt;!-- this produces the @sort attribute --&gt; &lt;xsl:call-template name="tokenize-date"&gt; &lt;xsl:with-param name="input" select="$head" /&gt; &lt;/xsl:call-template&gt; &lt;xsl:value-of select="$head" /&gt; &lt;/date&gt; &lt;xsl:if test="$tail != ''" &gt; &lt;xsl:call-template name="tokenize-datelist"&gt; &lt;xsl:with-param name="input" select="$tail" /&gt; &lt;xsl:with-param name="delim" select="$delim" /&gt; &lt;/xsl:call-template&gt; &lt;/xsl:if&gt; &lt;/xsl:if&gt; &lt;/xsl:template&gt; &lt;xsl:template name="tokenize-date"&gt; &lt;xsl:param name="input" select="''" /&gt; &lt;xsl:param name="delim" select="'/'" /&gt; &lt;xsl:variable name="dd" select="substring-before($input, $delim)" /&gt; &lt;xsl:variable name="my" select="substring-after($input, $delim)" /&gt; &lt;xsl:variable name="mm" select="substring-before($my, $delim)" /&gt; &lt;xsl:variable name="yy" select="substring-after($my, $delim)" /&gt; &lt;xsl:attribute name="sort"&gt; &lt;xsl:value-of select="concat($yy, $mm, $dd)" /&gt; &lt;/xsl:attribute&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p>When this is run through msxsl.exe, an XSLT 1.0 processor, the following output is produced:</p> <pre><code>&lt;shows&gt; &lt;show show_id="1"&gt; &lt;name&gt;ShowName1&lt;/name&gt; &lt;date&gt;21/04/2009&lt;/date&gt; &lt;/show&gt; &lt;show show_id="2"&gt; &lt;name&gt;ShowName2&lt;/name&gt; &lt;date&gt;22/04/2009&lt;/date&gt; &lt;/show&gt; &lt;show show_id="1"&gt; &lt;name&gt;ShowName1&lt;/name&gt; &lt;date&gt;23/04/2009&lt;/date&gt; &lt;/show&gt; &lt;show show_id="2"&gt; &lt;name&gt;ShowName2&lt;/name&gt; &lt;date&gt;25/04/2009&lt;/date&gt; &lt;/show&gt; &lt;show show_id="1"&gt; &lt;name&gt;ShowName1&lt;/name&gt; &lt;date&gt;27/04/2009&lt;/date&gt; &lt;/show&gt; &lt;show show_id="2"&gt; &lt;name&gt;ShowName2&lt;/name&gt; &lt;date&gt;29/04/2009&lt;/date&gt; &lt;/show&gt; &lt;/shows&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload