June 2012

You are currently browsing the monthly archive for June 2012.

In case you’ve read my article on using SSIS and XSLT to get XML imported into the database, you know that I cheated a little by first manually removing the namespaces from the XML document.

Well, that obviously doesn’t work smoothly when the process needs to get automated.

So here’s a method to use XSLT to remove the namespaces for you.

Removing The Namespaces

Using the XML Task as explained in my article you can apply the XSLT to remove the namespaces as an additional step prior to the XML Task that applies the XSLT to CSV conversion.  As output destination, you could set up a package variable that accepts the “XML without namespaces”, or you can write to file.  Up to you to decide.

Here’s the XSLT that will remove namespaces from the XML:

<!-- remove namespaces -->
<xsl:stylesheet xmlns:xsl ="http://www.w3.org/1999/XSL/Transform" version ="1.0" >
  <xsl:template match ="@*" >
    <xsl:attribute name ="{local-name()}" >
      <xsl:value-of select ="." />
    </xsl:attribute>
    <xsl:apply-templates/>
  </xsl:template>
  <xsl:template match ="*" >
    <xsl:element name ="{local-name()}" >
      <xsl:apply-templates select ="@* | node()" />
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>

(Ref. http://blogs.msdn.com/b/kaevans/archive/2003/06/13/8679.aspx)

Removing namespaces is one thing, but you’re losing some information.  What if you’d like to keep the namespaces as part of the node name?

Replacing The Namespaces

Well, that possible too!  Using the XSLT below, namespaces are kept but the colons separating the namespaces from the attribute names are replaced with underscores.  The translate() function is used to achieve this:

<!-- replace namespaces -->
<xsl:stylesheet xmlns:xsl ="http://www.w3.org/1999/XSL/Transform" version ="1.0" >
  <xsl:template match ="@*" >
    <xsl:attribute name ="{local-name()}" >
      <xsl:value-of select ="." />
    </xsl:attribute>
    <xsl:apply-templates/>
  </xsl:template>
  <xsl:template match ="*" >
    <!--keep namespace prefix as first part of node name (replaced colon with underscore) -->
    <xsl:element name ="{translate(name(), ':', '_')}" >
      <xsl:apply-templates select ="@* | node()" />
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>

Have fun!

Valentino.

Share

Tags: , ,

If you’ve ever encountered the words collation and conflict in the same sentence, or better, error message, then you’re going to love this one.  It’s a Microsoft Connect request that exists since 2008, created by SQL Server MVP Erland Sommarskog, and it suggests that the SQL Server installer should not select a server collation by default.

Ow yeah, sure got my vote!  I don’t think I ever came across a server which was initially installed with the correct collation unless it was installed by myself.  Not selecting a collation by default in the setup means that whoever is installing the server, usually a DBA, needs to think about (and more importantly: understand!) what collations are and how selecting the wrong one might impact the applications using the databases.  No more messed up servers with half of the databases using Latin1_General_CI_AS and the other half using SQL_Latin1_General_CP1_CI_AS, with possibly a couple of French_CI_AS databases thrown in for fun.

Sound familiar?  Then vote this up!

Share

Tags: , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved