<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- A Prompt use for XSLT
John Spears, of the SQL Prompt team at Red-Gate, shows us how easy it is to use XSLT to transform an XML file such as the SQL Prompt snippets, to print it out, or share snippets across a development team. Supporting files are in the speech bubble above.
-->
<!--
XSLT is one of those tools whose use is open-ended. Once you know how to use it, it can be handy for many tasks that involve creating files in a number of different formats from the data in xml files. The target format can be specified precisely by the template file, and can include all manner of forms such as HTML, java or C# classes, CSV, PostScript, TeX or other print formats. This versatility was brought home to me last month, when Jeff Hawkins of VillagePress asked me if there was any way to print a list of snippets from SQL Prompt. Currently SQL Prompt does not come with this built in, but the snippets file is XML so we can use XSLT to produce an HTML file suitable for printing. XSLT is purpose-built for this sort of task which would apply to all sorts of XML-based data
(If you are not interested in XSLT but want so see the SQL Prompt snippets, a pdf containing the default SQL Prompt snippets is included in the download package.)
Making An HTML file of snippets
-->
<!-- We start with a template for a bare-bones HTML file -->
<xsl:template match="/">
<html>
<head>
<!-- This stylesheet is included in the download -->
<link rel="stylesheet" href="snippets.css" />
</head>
<body>
<h1>SQL Prompt Snippets</h1>
<xsl:apply-templates select="/SnippetManager/SnippetCategories/SnippetCategory" />
</body>
</html>
</xsl:template>
<!-- For each category in the snippets file we add
a heading and all the snippets it contains -->
<xsl:template match="SnippetCategory">
<xsl:variable name="cat" select="CategoryID" />
<div>
<h2><xsl:value-of select="Name" /></h2>
<table cellspacing="0">
<!-- Snippets are matched to categories based on their category ID -->
<xsl:apply-templates select="/SnippetManager/Snippets/Snippet[child::Category=$cat]" />
</table>
</div>
</xsl:template>
<!-- Show the name and description for each snippet -->
<xsl:template match="Snippet">
<tr>
<!-- We alternate the row background colors to make it easier to read -->
<xsl:if test="position() mod 2 != 1">
<xsl:attribute name="style">background-color:#eee</xsl:attribute>
</xsl:if>
<td class="snippet">
<xsl:value-of select="Shortcut" />
</td>
<td class="description">
<xsl:value-of select="Description" />
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
<!--
To use this stylesheet you will need an xslt processor. I recommend nxslt2 (http://www.xmllab.net/Products/nxslt2/tabid/73/Default.aspx) - it is very simple, and it works. Download it and put it somewhere in your path. You will also need your SQL Prompt snippets file, which you can find at the location below.
Windows 2000 and XP: %USERPROFILE%\Local Settings\Application Data\Red Gate\SQL Prompt 3\SQL Prompt 3 Snippets.xml
Windows Vista: %LOCALAPPDATA%\Red Gate\SQL Prompt 3\SQL Prompt 3 Snippets.xml
Run nxslt2 from the command line to generate a printable snippets file
> nxslt2.exe "SQL Prompt 3 Snippets.xml" print-styling.xsl -o snippets.html
---------------------------------------Snip here -->
-->
<?xml version="1.0" encoding="utf-8" ?>
<!-- Merging snippet files -->
<!--
Another common request is to share snippets across a team. This is easy if you have one set of snippets everyone should use - you just add the snippets on one machine and then copy the snippets file to everyone. However sometimes people will have their own existing snippets they do not want to lose. Using XSLT we can easily merge two snippet files.
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes" />
<!--
For this stylesheet we will take a user's existing snippets file, but we also
need the file containing the snippets we want to import. The second filename is
specified by a parameter, and loaded explicitly.
-->
<xsl:param name="ExtraSnippetsFile" />
<xsl:variable name="ExtraSnippets" select="document($ExtraSnippetsFile)" />
<xsl:variable name="MainSnippets" select="/" />
<!-- The skeleton for an empty snippets file -->
<xsl:template match="/">
<SnippetManager>
<SnippetCategories>
<!-- Process all categories from both files -->
<xsl:apply-templates
select="/SnippetManager/SnippetCategories/SnippetCategory"
mode="display" />
<xsl:apply-templates
select="$ExtraSnippets/SnippetManager/SnippetCategories/SnippetCategory"
mode="imported" />
</SnippetCategories>
<Snippets>
<!-- Process all snippets from both files -->
<xsl:apply-templates
select="/SnippetManager/Snippets/Snippet"
mode="display" />
<xsl:apply-templates
select="$ExtraSnippets/SnippetManager/Snippets/Snippet"
mode="imported" />
</Snippets>
</SnippetManager>
</xsl:template>
<!--
For categories from the secondary file, we only include them if there is not a category with the same name in the primary file.
-->
<xsl:template match="SnippetCategory" mode="imported">
<xsl:variable name="cat" select="string(Name)" />
<xsl:if test="count($MainSnippets/SnippetManager/SnippetCategories/SnippetCategory[child::Name=$cat])=0">
<xsl:apply-templates select="." mode="display" />
</xsl:if>
</xsl:template>
<!--
Categories may exist with the same name but differing IDs in the different files. If this is the case CategoryIDs in the secondary file will not be correct for the output file. To solve this we look up all category names based on the CategoryID from the containing file, then convert this back to an ID based on the primary file. If there is no match in the primary file we assume it is a category only in the secondary file and just use the ID provided.
-->
<xsl:template match="Snippet/Category" mode="display">
<xsl:copy>
<xsl:variable name="catID" select="string(.)" />
<xsl:variable name="catName" select="/SnippetManager/SnippetCategories/SnippetCategory[child::CategoryID=$catID]/Name" />
<xsl:choose>
<xsl:when test="count($MainSnippets/SnippetManager/SnippetCategories/SnippetCategory[child::Name=$catName])!=0">
<xsl:value-of select="$MainSnippets/SnippetManager/SnippetCategories/SnippetCategory[child::Name=$catName]/CategoryID" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string(.)" />
</xsl:otherwise>
</xsl:choose>
</xsl:copy>
</xsl:template>
<!--
For snippets in the secondary file, we only include them if their shortcut does not conflict with a shortcut in the primary file
-->
<xsl:template match="Snippet" mode="imported">
<xsl:variable name="sc" select="string(Shortcut)" />
<xsl:if test="count($MainSnippets/SnippetManager/Snippets/Snippet[child::Shortcut=$sc])=0">
<xsl:apply-templates select="." mode="display" />
</xsl:if>
</xsl:template>
<!--
The default action is to copy text but throw away all tags, here we change this so that tags are kept as well
-->
<xsl:template match="*" mode="display">
<xsl:copy>
<xsl:apply-templates mode="display" />
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
<!--
As with the print styling you will need an xslt processor and your SQL Prompt snippets file. You will also need a secondary file containing some additional snippets. I have included a sample file with some additional snippets in the download to help you try it out
> nxslt2 "SQL Prompt 3 Snippets.xml" merge-snippets.xsl -o "New Snippets.xml" ExtraSnippetsFile="Extra Snippets.xml"
You will then need to make sure SQL Prompt isn't running and replace your snippets file with the new one you have just created (New Snippets.xml)
-->