Category Archives: Visual Studio

Visual Studio automation Snippets

Deploy database diagram changes along with table changes

I’ve often wished, when I did a database compare, that changes to sysdiagrams would be detected. That they could be incorporated into the project and that deploy could put them on the target system.  Upon reading someone else complain about the same thing it suddenly occurred to me that we could come very close to achieving this (in some cases actually achieving it) with very little effort.

Step 1:  Create a procedure that scripts a diagram to a form visible to database compare.
Step 2:  Put a trigger on sysdiagrams to run this procedure each time a diagram is updated.
Step 3:  Add a Post-Deploy script to your deployment that contains commands to execute the scripts that update the diagram.

Although most of the process is straightforward there are a couple of gotchas.  The first one is how to store the script so that compare will notice it has changed.  This was rather easy.  I script the diagram to a custom procedure named ‘UpsertDiagram_’+<diagram name>.  This results in my having a procedure for every diagram.  The trigger automatically creates/updates the procedure.  The procedure change is detected by Schema Compare and the deployment can then be automatic.  As a side effect the diagram is version controlled along with the rest of the project (because everybody version controls all their projects!).

The next gotcha is that unless you have a post-deploy that executes the procedures, the diagrams don’t actually get updated.  There isn’t much that can be done about this other than to execute the procedures manually but at least they exist and are up-to-date. I suppose a DDL trigger to execute ‘UpsertDiagram_XXX’ procedures if they are updated could work but I’ve not tried it.

The last point is that you could create a conflict if you run ‘UpsertDiagram_XXX’ procedures on a system with a trigger that updates the procedure when the diagram is updated.  A simple trick with a little known function can allow your trigger to detect this condition and prevent conflict.  The key is to use CONTEXT_INFO(). By looking for a specific value that will only exist while our custom procedure runs you can skip execution of the update procedure.

This first procedure will add a suitable trigger. The second scripts the diagram into a custom procedure.

 

CREATE PROCEDURE AutomaticallyUpdateDiagramProcedures AS
DECLARE @CMD VARCHAR(4000) = ' trigger iut_sysdiagrams on sysdiagrams for update,insert as
begin
    if OBJECT_ID(''UpsertDiagramProc'') is not null and (CONTEXT_INFO() IS NULL OR CONTEXT_INFO() != CAST(''UpsertDiagram'' AS varbinary(128)))
    begin
        declare @CMD varchar(max) = '''';

        select @CMD += CHAR(10)+''EXEC UpsertDiagramProc ''''''+name+'''''';''
          from inserted;
        
        EXEC(@CMD);
    end
end'

IF OBJECT_ID('dbo.iut_sysdiagrams') is null 
    SET @CMD = 'CREATE'+@CMD;
ELSE
    SET @CMD = 'ALTER'+@CMD;

IF OBJECT_ID('dbo.sysdiagrams') is not null
    EXEC(@CMD);

 

-- Creates a stored procedure for the named database diagram that can recreate the diagram.
-- This allows a database project to track diagram changes as well as deploy them.
-- Although deployment only deploys procedures, the created proc can be run in a post-deploy script to effect updates.
-- Note that diagrams only reference objects. They don't contain definitions for objects.  
-- So objects must exist on the new system to appear in the diagram. 
-- The references are by name not ID so the behavior most people want usually happens. 
-- The required parameter is the name of the diagram sans the principal_id.  
-- You can add support for principal_id using the function DATABASE_PRINCIPAL_ID('dbo'). 
-- This tool just copies the existing ID which is usually OK. 
CREATE PROCEDURE dbo.UpsertDiagramProc (@name VARCHAR(128),@Debug bit = 0)
AS  
BEGIN 
  DECLARE @Chunk        INT = 50;
  DECLARE @Index        INT = 1;
  DECLARE @Block        INT;
  DECLARE @Diagram_ID   INT;
  DECLARE @PrincipalID  VARCHAR(10);
  DECLARE @Version      VARCHAR(10);
  DECLARE @Size         INT;
  DECLARE @Line         VARCHAR(MAX);
  DECLARE @ProcDef      VARCHAR(MAX);
    
   -- Get PK diagram_id using the diagram's name. 
  SELECT  @Diagram_ID  = diagram_id 
         ,@Size         = DATALENGTH(definition) 
         ,@PrincipalID  = principal_id
         ,@Version      = [version]
  FROM    sysdiagrams 
  WHERE   [name] = @name;
  
  IF @Diagram_ID IS NULL  
    BEGIN 
      PRINT '/**';
      PRINT 'Diagram name [' + @name + '] could not be found.';
      PRINT '*/';
    END 
  ELSE -- Diagram exists 
    BEGIN 
      SET @ProcDef  ='IF OBJECT_ID(''UpsertDiagram_'+@name+''') IS NULL EXEC(''CREATE PROCEDURE UpsertDiagram_'+@name+' AS RETURN NULL;'');';
      IF @Debug = 1
        SELECT @ProcDef;
      ELSE
        EXEC(@ProcDef);
      SET @ProcDef='ALTER PROCEDURE UpsertDiagram_'+@name+' AS'
        + CHAR(10)+'BEGIN' 
        + CHAR(10)+'  /**' 
        + CHAR(10)+'      Restores diagram ''' + @name + '''' 
        + CHAR(10)+'    ' 
        + CHAR(10)+'      Generated with: UpsertDiagramProc ''' + @name + '''' 
        + CHAR(10)+'      Creates the [sysdiagrams] table if it doesn''t exist.' 
        + CHAR(10)+'      Restores the named diagram as it existed when this proc was created.' 
        + CHAR(10)+'    ' 
        + CHAR(10)+'    ' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + '' 
        + CHAR(10)+'     Author: Rick Bielawski' 
        + CHAR(10)+'  **/' 
        + CHAR(10)
        --            The context value must be synced with the trigger code that automatically updates this procedure upon diagram change.
        --            We don't want this procedure updated while it puts the very diagram we created it to update into the table
        + CHAR(10)+'  DECLARE @OldContext varbinary(128) = ISNULL(CONTEXT_INFO(),0X)'
        + CHAR(10)+'         ,@NewContext varbinary(128) = CAST(''UpsertDiagram'' AS varbinary(128));' 
        + CHAR(10)+'  SET CONTEXT_INFO @NewContext;'
        + CHAR(10)+'  PRINT ''=== Start creation/update of diagram in ' + DB_NAME() + ' ==='';' 
        + CHAR(10)
        + CHAR(10)+'  -- If the sysdiagrams table has not been created in this database, create it! ' 
        + CHAR(10)+'  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''sysdiagrams'') ' 
        + CHAR(10)+'    BEGIN ' 
        + CHAR(10)+'      -- Create table script generated by Sql Server Management Studio ' 
        + CHAR(10)+'      -- _Assume_ this is roughly equivalent to what Sql Server/Management Studio ' 
        + CHAR(10)+'      -- creates the first time you add a diagram to a 2008 database ' 
        + CHAR(10)+'      CREATE TABLE [dbo].[sysdiagrams]( ' 
        + CHAR(10)+'            [name] [sysname] NOT NULL, ' 
        + CHAR(10)+'            [principal_id] [int] NOT NULL, ' 
        + CHAR(10)+'            [diagram_id] [int] IDENTITY(1,1) NOT NULL, ' 
        + CHAR(10)+'            [version] [int] NULL, ' 
        + CHAR(10)+'            [definition] [varbinary](max) NULL, ' 
        + CHAR(10)+'      PRIMARY KEY CLUSTERED  ' 
        + CHAR(10)+'        ( ' 
        + CHAR(10)+'            [diagram_id] ASC ' 
        + CHAR(10)+'        )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) , ' 
        + CHAR(10)+'      CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED  ' 
        + CHAR(10)+'        ( ' 
        + CHAR(10)+'            [principal_id] ASC, ' 
        + CHAR(10)+'            [name] ASC ' 
        + CHAR(10)+'        )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF)  ' 
        + CHAR(10)+'        );  ' 
        + CHAR(10)+'      EXEC sys.sp_addextendedproperty @name=N''microsoft_database_tools_support'', ' 
        + CHAR(10)+'           @value=1 , @level0type=N''SCHEMA'',@level0name=N''dbo'', ' 
        + CHAR(10)+'           @level1type=N''TABLE'',@level1name=N''sysdiagrams''; ' 
        + CHAR(10)+'      PRINT ''[sysdiagrams] table required creation'';' 
        + CHAR(10)+'    END ' 
        + CHAR(10)+'  -- Target table will now exist, if it didn''t before' 
        + CHAR(10)+'  SET NOCOUNT ON -- Hide (1 row affected) messages' 
        + CHAR(10)+'  DECLARE @newid INT;' 
        + CHAR(10)+'  SELECT @newid = [diagram_id] from sysdiagrams where [name] = ''' + @name + ''';' 
        + CHAR(10)    
        + CHAR(10)+'  BEGIN TRY' 
        + CHAR(10)+'    IF @newid IS NULL' 
        + CHAR(10)+'    BEGIN' 
        + CHAR(10)+'        Print ''Inserting new ''''' + @name + ''''' diagram'';'
        + CHAR(10)+'        INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])' 
        + CHAR(10)+'        VALUES (''' + @name + ''', ' + @PrincipalID  + ', ' + @Version + ', 0x);' 
        + CHAR(10)+'        SET @newid = SCOPE_IDENTITY();' 
        + CHAR(10)+'    END' 
        + CHAR(10)+'    ELSE' 
        + CHAR(10)+'    BEGIN' 
        + CHAR(10)+'        PRINT ''Updating existing ''''' + @name + ''''' diagram'';' 
        + CHAR(10)+'        UPDATE sysdiagrams ' 
        + CHAR(10)+'           SET [principal_id] = ' + @PrincipalID 
        + CHAR(10)+'              ,[version]      = ' + @Version 
        + CHAR(10)+'              ,[definition]   = 0x'
        + CHAR(10)+'        WHERE diagram_id = @newid;' 
        + CHAR(10)+'    END';
      -- Output diagram data encoded as hex strings in a way that tries to be readable and comparable.
      WHILE @Index < @Size  
        BEGIN 
          SET @Block = 1;
          SET @ProcDef += CHAR(10)+CHAR(10)+'    UPDATE sysdiagrams SET [definition] .Write ( 0X' 
          -- The operation must be done in blocks because there is no way to represent binary literal values greater than 8000 bytes
          WHILE @Block < 8001-@Chunk AND @Index < @Size  
            BEGIN
              SELECT  @ProcDef += CHAR(10)+'     + ' 
                               + UPPER(sys.fn_varbintohexstr(SUBSTRING(definition, @Index, @Chunk))) + ' -- index:' + CAST(@Index AS VARCHAR) 
              FROM    sysdiagrams 
              WHERE   diagram_id = @diagram_id;
              SET @Block += @Chunk;
              SET @Index += @Chunk;
            END
          SET @ProcDef += CHAR(10)+'     , null, 0) WHERE diagram_id = @newid;';
        END 
      SET @ProcDef 
        +=CHAR(10)
        + CHAR(10)+'    PRINT ''=== Finished writing diagram id '' + CAST(@newid AS VARCHAR) + '' to ' + DB_NAME() + ' ==='';' 
        + CHAR(10)+'  END TRY' 
        + CHAR(10)+'  BEGIN CATCH' 
        + CHAR(10)+'    -- If we got here, the [definition] updates didn''t complete, so delete the diagram row' 
        + CHAR(10)+'    -- (and hope it doesn''t fail!)' 
        + CHAR(10)+'    DELETE FROM sysdiagrams WHERE diagram_id = @newid;' 
        + CHAR(10)+'    PRINT ''***** '' + Error_Message() + '' *****'';' 
        + CHAR(10)+'    PRINT ''***** END ScriptDiagram - fix the error before running again *****'';' 
        + CHAR(10)+'    RETURN' 
        + CHAR(10)+'  END CATCH' 
        + CHAR(10)+'  SET CONTEXT_INFO @OldContext;'
        + CHAR(10)+'END' 

      IF @Debug = 1
        SELECT CAST('<?M '+@ProcDef+'?'+'>' AS xml) AS ProcDef;
      ELSE
        EXEC(@ProcDef);

      PRINT 'UpsertDiagram_'+@name+' PROCEDURE Upserted.';
    END 
END

Looking for references in SSIS

This post was originally created in February of 2012 but for some reason it got deleted by Word Press with no notice or explanation. I just discovered it missing and though I can’t find a way to give the post its original URL (https://rickbielawski.wordpress.com/2012/02/22/looking-for-references-in-ssis), with the help of web.archive.org I’ve been able to restore the content.


Similar to my post of a few years ago where you may want to search an SSRS package (Querying Information from Deployed SSRS Packages) you can also search SSIS. The big difference is in the namespaces used and the fact that sometimes the package isn’t actually in a database to be queried. This query finds certain types of information in a package and returns its location.

-- This query shows exactly where in a package a query containing the search criteria (@F) can be found. 
-- Be careful in your specification of table/column names since wildcards and patterns are not allowed 
-- and some references are generated as [schema].[name] while others are just schema.name.

Declare @F varchar(100) = 'TextToFind';  --< Put the text you want to find here

With XMLNAMESPACES(N'www.microsoft.com/SqlServer/Dts'               AS DTS
                  ,N'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
                  ,N'http://www.w3.org/2001/XMLSchema'              AS xsd
                  ,N'urn:schemas-microsoft-com:xml-msdata'          AS msdata)
,   FileData AS (select cast( [SSISPkgData].BulkColumn AS XML) AS pkgXML
                   from openrowset(bulk 'H:\SomePackage.dtsx',single_blob) as [SSISPkgData])
, Found as (                                 
select Nodes.value('@SQLTask:SqlStatementSource','varchar(MAX)') Txt
      -- Notice this very cool feature.  From SSMS results window you can click on the statement to see it. 
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('@SQLTask:SqlStatementSource'
                    ,'varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//SQLTask:SqlTaskData[contains(upper-case(@SQLTask:SqlStatementSource)
                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(..)','varchar(max)') Parent
      ,Nodes.value('../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../..)','varchar(max)') GrandParent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//DTS:Property[@DTS:Name="Expression" and . != "" and contains(upper-case(.)
                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../@name','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../../../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//property[(@name="SqlCommand" or @name="SqlCommandVariable" or 
                             @name="OpenRowset" or @name="OpenRowsetVariable") and . != "" and 
                             contains(upper-case(.),upper-case(sql:variable("@F")))]') Pkg(Nodes)
)
Select Parent,ParentName,GrandParent,GrandParentName,GGParent,GGParentName,QueryText
  from Found
 --where Txt like '%update%'  -- Uncomment this Where clause to further restrict results

XSLT to transform Visual Studio Documentation to HTML

This isn’t really the greatest script but it does a fair job and makes a great starting point if you want to roll your own.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <!--                Created by Rick Bielawski - published 2011-02-18                   -->
  <!--                             last updated 2011-06-05                               -->
  <!--  To display an XML documentation file generated by Visual Studio with a browser   -->
  <!--  add the line below immediately after the ?xml version line in your XML document  -->
  <!--  then open the XML file with your browser. Obviously it must point to THIS file.  -->
  <!--        <?xml-stylesheet href="visual-studio-xml-doc.xsl" type="text/xsl"?>        -->
  <!--  Or, use an XSLT generator with this code on your XML document to create html     -->
 
  <!-- ======================================================================
       Here we declare ancilary routines used to extract data
       ====================================================================== -->
  <!--  Replace a given string with another.  Basically this is only needed because long
        parameter lists don't wrap at a comma in HTML without a space being inserted.  -->
  <xsl:template name="tReplaceSubString">
    <xsl:param name="pText" />
    <xsl:param name="pLookFor" select="','" />
    <xsl:param name="pNewValue" select="' ,'" />
    <xsl:choose>
      <xsl:when test="contains($pText,$pLookFor)">
        <xsl:value-of select="substring-before($pText,$pLookFor)" />
        <xsl:value-of select="$pNewValue" />
        <xsl:call-template name="tReplaceSubString">
          <xsl:with-param name="pText"     select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pLookFor"  select="$pLookFor" />
          <xsl:with-param name="pNewValue" select="$pNewValue" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pText" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!--  Counting the number of levels in a member's name allows me to tell
        where the name of the entity being documented starts (after the last one).  -->
  <xsl:template name="tCountOccurances">
    <xsl:param name="pText" />
    <xsl:param name="pLookFor" select="'.'" />
    <xsl:param name="pCount" select="0" />
    <xsl:choose>
      <xsl:when test="contains($pText,$pLookFor)">
        <xsl:call-template name="tCountOccurances">
          <xsl:with-param name="pText"    select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pLookFor" select="$pLookFor" />
          <xsl:with-param name="pCount"   select="$pCount + 1" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pCount" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!--  Replaces the pCount instance of pLookFor with pSeparator.  Used to split a string.
        pCount=0 doesn't look for the pLookFor.  It just puts pSeperator at the beginning.
        pCount=1 splits at the 1st occurance, 2 splits at the 2nd occurance....
        Splitting on a count greater than the number of occurances puts pSeperator at the end.  -->
  <xsl:template name="tSplitAtInstance">
    <xsl:param name="pText" />
    <xsl:param name="pCount" select="0" />
    <xsl:param name="pLookFor" select="'.'" />
    <xsl:param name="pSeparator" select="'|'" />
    <xsl:param name="pStartEmpty" />
    <xsl:choose>
      <xsl:when test="$pCount &gt; 0 and contains($pText,$pLookFor)">
        <xsl:variable name="temp">
          <xsl:if test="$pStartEmpty = ''">
            <xsl:value-of select="substring-before($pText,$pLookFor)" />
          </xsl:if>
          <xsl:if test="$pStartEmpty != ''">
            <xsl:value-of select="concat($pStartEmpty,$pLookFor,substring-before($pText,$pLookFor))" />
          </xsl:if>
        </xsl:variable>
        <xsl:call-template name="tSplitAtInstance">
          <xsl:with-param name="pText"       select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pCount"      select="$pCount - 1" />
          <xsl:with-param name="pLookFor"    select="$pLookFor" />
          <xsl:with-param name="pSeparator"  select="$pSeparator" />
          <xsl:with-param name="pStartEmpty" select="$temp" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="concat($pStartEmpty,$pSeparator,$pText)" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!-- ======================================================================
       These format individual parts of the document.
       ====================================================================== -->
 
  <!--  This seems to be the only thing that can be done generically -->
  <xsl:template name="tElementName">
    <xsl:param name="pText" />
    <span class="ElementName"><xsl:value-of select="$pText" />: </span>
  </xsl:template>
 
  <!-- ======================================================================
 
       This is where actual transformation starts
 
       ====================================================================== -->
 
  <!--  Set a var with the name of the assembly for the documented project. -->
  <xsl:variable name="vAssembly">
    <xsl:value-of select="doc/assembly/name" />
  </xsl:variable>
 
  <!--  This creates the HTML document within which the XML content is formatted. -->
  <xsl:template match="doc">
    <HTML>
      <HEAD>
        <style type="text/css">                 
          h1 {font-weight:bold; text-decoration:underline; text-align:center}
          pre {margin-top:0; margin-bottom:0}
          table {margin-left:5mm; margin-top:0; margin-bottom:0}
          tr#FirstRow {font-size:1.2em; text-decoration:underline}
          td {padding-left:2em}
          
          .AttributeName {font-weight:bold; padding-right:1em; color:blue; font-family:"Monospace"}
          .ClassName {color:red}
          .CodeItem {font-family:"Monospace"}
          .ElementName {padding-right:5px; text-transform:capitalize; font-weight:bold; font-style:italic}
          .EntityName {margin-left:2em; font-weight:bold; font-size:1.2em; font-family:"Monospace"}
          .MemberTypeName {color:blue}
 
          .summary {margin-top:0.3em; margin-bottom:0.3em}
          .returns {margin-top:0.3em; margin-bottom:0.3em}
          .exception {margin-top:0.3em; margin-bottom:0.3em}
          .remarks {margin-top:0.3em; margin-bottom:0.3em}
        </style>
        <TITLE><xsl:value-of select="$vAssembly" /> documentation</TITLE>
      </HEAD>
      <BODY>
        <h1><xsl:value-of select="$vAssembly" /> documentation</h1><p />
        <xsl:apply-templates select="members/member" />
      </BODY>
    </HTML>
  </xsl:template>
 
  <!--  This template first extracts information about the Member being formatted   -->
  <!--  Then formats the individual doc sections: summary, param, return...   -->
  <xsl:template match="member">
 
    <!-- I decided to use the <para> tag to flag the entity as public.  This way I can
         document internal stuff for intellisense but not for users.  It works well
         since intellisense ignores <para> formatting anyway.  -->
    <xsl:if test="count(summary/para) > 0">
 
      <!--  The whole name has the type, namespace, class name, and member name.   -->
      <xsl:variable name="vWholeName">
        <xsl:value-of select="@name" />
      </xsl:variable>
 
      <!--  The type of member being documented is encoded in the first character   -->
      <xsl:variable name="vMemberType">
        <xsl:value-of select="substring-before($vWholeName,':')" />
      </xsl:variable>
      <xsl:variable name="vMemberTypeName">
        <xsl:choose>
          <xsl:when test="$vMemberType = 'E'">Event</xsl:when>
          <xsl:when test="$vMemberType = 'F'">Field</xsl:when>
          <xsl:when test="$vMemberType = 'M'">Method</xsl:when>
          <xsl:when test="$vMemberType = 'N'">NameSpace</xsl:when>
          <xsl:when test="$vMemberType = 'P'">Property</xsl:when>
          <xsl:when test="$vMemberType = 'T'">Type</xsl:when>
          <xsl:otherwise>Unknown</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
 
      <!--  We need the number of name parts to get the last part (the name of the entity).
            Unfortunately there might be periods in parameter data types so we must count
            only those that come before any open paren.  -->
      <xsl:variable name="vNumPeriods">
        <xsl:variable name="temp">
          <xsl:choose>
            <xsl:when test="contains($vWholeName,'(')">
              <xsl:value-of select="substring-before($vWholeName,'(')" />
            </xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="$vWholeName" />
            </xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:call-template name="tCountOccurances">
          <xsl:with-param name="pText" select="$temp" />
          <xsl:with-param name="pLookFor" select="'.'" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  Split the full name between entity name (and any args) and the path (ns,class...)  -->
      <xsl:variable name="vSplitName">
        <xsl:call-template name="tSplitAtInstance">
          <xsl:with-param name="pText"      select="$vWholeName" />
          <xsl:with-param name="pCount"     select="$vNumPeriods" />
          <xsl:with-param name="pLookFor"   select="'.'" />
          <xsl:with-param name="pSeparator" select="'|'" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  Get the entity name (and any args).
            Add space to commas so browsers can wrap them if needed  -->
      <xsl:variable name="vEntityName">
        <xsl:call-template name="tReplaceSubString">
          <xsl:with-param name="pText"     select="substring-after($vSplitName,'|')" />
          <xsl:with-param name="pLookFor"  select="','" />
          <xsl:with-param name="pNewValue" select="', '" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  We'll assume the assembly name is also the prime Namespace name and omit it.
            The 'class' name might also include any sub-namespace names.  -->
      <xsl:variable name="vClass">
        <xsl:value-of select="substring-after(substring-before($vSplitName,'|'),'.')" />
      </xsl:variable>
 
      <!--  Variables are populated.  This formats member data on the page.  -->
      <xsl:if test="not($vClass='')">
        <span class="ClassName">
          <xsl:value-of select="$vClass" /> -
        </span>
      </xsl:if>
      <span class="MemberTypeName">
        <xsl:value-of select="$vMemberTypeName" />
      </span>:
      <div class="EntityName">
        <xsl:value-of select="$vEntityName" />
        <xsl:if test="$vMemberType = 'M' and not(contains($vWholeName,'('))">()</xsl:if>
      </div>
      <!-- And now, report all the sub-elements -->
      <xsl:apply-templates select="summary" />
      <xsl:apply-templates select="param" />
      <xsl:apply-templates select="returns" />
      <xsl:apply-templates select="interface" />
      <xsl:apply-templates select="exception" />
      <xsl:apply-templates select="example" />
      <xsl:apply-templates select="remarks" />
      <hr />
    </xsl:if>
  </xsl:template>
 
  <!-- These format the primary documentation elements -->
 
  <xsl:template match="summary">
    <div class="summary">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="param">
    <div class="param">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <span class="AttributeName"><xsl:value-of select="@name" /></span>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="returns">
    <div class="returns">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="interface">
    <div class="interface">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <span class="AttributeName"><xsl:value-of select="@name" /></span>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="exception">
    <div class="exception">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      (<xsl:value-of select="substring-after(@cref,'.')" />)
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="example">
    <div class="example">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <pre><xsl:value-of select="." /></pre>
    </div>
  </xsl:template>
  <xsl:template match="remarks">
    <div class="remarks">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
 
  <!-- Document elements can contain custom formatting elements.  -->
 
  <xsl:template match="para">
    <xsl:if test="not(.='')">
      <xsl:apply-templates select="node()" />
      <br />
    </xsl:if>
  </xsl:template>
 
  <xsl:template match="c">
    <span class="CodeItem">
      <xsl:value-of select="." />
    </span>
  </xsl:template>
 
  <xsl:template match="list">
    <table>
      <tr id="FirstRow">
        <td>Item</td>
        <td>Description</td>
      </tr>
      <xsl:apply-templates select="node()" />
    </table>
  </xsl:template>
  <xsl:template match="item">
    <tr><xsl:apply-templates select="node()" /></tr>
  </xsl:template>
  <xsl:template match="term">
    <td><xsl:value-of select="." /></td>
  </xsl:template>
  <xsl:template match="description">
    <td><xsl:value-of select="." /></td>
  </xsl:template>
 
</xsl:stylesheet>