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.