2

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">[email protected]</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">[email protected]</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>[email protected]</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>[email protected]</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!

Improve this question
1
  • What you're asking for is not possible, because your @Name values are not valid XML element names (they contain spaces) - so the "desired output" you show is not a well-formed XML document. You might solve this by replacing the spaces with (e.g.) underscores - but there could be other characters there that will trip you up. Commented Apr 21, 2016 at 16:35

2 Answers 2

Reset to default
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>[email protected]</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>[email protected]</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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you! It indeed worked. One question though, if the root node has name space, would the same stylesheet apply?
It depends. If the RootNode element declares a default namespace (which would be inherited by DataColumn) then no - this would not work as is.
0

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>[email protected]</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>[email protected]</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>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.