Link to home
Start Free TrialLog in
Avatar of MoreThanDoubled
MoreThanDoubled

asked on

use XSLT to flatten XML file to a text file

I have an xml file and i need to flatten the hierarchy by using an xslt template, so that i can take that .xslt file and create a text file that contains the flatten data from the xml file. I am using SSIS but i need to create the xslt template.  The examples i have seen thus far use html..

Can someone with some experience help me on this?
The xml file is below:  

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
- <cbdcustomxmlasns>
- <asns>
- <asn>
- <Shipment>
  <externaltpid>IPBB1</externaltpid> 
  <purposecode>00</purposecode> 
  <carriermode>TL</carriermode> 
  <transmode>M</transmode> 
  <carriername /> 
  <carrierscaccode>KNIG</carrierscaccode> 
  <equipmentinitial>KNIG</equipmentinitial> 
  <carriernumber>64755</carriernumber> 
  <routing>TPVSTL</routing> 
  <manifestnumber /> 
  <bolnumber>03686320023681549</bolnumber> 
  <sealnumber /> 
  <shipdate>09/29/2011</shipdate> 
  <shiptime /> 
  <deliverydate /> 
  <totalshipmentweight /> 
  <totalshipmentweightuom /> 
  <totalshipmentunits /> 
  <totalshipmentunitsuom /> 
  <paymentterms /> 
  <amtfreight /> 
  <shiptoqualifier>28</shiptoqualifier> 
  <shiptocode>GPC125</shiptocode> 
  <shiptoname>GP</shiptoname> 
  <shiptoaddr1 /> 
  <shiptoaddr2 /> 
  <shiptocity /> 
  <shiptostate /> 
  <shiptozip /> 
  <shiptocountry /> 
  <shipfromqualifier>28</shipfromqualifier> 
  <shipfromcode>IPC660</shipfromcode> 
  <shipfromname>International Paper</shipfromname> 
  <shipfromaddr1 /> 
  <shipfromaddr2 /> 
  <shipfromcity /> 
  <shipfromstate /> 
  <shipfromzip /> 
  <shipfromcountry /> 
- <Order>
  <ordernumber>3927036</ordernumber> 
  <orderdate /> 
  <totalorderweight /> 
  <totalorderweightuom /> 
  <totalorderunits /> 
  <totalorderunitsuom /> 
  <vendorponumber /> 
- <Detail>
  <basisweight>129.0</basisweight> 
  <basisweightuom>LB</basisweightuom> 
  <paperdescription /> 
  <apipapercode /> 
  <millproductcode>10SBSIP</millproductcode> 
  <productwidth /> 
  <caliper /> 
  <caliperuom /> 
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.04</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23498.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3037.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680202</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23149.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3035.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680306</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23149.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3043.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680309</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23149.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3047.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680310</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>59.91</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>22977.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3027.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680312</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>59.91</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>22977.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3031.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680315</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>59.91</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>22977.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3027.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680316</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23129.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3041.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680318</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23129.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3047.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680321</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23129.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3049.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680322</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23142.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3041.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680324</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>35.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>60.0</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23142.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3049.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680327</rollserialnumber> 
  </Item>
  </Detail>
- <Detail>
  <basisweight>129.0</basisweight> 
  <basisweightuom>LB</basisweightuom> 
  <paperdescription /> 
  <apipapercode /> 
  <millproductcode>10SBSIP</millproductcode> 
  <productwidth /> 
  <caliper /> 
  <caliperuom /> 
- <Item>
  <rollwidth>38.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>59.94</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>23193.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3289.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312680249</rollserialnumber> 
  </Item>
- <Item>
  <rollwidth>38.5</rollwidth> 
  <rollwidthuom>IN</rollwidthuom> 
  <rolldiameter>59.06</rolldiameter> 
  <rolldiameteruom>IN</rolldiameteruom> 
  <rolllength>22284.0</rolllength> 
  <rolllengthuom>FT</rolllengthuom> 
  <rollweight>3163.0</rollweight> 
  <rollweightuom>LB</rollweightuom> 
  <rollserialnumber>A312690013</rollserialnumber> 
  </Item>
  </Detail>
  </Order>
  </Shipment>
  </asn>
  </asns>
  </cbdcustomxmlasns>

Open in new window

Avatar of Farzad Akbarnejad
Farzad Akbarnejad
Flag of Iran, Islamic Republic of image
Hello,
Maybe this will help you:

http://www.stylusstudio.com/docs/v62/d_xslt2.html

-FA
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image
hmm, doesn't sound like a beginnersguide to XSLT was the question,

must admit that the requirements are a bit vague

Below stylesheet will seperate fields with ',' and will start a new line for each Item
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    <xsl:strip-space elements="*"/>
    <xsl:output method="text"/>
    <xsl:template match="*">
        <xsl:apply-templates/>
        <xsl:if test="normalize-space(text())">
            <xsl:text>,</xsl:text>
        </xsl:if>
    </xsl:template>
    <xsl:template match="Item">
        <xsl:text>&#10;</xsl:text>
        <xsl:apply-templates/>
    </xsl:template>
</xsl:stylesheet>

Open in new window

Avatar of MoreThanDoubled
MoreThanDoubled

ASKER

This is what i am talking about and this is why i continue my membership!!!!!!

Instead of starting a new line for each item how can i keep the items in one line possible separated by a comma and then when i get to the tag <item> it loops through the tag <item> and then starts a new line.
If that is not clear, then give me about 1 hour 1/2 to get to my desktop.
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image
that is not entirely clear
maybe post an example
Avatar of MoreThanDoubled
MoreThanDoubled

ASKER

From what you stated above about the output of the data it seems like the data would look like the following:
IPBB1,
00,
TL,
M,  
:
:

For the xml i provided above the output should look like this in a text file so that i can read that text file into a database (Gerton,copy it into notepad and turn the wrap off):

IPBB1      00 TL      M KNIG      KNIG      64755      TPVSTL 03686320023681549 09/29/2011 28      GPC125      GP 28      IPC660      International Paper 3927036 129.0 LB 10SBSIP 35.5 IN  60.04 IN      23498.0      FT 3037.0 LB      A312680202
IPBB1      00 TL      M KNIG      KNIG      64755      TPVSTL 03686320023681549 09/29/2011 28      GPC125      GP 28      IPC660      International Paper 3927036 129.0 LB 10SBSIP 35.5 IN  60.0  IN      23149.0      FT 3035.0 LB      A312680306
IPBB1      00 TL      M KNIG      KNIG      64755      TPVSTL 03686320023681549 09/29/2011 28      GPC125      GP 28      IPC660      International Paper 3927036 129.0 LB 10SBSIP 35.5 IN  60.0  IN      23149.0      FT 3043.0 LB      A312680309
IPBB1      00 TL      M KNIG      KNIG      64755      TPVSTL 03686320023681549 09/29/2011 28      GPC125      GP 28      IPC660      International Paper 3927036 129.0 LB 10SBSIP 35.5 IN  60.0  IN      23149.0      FT 3047.0 LB      A312680310
IPBB1      00 TL      M KNIG      KNIG      64755      TPVSTL 03686320023681549 09/29/2011 28      GPC125      GP 28      IPC660      International Paper 3927036 129.0 LB 10SBSIP 35.5 IN  59.91 IN      22977.0      FT 3027.0 LB      A312680312

Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image
No the data will more be like this

IPBB1,00,TL,M,KNIG...

do you want to loose the metadata?
Avatar of MoreThanDoubled
MoreThanDoubled

ASKER

What do you mean by "do i want to loose the metadata?
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image
there is more than just Item elements in the XML

<externaltpid>IPBB1</externaltpid>
  <purposecode>00</purposecode>
  <carriermode>TL</carriermode>
  <transmode>M</transmode>

I think I got it now, you want those repeated for each Item?
ASKER CERTIFIED SOLUTION
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MoreThanDoubled
MoreThanDoubled

ASKER

i am still reviewing.......