Replace XML element tag using attribute value using xslt

Question

How can I replace the XML element tag name with the attribute value using a stylesheet? The XML file structure is fairly simple and I want to replace the element tag name (DataColumn) using the attribute value from the element. I am working with SSIS. XML file is the data source. I understand that there might be more than one ways to parse the XML file but I am looking for XML transformation approach in the SSIS. The alternate solution I am able to come up is the PIVOT component. However, it becomes quite trivial if the XML file contains 30 or more elements. Here is the XML structure. I have abbreviated content so that it won't be too lengthy.

<?xml version="1.0" encoding="utf-8"?>
<RootNode>
  <ResponseHeader />
  <CustomReport Name="Sample Test" ID="000">
    <DataRow>
      <DataColumn Name="Order ID">12345654</DataColumn>
      <DataColumn Name="Company ID">656584</DataColumn>
      <DataColumn Name="First Name">John</DataColumn>
      <DataColumn Name="Last Name">Smith</DataColumn>
      <DataColumn Name="Email">john.smith@domain.com</DataColumn>
      <DataColumn Name="Address 1">13542 S Main Street</DataColumn>
      <DataColumn Name="Address 2"></DataColumn>
      <DataColumn Name="City">Dallas</DataColumn>
      <DataColumn Name="State">TX</DataColumn>
      <DataColumn Name="Zip">75236</DataColumn>
      <DataColumn Name="Country">United States</DataColumn>
      <DataColumn Name="Phone">000-000-0000</DataColumn>
      <DataColumn Name="Fax"></DataColumn>
    </DataRow>
    <DataRow>
      <DataColumn Name="Order ID">5432354</DataColumn>
      <DataColumn Name="Company ID">545454</DataColumn>
      <DataColumn Name="First Name">Jane</DataColumn>
      <DataColumn Name="Last Name">Smith</DataColumn>
      <DataColumn Name="Email">jane.smith@domain.com</DataColumn>
      <DataColumn Name="Address 1">213213 W Main Blvd.</DataColumn>
      <DataColumn Name="Address 2"></DataColumn>
      <DataColumn Name="City">Baltimore</DataColumn>
      <DataColumn Name="State">MD</DataColumn>
      <DataColumn Name="Zip">21207</DataColumn>
      <DataColumn Name="Country">United States</DataColumn>
      <DataColumn Name="Phone">000-000-0000</DataColumn>
      <DataColumn Name="Fax"></DataColumn>
    </DataRow>
  </CustomReport>
</RootNode>

Here is the desired output:

<?xml version="1.0" encoding="utf-8"?>
    <RootNode>
      <ResponseHeader />
      <CustomReport Name="Sample Test" ID="000">
        <DataRow>
          <Order ID>12345654</Order ID>
          <Company ID>656584</Company ID>
          <First Name>John</First Name>
          <Last Name>Smith</Last NAme>
          <Email>john.smith@domain.com</Email>
          <Address 1>13542 S Main Street</Address 1>
          <Address 2></Address2>
          <City>Dallas</City>
          <State>TX</State>
          <Zip>75236</Zip>
          <Country>United States</Country>
          <Phone>000-000-0000</Phone>
          <Fax></Fax>
        </DataRow>
        <DataRow>
          <Order ID>5432354</Order ID>
          <Company ID>545454</Company ID>
          <First Name>Jane</First Name>
          <Last Name>Smith</Last Name>
          <Email>jane.smith@domain.com</Email>
          <Address 1>213213 W Main Blvd.</Address 1>
          <Address 2></Address 2>
          <City>Baltimore</City>
          <State>MD</State>
          <Zip>21207</Zip>
          <Country>United States</Country>
          <Phone>000-000-0000</Phone>
          <Fax></Fax>
        </DataRow>
      </CustomReport>
    </RootNode>

If this subject has been posted and answered before, please point me to the right link. Thank you!

Answer 1

You can't do exactly that as some of the Names attributes have spaces and those are not permitted as element names. You can't have an element <Last Name>Smith</Last Name> However you can replace the space with another character like - This should do it:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  >

  <xsl:template match="RootNode|CustomReport|DataRow">
   <xsl:element name="{name()}">
    <xsl:apply-templates select="@*"/>
    <xsl:apply-templates/>
   </xsl:element>
  </xsl:template>

  <xsl:template match="DataColumn">
   <xsl:variable name='nn' select="translate(@Name,' ','-')"/>
   <xsl:element name="{$nn}">
    <xsl:apply-templates select="*"/>
    <xsl:apply-templates/>
   </xsl:element>
  </xsl:template>

<xsl:template match="@*">
   <xsl:attribute name="{name()}">
    <xsl:value-of select="."/>
   </xsl:attribute>
  </xsl:template>

  <xsl:template match="text()">
    <xsl:value-of select='.'/>
  </xsl:template>


</xsl:stylesheet>

Which gives:

<?xml version="1.0"?>
<RootNode>

  <CustomReport Name="Sample Test" ID="000">
    <DataRow>
      <Order-ID>12345654</Order-ID>
      <Company-ID>656584</Company-ID>
      <First-Name>John</First-Name>
      <Last-Name>Smith</Last-Name>
      <Email>john.smith@domain.com</Email>
      <Address-1>13542 S Main Street</Address-1>
      <Address-2/>
      <City>Dallas</City>
      <State>TX</State>
      <Zip>75236</Zip>
      <Country>United States</Country>
      <Phone>000-000-0000</Phone>
      <Fax/>
    </DataRow>
    <DataRow>
      <Order-ID>5432354</Order-ID>
      <Company-ID>545454</Company-ID>
      <First-Name>Jane</First-Name>
      <Last-Name>Smith</Last-Name>
      <Email>jane.smith@domain.com</Email>
      <Address-1>213213 W Main Blvd.</Address-1>
      <Address-2/>
      <City>Baltimore</City>
      <State>MD</State>
      <Zip>21207</Zip>
      <Country>United States</Country>
      <Phone>000-000-0000</Phone>
      <Fax/>
    </DataRow>
  </CustomReport>
</RootNode>

Answer 2

Subject to my comment above, you could try:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<!-- identity transform -->
<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="DataColumn">
    <xsl:element name="{translate(@Name, ' ', '_')}">
        <xsl:value-of select="." />
    </xsl:element>
</xsl:template>

</xsl:stylesheet>

Applied to the given input example, the result would be:

<?xml version="1.0" encoding="UTF-8"?>
<RootNode>
   <ResponseHeader/>
   <CustomReport Name="Sample Test" ID="000">
      <DataRow>
         <Order_ID>12345654</Order_ID>
         <Company_ID>656584</Company_ID>
         <First_Name>John</First_Name>
         <Last_Name>Smith</Last_Name>
         <Email>john.smith@domain.com</Email>
         <Address_1>13542 S Main Street</Address_1>
         <Address_2/>
         <City>Dallas</City>
         <State>TX</State>
         <Zip>75236</Zip>
         <Country>United States</Country>
         <Phone>000-000-0000</Phone>
         <Fax/>
      </DataRow>
      <DataRow>
         <Order_ID>5432354</Order_ID>
         <Company_ID>545454</Company_ID>
         <First_Name>Jane</First_Name>
         <Last_Name>Smith</Last_Name>
         <Email>jane.smith@domain.com</Email>
         <Address_1>213213 W Main Blvd.</Address_1>
         <Address_2/>
         <City>Baltimore</City>
         <State>MD</State>
         <Zip>21207</Zip>
         <Country>United States</Country>
         <Phone>000-000-0000</Phone>
         <Fax/>
      </DataRow>
   </CustomReport>
</RootNode>

However, this too would fail if a name started with a digit, for example.