• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

XSL/XML structure clashing

 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My input xml xconsists of 4 years of a company_report that contains 4 years of profit & loss extracts followed by the same 4 years of balance sheet extracts.
I wish to perform arithmetic to generate some additional financial figures for each year of extracts. My problem is that the arithmetic for any one year refers to elements from both extracts, which are seperated in the input structure - rotten xml structure, I know.
The following XSL will generate the first years figures and shows the arithmetic.
What do I do to get each years figures into my output. Can I merge my output into the company report after the extracts?
XSL
<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl='http://www.w3.org/XSL/Transform/1.0'>
<xsl:output method="xml"/>
<xsl:template match="/">
<aggregates>
<xsl:apply-templates select="company_report"/>
</aggregates>
</xsl:template>
<xsl:template match="company_report">
<account_end_date>
<xsl:value-of select="balance_sheet_extract/account_end_date"/>
</account_end_date>
<equity>
<xsl:variable name="net_worth">
<xsl:call-template name="calc_net_worth">
<xsl:with-param name="fixed_assets" select="balance_sheet_extract/fixed_assets"/>
<xsl:with-param name="current_assets" select="balance_sheet_extract/current_assets"/>
<xsl:with-param name="current_liab" select="balance_sheet_extract/current_liabilities"/>
<xsl:with-param name="long_term_liab" select="balance_sheet_extract/long_term_liabilities"/>
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="$net_worth div balance_sheet_extract/total_assets"/>
</equity>
<current_ratio>
<xsl:value-of select="balance_sheet_extract/current_assets div balance_sheet_extract/current_liabilities"/>
</current_ratio>
<quick_ratio>
<xsl:value-of select="(balance_sheet_extract/current_assets - balance_sheet_extract/stock) div balance_sheet_extract/current_liabilities"/>
</quick_ratio>
<borrowing_ratio>
<xsl:value-of select="(profit_and_loss_extract/interest_paid + balance_sheet_extract/long_term_borrowings + balance_sheet_extract/other_long_term_liabilities) div $net_worth"/>
</borrowing_ratio>
<gross_profit>
<xsl:value-of select="(profit_and_loss_extract/profit_before_tax div profit_and_loss_extract/turnover) * 100"/><xsl:text>%</xsl:text>
</gross_profit>
<interest_cover>
<xsl:value-of select="(profit_and_loss_extract/profit_before_tax + profit_and_loss_extract/interest_paid) div profit_and_loss_extract/interest_paid"/>
</interest_cover>
<credit_given>
<xsl:value-of select="balance_sheet_extract/debtors div profit_and_loss_extract/turnover div 365"/>
</credit_given>
<credit_taken>
<xsl:value-of select="balance_sheet_extract/creditors div (profit_and_loss_extract/turnover - profit_and_loss_extract/profit_before_tax) div 365"/>
</credit_taken>
</xsl:template>
<xsl:template name="calc_net_worth">
<xsl:param name="fixed_assets"/>
<xsl:param name="current_assets"/>
<xsl:param name="current_liab"/>
<xsl:param name="long_term_liab"/>
<xsl:value-of select="$fixed_assets + $current_assets - $current_liab - $long_term_liab"/>
</xsl:template>
</xsl:stylesheet>
XML
+ <profit_and_loss_extract>
<scale>M</scale>
<account_end_date>31/12/2000</account_end_date>
<number_months>12</number_months>
<turnover>140.64</turnover>
<profit_before_tax>-9.59</profit_before_tax>
<exports>10.46</exports>
<interest_paid>-0.03</interest_paid>
<profit_after_tax>-9.77</profit_after_tax>
<dividends>21.22</dividends>
<directors_fees>0.72</directors_fees>
<shareholder_funds>64.61</shareholder_funds>
<working_capital>21.42</working_capital>
<wages>46.72</wages>
<credit_limit>2.00</credit_limit>
<consolidated>Yes</consolidated>
</profit_and_loss_extract>
+ <profit_and_loss_extract>
<scale>M</scale>
<account_end_date>31/12/1999</account_end_date>
<number_months>12</number_months>
<turnover>127.82</turnover>
<profit_before_tax>-10.06</profit_before_tax>
<exports>9.86</exports>
<interest_paid>0.01</interest_paid>
<profit_after_tax>-9.89</profit_after_tax>
<dividends>N/K</dividends>
<directors_fees>0.24</directors_fees>
<shareholder_funds>69.85</shareholder_funds>
<working_capital>14.25</working_capital>
<wages>43.07</wages>
<credit_limit>2.50</credit_limit>
<consolidated>Yes</consolidated>
</profit_and_loss_extract>
+ <profit_and_loss_extract>
<scale>M</scale>
<account_end_date>31/12/1998</account_end_date>
<number_months>12</number_months>
<turnover>118.25</turnover>
<profit_before_tax>-14.62</profit_before_tax>
<exports>8.14</exports>
<interest_paid>0.50</interest_paid>
<profit_after_tax>-16.22</profit_after_tax>
<dividends>N/K</dividends>
<directors_fees>0.39</directors_fees>
<shareholder_funds>50.52</shareholder_funds>
<working_capital>-7.78</working_capital>
<wages>39.66</wages>
<credit_limit>2.50</credit_limit>
<consolidated>N/S</consolidated>
</profit_and_loss_extract>
+ <profit_and_loss_extract>
<scale>M</scale>
<account_end_date>31/12/1997</account_end_date>
<number_months>12</number_months>
<turnover>112.17</turnover>
<profit_before_tax>3.91</profit_before_tax>
<exports>7.61</exports>
<interest_paid>2.63</interest_paid>
<profit_after_tax>4.66</profit_after_tax>
<dividends>N/K</dividends>
<directors_fees>0.67</directors_fees>
<shareholder_funds>20.64</shareholder_funds>
<working_capital>-1.13</working_capital>
<wages>30.74</wages>
<credit_limit>2.00</credit_limit>
<consolidated>N/S</consolidated>
</profit_and_loss_extract>
+ <balance_sheet_extract>
<scale>M</scale>
<account_end_date>31/12/2000</account_end_date>
<number_months>12</number_months>
<fixed_assets>83.34</fixed_assets>
<current_assets>54.69</current_assets>
<total_assets>138.03</total_assets>
<current_liabilities>33.27</current_liabilities>
<shareholder_funds>64.61</shareholder_funds>
<long_term_liabilities>40.15</long_term_liabilities>
<long_term_borrowings>0.07</long_term_borrowings>
<other_long_term_liabilities>40.08</other_long_term_liabilities>
<stock>2.35</stock>
<debtors>21.57</debtors>
<creditors>4.52</creditors>
<cash>12.84</cash>
<bank_overdraft>N/K</bank_overdraft>
<employees>1,654</employees>
<intangible_assets>79.90</intangible_assets>
<investments>N/K</investments>
<reserves>61.58</reserves>
<short_term_loans>N/K</short_term_loans>
<paid_up_equity>3.04</paid_up_equity>
<tangible_fixed_assets>3.44</tangible_fixed_assets>
<working_capital>21.42</working_capital>
<inter_company_debtors>4.76</inter_company_debtors>
<inter_company_creditors>1.86</inter_company_creditors>
</balance_sheet_extract>
+ <balance_sheet_extract>
<scale>M</scale>
<account_end_date>31/12/1999</account_end_date>
<number_months>12</number_months>
<fixed_assets>115.89</fixed_assets>
<current_assets>57.28</current_assets>
<total_assets>173.17</total_assets>
<current_liabilities>43.03</current_liabilities>
<shareholder_funds>69.85</shareholder_funds>
<long_term_liabilities>60.28</long_term_liabilities>
<long_term_borrowings>0.18</long_term_borrowings>
<other_long_term_liabilities>60.09</other_long_term_liabilities>
<stock>2.30</stock>
<debtors>23.90</debtors>
<creditors>5.43</creditors>
<cash>18.95</cash>
<bank_overdraft>0.00</bank_overdraft>
<employees>1,600</employees>
<intangible_assets>109.83</intangible_assets>
<investments>N/K</investments>
<reserves>66.82</reserves>
<short_term_loans>0.33</short_term_loans>
<paid_up_equity>3.04</paid_up_equity>
<tangible_fixed_assets>6.06</tangible_fixed_assets>
<working_capital>14.25</working_capital>
<inter_company_debtors>0.99</inter_company_debtors>
<inter_company_creditors>9.06</inter_company_creditors>
</balance_sheet_extract>
+ <balance_sheet_extract>
<scale>M</scale>
<account_end_date>31/12/1998</account_end_date>
<number_months>12</number_months>
<fixed_assets>118.86</fixed_assets>
<current_assets>42.00</current_assets>
<total_assets>160.86</total_assets>
<current_liabilities>49.77</current_liabilities>
<shareholder_funds>50.52</shareholder_funds>
<long_term_liabilities>60.56</long_term_liabilities>
<long_term_borrowings>0.53</long_term_borrowings>
<other_long_term_liabilities>60.03</other_long_term_liabilities>
<stock>N/K</stock>
<debtors>23.78</debtors>
<creditors>10.28</creditors>
<cash>7.30</cash>
<bank_overdraft>0.00</bank_overdraft>
<employees>1,404</employees>
<intangible_assets>112.02</intangible_assets>
<investments>N/K</investments>
<reserves>47.49</reserves>
<short_term_loans>0.42</short_term_loans>
<paid_up_equity>3.04</paid_up_equity>
<tangible_fixed_assets>6.84</tangible_fixed_assets>
<working_capital>-7.78</working_capital>
<inter_company_debtors>1.36</inter_company_debtors>
<inter_company_creditors>17.19</inter_company_creditors>
</balance_sheet_extract>
+ <balance_sheet_extract>
<scale>M</scale>
<account_end_date>31/12/1997</account_end_date>
<number_months>12</number_months>
<fixed_assets>97.53</fixed_assets>
<current_assets>40.57</current_assets>
<total_assets>138.10</total_assets>
<current_liabilities>41.70</current_liabilities>
<shareholder_funds>20.64</shareholder_funds>
<long_term_liabilities>75.77</long_term_liabilities>
<long_term_borrowings>0.60</long_term_borrowings>
<other_long_term_liabilities>75.17</other_long_term_liabilities>
<stock>N/K</stock>
<debtors>24.16</debtors>
<creditors>5.51</creditors>
<cash>4.60</cash>
<bank_overdraft>0.00</bank_overdraft>
<employees>1,430</employees>
<intangible_assets>91.28</intangible_assets>
<investments>N/K</investments>
<reserves>17.60</reserves>
<short_term_loans>0.37</short_term_loans>
<paid_up_equity>3.04</paid_up_equity>
<tangible_fixed_assets>6.25</tangible_fixed_assets>
<working_capital>-1.13</working_capital>
<inter_company_debtors>1.67</inter_company_debtors>
<inter_company_creditors>15.00</inter_company_creditors>
</balance_sheet_extract>
 
Sheriff
Posts: 5782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am not quite sure if I understand your problem correctly. If the issue is with two XML files from different sources having the same element name(s), the best solution would be to use namespaces.
 
John Coleman
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is only one input xml file, the structure is:
<comapany_report>
<profit_and_loss_extract>
year 1 elements
</profit_and_loss_extract>
<profit_and_loss_extract>
year 2 elements
</profit_and_loss_extract>
<profit_and_loss_extract>
year 3 elements
</profit_and_loss_extract>
<profit_and_loss_extract>
year 4 elements
</profit_and_loss_extract>
<balance_sheet_extract>
year 1 elements
</balance_sheet_extract>
<balance_sheet_extract>
year 2 elements
</balance_sheet_extract>
<balance_sheet_extract>
year 3 elements
</balance_sheet_extract>
<balance_sheet_extract>
year 4 elements
</balance_sheet_extract>
</company_report>
I want to produce a report containing calculations that use values from each of the extracts by year, something like this:
<aggregates>
<year year="1">
year 1 figures
</year>
<year year="2">
year 2 figures
</year>
<year year="3">
year 3 figures
</year>
<year year="4">
year 4 figures
</year>
</aggregates>
This is a structure clash, my input and output data structures don't match.
Perhaps in XSL I can merge the years somehow, or scan them matching the extracts by year for each year?
 
Ajith Kallambella
Sheriff
Posts: 5782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, you will be able to use XSL to merge the releated elements together. The famous misconception about XSL is that it is used for generating presentation. XSL can transform one XML document into another XML document and your presentation happens to be an XML document!
So, you should be able to create new aggregate elements using a stylesheet and generate another adhoc XML document for final processing. This is just the "transformation" XSL. The "presentation" XSL will then consume the transformed XML not knowing how it was generated. In fact, chaining transformations is one of the recommended practices since it offers very flexible design options and it is extremely easy to maintain and change.
There is an < xsl:elemement > construct that can be used to create a new XML element in the output document.
Hope that helps.
 
Leverager of our synergies
Posts: 10065
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
... or just "wrap" XSLT instructions in XML elements and they will be copied in the output "as is". For example, this template should work on John's last data:
<xsl:template match="company_report">
<xsl:for-each select="profit_and_loss_extract">
<year year="{position()}">
<xsl:value-of select="."/>
</year>
</xsl:for-each>
</xsl:template>
 
Ajith Kallambella
Sheriff
Posts: 5782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Talk about practical advice - Map always beats me.
 
reply
    Bookmark Topic Watch Topic
  • New Topic