?
Solved

XSLT need a couple of fields added + quickie tutorial

Posted on 2014-08-01
5
Medium Priority
?
163 Views
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:
<FlightInfo>
    <FlightKey>
        <Alc>SCX</Alc>
        <Number>0117</Number>
        <STD>2014-06-30T11:00:00Z</STD>
        <OriginIcao>KMSP</OriginIcao>
    </FlightKey>
    <Compute Timestamp="2014-06-30 09:09:00">
        <Fuels Unit="LBS">
            <Pre>27151</Pre>
            <Release>10651</Release>
            <Tankerage>16500</Tankerage>
            <Burn>3830</Burn>
            <Arrival>22996</Arrival>
            <Costs>
                <Origin Currency="USD" Quantity="USG">3.11</Origin>
                <Destination Currency="USD" Quantity="USG">4.00</Destination>
                <Savings Currency="USD">2098.98</Savings>
            </Costs>
        </Fuels>
        <Dispatcher>NATHAN ROOD</Dispatcher>
    </Compute>
</FlightInfo>

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="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">
   <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
   
   <xsl:template match="/FlightInfo">
      <xsl:text>Alc,Number,STD,OriginIcao,Pre,Release,Tankerage,Burn,Fuel&#13;&#10;</xsl:text>
     
      <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> 

      <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:for-each> 
      
      <xsl:value-of select="/Compute/Dispatcher" /><xsl:text>","</xsl:text>
      
   </xsl:template>
</xsl:stylesheet> 

Open in new window

Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
  • 3
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
zc2 earned 2000 total points
ID: 40235417
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="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">
   <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
   
   <xsl:template match="/FlightInfo">
      <xsl:text>Alc,Number,STD,OriginIcao,Pre,Release,Tankerage,Burn,Fuel,.......&#13;&#10;</xsl:text>
     
     <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:apply-templates>
   </xsl:template>

	<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>

	<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>
	<xsl:template match="Costs">
		<xsl:apply-templates select="Origin"/>
		<xsl:apply-templates select="Destination"/>
		<xsl:apply-templates select="Savings"/>
 	</xsl:template>

	<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>
   
   <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>
   </xsl:template>
</xsl:stylesheet> 

Open in new window

0
 
LVL 66

Author Comment

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

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

Assisted Solution

by:zc2
zc2 earned 2000 total points
ID: 40241659
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.
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 40246124
Thanks much.  -Jim
0
 
LVL 19

Expert Comment

by:zc2
ID: 40246164
You're welcome.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

840 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