XSLT need a couple of fields added + quickie tutorial

Posted on 2014-08-01
Last Modified: 2014-08-07
Hi All

I'm building an SSIS package that consumes an XML file, then uses an XLST file to 'flatten' it into a single row.

Question:  How do I handle the elements not in the XLSD below?  Fuels-Unit,  All the Fuels-Costs values, and Dispatcher.

Here's the XML:
    <Compute Timestamp="2014-06-30 09:09:00">
        <Fuels Unit="LBS">
                <Origin Currency="USD" Quantity="USG">3.11</Origin>
                <Destination Currency="USD" Quantity="USG">4.00</Destination>
                <Savings Currency="USD">2098.98</Savings>
        <Dispatcher>NATHAN ROOD</Dispatcher>

Open in new window

Here's the XLSD, with the columns I was able to piece together..
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="" xmlns:xs="" xmlns:fn="">
   <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
   <xsl:template match="/FlightInfo">
      <xsl:for-each select="FlightKey">
         <xsl:value-of select="Alc" /><xsl:text>","</xsl:text>
         <xsl:value-of select="Number" /><xsl:text>","</xsl:text>
         <xsl:value-of select="STD" /><xsl:text>","</xsl:text>
         <xsl:value-of select="OriginIcao" /><xsl:text>","</xsl:text>

      <xsl:for-each select="Compute/Fuels">
         <xsl:value-of select="Pre" /><xsl:text>","</xsl:text>
         <xsl:value-of select="Release" /><xsl:text>","</xsl:text>
         <xsl:value-of select="Tankerage" /><xsl:text>","</xsl:text>
         <xsl:value-of select="Burn" /><xsl:text>","</xsl:text>
         <xsl:value-of select="Arrival" /><xsl:text>","</xsl:text>
      <xsl:value-of select="/Compute/Dispatcher" /><xsl:text>","</xsl:text>

Open in new window

Thanks in advance.
Question by:Jim Horn
    LVL 18

    Accepted Solution

    I've redesigned your XSLT a little, while making it to read all the requested values.
    A general rule - it's almost always better to use templates instead of xsl:for-each

    <?xml version="1.0" encoding="utf-8"?>
    <xsl:stylesheet version="2.0" xmlns:xsl="" xmlns:xs="" xmlns:fn="">
       <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
       <xsl:template match="/FlightInfo">
         <xsl:apply-templates select="FlightKey"/>
         <xsl:apply-templates select="Compute/Fuels"/>
         <xsl:apply-templates select="Compute/Fuels/Costs"/>
         <xsl:apply-templates select="Compute/Dispatcher" mode="out">
    	 	<xsl:with-param name="last" select="true()"/>
    	<xsl:template match="FlightKey">
    		<xsl:apply-templates select="Alc" mode="out"/>
    		<xsl:apply-templates select="Number" mode="out"/>
    		<xsl:apply-templates select="STD" mode="out"/>
    		<xsl:apply-templates select="OriginIcao" mode="out"/>
    	<xsl:template match="Fuels">
    		<xsl:apply-templates select="@Unit" mode="out"/>
    		<xsl:apply-templates select="Pre" mode="out"/>
    		<xsl:apply-templates select="Release" mode="out"/>
    		<xsl:apply-templates select="Tankerage" mode="out"/>
    		<xsl:apply-templates select="Burn" mode="out"/>
    		<xsl:apply-templates select="Arrival" mode="out"/>
    	<xsl:template match="Costs">
    		<xsl:apply-templates select="Origin"/>
    		<xsl:apply-templates select="Destination"/>
    		<xsl:apply-templates select="Savings"/>
    	<xsl:template match="Origin|Destination|Savings">
    		<xsl:apply-templates select="." mode="out"/>
    		<xsl:apply-templates select="@Currency" mode="out"/>
    		<xsl:apply-templates select="@Quantity" mode="out"/>
       <xsl:template match="*|@*" mode="out">
        <xsl:param name="last"/>
       	<xsl:text>"</xsl:text><xsl:value-of select="."/><xsl:text>"</xsl:text>
       	<xsl:if test="not($last)">,</xsl:if>

    Open in new window

    LVL 65

    Author Comment

    by:Jim Horn
    Couple of follow-on questions...

    What is a 'template'?
    Is there any way to specify the data type of each column?
    LVL 18

    Assisted Solution

    You could think of a template as a "subroutine" - a chunk of your XSLT script which processes only given node(s) matched by the specified pattern.

    In my example the template: <xsl:template match="FlightKey"> starts a template which is able to processes only the "FlightKey" element, nothing else.
    It could be applied explicitly by <xsl:apply-templates select="FlightKey"/> or implicitly, when the XSLT traverses the tree and tries to find a best matching template for each node.
    In my example the <xsl:template match="/FlightInfo"> template is applied implicitly.
    Also a template could be called just by name, like a regular subroutine.

    You can pass parameters to a template. For an instance, you could pass the data type parameter to the  <xsl:template match="*|@*" mode="out"> template making it format the output data differently depending on the provided data type parameter.
    LVL 65

    Author Comment

    by:Jim Horn
    Thanks much.  -Jim
    LVL 18

    Expert Comment

    You're welcome.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now