Getting Unique XML Element Values with XSL 1.0

Today I needed to munge some dirty XML data. I still haven't taught myself XSL/XPath 2.0 yet, so I was limited to XSL 1.0 for now. The data I had looked like this, only much, much worse.

<Subject>Value 1|Value 2</Subject>
<Subject>Value 1|Value 2</Subject>
<Subject>Value 1|Value 2</Subject>
<Time>Time Value 1</Time>
<Time>Time Value 1</Time>
<Time>Time Value 1</Time>
<Subject>Value 3|Value 4</Subject>
<Subject>Value 3|Value 4</Subject>
<Subject>Value 3|Value 4</Subject>
<Time>Time Value 2</Time>
<Time>Time Value 2</Time>
<Time>Time Value 2</Time>

I wanted two things out of that series of elements: unique strings and the value before the |. As I type this, I realize there may be a bit of a bug here, but I'll have to test it out. Here's what I did for the series of subject elements. Can you spot the bug? ;-)

<xsl:variable name="subjects" select="/fragment/index-only-subjects//Subject[not(text()=preceding-sibling::Subject/text())]/text()"/>
  <xsl:for-each select="$subjects">
    <xsl:sort select="." data-type="text"/>
      <subject>
        <xsl:choose>
          <xsl:when test="contains(.,'|')">
            <xsl:value-of select="substring-before(.,'|')"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:value-of select="."/>
          </xsl:otherwise>
        </xsl:choose>
      </subject>
  </xsl:for-each>

I'm pretty sure there's a way to do this with xsl:keys / key(), but I got this solution working first.

Centering a div

This approach will center a <div> in a scenario where you want to have the body of your page centered on the screen, but leave the text left aligned.

body
{
 text-align: center;
 align: center;
}
 
#container
{
 text-align: left;
 margin: 0 auto;
}

align:center / margin: 0 auto are for IE/FF.

See Unencrypted DTSRun Commands

If you come across encrypted DTSRun commands, here's a way to decrypt them.

Drop down to the command prompt and paste in the full DTSRun string:

C:\>DTSRun /~ZThisWouldBeYourBigUglyDTSRunCommandString /!X /!C

Everything after C:\> is directly from the Job's Command text box. You have to add /!X and /!C to the end. Hit Enter to run it and the results then end up in your "paste" buffer, so you can just Ctrl+V into Notepad or wherever.

This seems to fail if the DTS is connecting to a server not setup on your machine. You may see something like the error below. Just add the remote machine to Enterprise Manager.

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Thanks to the guys over at RDA Blogs for posting about this.

T-SQL SPROC to Re-Create a Full Text Catalog

I cannot claim any ownership over this code. It's a colleague's, but it's so handy that I have to document it somewhere.

This will create an SPROC to re-create a full text catalog, which of course can then be tied to a Job.

CREATE PROCEDURE p_RecreateFT_Articles
 
AS 
 
ALTER TABLE [dbo].[Articles] WITH NOCHECK ADD 
 CONSTRAINT [PK_Articles] PRIMARY KEY  CLUSTERED 
 (
  [Article_ID]
 )  ON [PRIMARY]
 
IF EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE NAME = 'FT_Articles')
EXEC sp_fulltext_catalog 'FT_Articles', 'drop'

-- Create the catalog if it doesn't already exist.
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE NAME = 'FT_Articles')
EXEC sp_fulltext_catalog 'FT_Articles', 'create'

-- Add the full text index to the table 
EXEC sp_fulltext_table '[dbo].[Articles]', 'create', 'FT_Articles', 'PK_Articles'

-- Add the columns to the full text index 
EXEC sp_fulltext_column '[dbo].[Articles]', 'ArticleTitle', 'add' 
EXEC sp_fulltext_column '[dbo].[Articles]', 'ArticleBody', 'add'

-- Activate the index
EXEC sp_fulltext_table '[dbo].[Articles]', 'activate' 

-- Start population
EXEC sp_fulltext_catalog   'FT_Articles', 'start_full'

GO

T-SQL INSERT From One Table Into Another

This is just a simple example of doing an INSERT INTO one table from another, with a hard-coded value thrown into the mix.

INSERT INTO ProductSubjects  (ID, ProductID, SubjectID)
SELECT NEWID(), '52EB01B6-B768-4F9B-8F60-1A695A13D945', Subjects.ID 
FROM Subjects