Clash Royale CLAN TAG#URR8PPP

How can i “Normalize” very unorganized and “lossy” QuickBooks XML Data with XSLT?

My current assignment is to take QuickBooks accounting data in Excel format (a years worth, but I am currently working on perfecting only a month before I move to the rest), map it with a schema, export it into XML, and write a stylesheet that transforms that XML into "Normalized" data. I have attached a piece of the XML data I'm struggling with, my current stylesheet, and what I need it to output (i'm sort of close..). All blank tags can be ignored (they are just a result of the Excel import) and all headers containing 'Total' are not needed.

The QuickBooks Data that I've exported from Excel into XML is lengthy (for only 1 transaction and 1 month out of many transactions and a years worth of data):

<MonthlyPandL>
<Transactions>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>63400 · Interest Expense</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-04</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Dell Financial Services</Name>
<blank6/>
<Memo>003-8922605-001</Memo>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>160</Amount>
<blank11/>
<Balance>160</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>Total 63400 · Interest Expense</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>160</Amount>
<blank11/>
<Balance>160</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>64000 · Internet Expenses</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Bill</Type>
<blank3/>
<Date>2017-05-29</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Swift Systems, Inc.</Name>
<blank6/>
<Memo>Server Hosting Fee</Memo>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>20000 · Accounts Payable</Split>
<blank10/>
<Amount>250</Amount>
<blank11/>
<Balance>250</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>Total 64000 · Internet Expenses</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>250</Amount>
<blank11/>
<Balance>250</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>64500 · Client Expenses</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6>64501 · Internet Properties</Header6>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Deposit</Type>
<blank3/>
<Date>2017-05-01</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo>Deposit</Memo>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>-9.99</Amount>
<blank11/>
<Balance>-9.99</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-04</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>9.99</Amount>
<blank11/>
<Balance>0</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Deposit</Type>
<blank3/>
<Date>2017-05-08</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo>Deposit</Memo>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>-9.99</Amount>
<blank11/>
<Balance>-9.99</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-08</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>88.18</Amount>
<blank11/>
<Balance>78.19</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-08</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>1.99</Amount>
<blank11/>
<Balance>80.18</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-11</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>9.99</Amount>
<blank11/>
<Balance>90.17</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-12</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>49.98</Amount>
<blank11/>
<Balance>140.15</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Deposit</Type>
<blank3/>
<Date>2017-05-15</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo>Deposit</Memo>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>-9.99</Amount>
<blank11/>
<Balance>130.16</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-15</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>1.99</Amount>
<blank11/>
<Balance>132.15</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-15</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>34.95</Amount>
<blank11/>
<Balance>167.1</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-18</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>45.98</Amount>
<blank11/>
<Balance>213.08</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-22</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>39.89</Amount>
<blank11/>
<Balance>252.97</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-22</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Network Solutions</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>5.98</Amount>
<blank11/>
<Balance>258.95</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6>Total 64501 · Internet Properties</Header6>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>258.95</Amount>
<blank11/>
<Balance>258.95</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6>64503 · Domain &amp; User/SubAdmin Hosting</Header6>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Bill</Type>
<blank3/>
<Date>2017-05-29</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Swift Systems, Inc.</Name>
<blank6/>
<Memo>email service</Memo>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>20000 · Accounts Payable</Split>
<blank10/>
<Amount>62.4</Amount>
<blank11/>
<Balance>62.4</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6>Total 64503 · Domain &amp; User/SubAdmin Hosting</Header6>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>62.4</Amount>
<blank11/>
<Balance>62.4</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>Total 64500 · Client Expenses</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>321.35</Amount>
<blank11/>
<Balance>321.35</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>68400 · Travel Expense</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6>68404 · Tolls &amp; Parking</Header6>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount/>
<blank11/>
<Balance/>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6/>
<blank1/>
<blank2/>
<Type>Check</Type>
<blank3/>
<Date>2017-05-11</Date>
<blank4/>
<Num/>
<blank5/>
<Name>Baltimore County Revenue</Name>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split>MCM BofA Checking</Split>
<blank10/>
<Amount>2.35</Amount>
<blank11/>
<Balance>2.35</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5/>
<Header6>Total 68404 · Tolls &amp; Parking</Header6>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>2.35</Amount>
<blank11/>
<Balance>2.35</Balance>
</Transaction>
<Transaction>
<Header1/>
<Header2/>
<Header3/>
<Header4/>
<Header5>Total 68400 · Travel Expense</Header5>
<Header6/>
<blank1/>
<blank2/>
<Type/>
<blank3/>
<Date/>
<blank4/>
<Num/>
<blank5/>
<Name/>
<blank6/>
<Memo/>
<blank7/>
<Class/>
<blank8/>
<Clr/>
<blank9/>
<Split/>
<blank10/>
<Amount>2.35</Amount>
<blank11/>
<Balance>2.35</Balance>
</Transaction>
</Transactions>


My Stylesheet:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:variable name="newLine">
<xsl:text>
</xsl:text>
</xsl:variable>

<xsl:template match="@* | node()" name="identity">
<xsl:copy>
<xsl:apply-templates select="@* | node()" />
</xsl:copy>
</xsl:template>

<xsl:template match="MonthlyPandL/Transactions" name ="Transaction">
<xsl:value-of select="$newLine"/>
<xsl:value-of select="$newLine"/>
<xsl:for-each select="Transaction">
<!--<xsl:if test="Header5 and Balance">
<Transaction>-->

<xsl:if test="string-length(Header1) > 0">
<xsl:variable name="Header1" select="Header1"/>
<xsl:element name="Category">
<xsl:value-of select="$Header1"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Header5) > 0 and not(contains(Header5,'Total'))">
<xsl:variable name="Number" select="substring-before(Header5,' ·')"/>
<xsl:variable name="Title" select="substring-after(Header5,'· ')"/>
<xsl:element name="Number">
<xsl:value-of select="$Number"/>
</xsl:element>
<xsl:element name="Title">
<xsl:value-of select="$Title"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Header6) > 0 and not(contains(Header6,'Total'))">
<xsl:variable name="SubNumber" select="substring-before(Header6,' ·')"/>
<xsl:variable name="SubTitle" select="substring-after(Header6,'· ')"/>
<xsl:element name="SubNumber">
<xsl:value-of select="$SubNumber"/>
</xsl:element>
<xsl:element name="SubTitle">
<xsl:value-of select="$SubTitle"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Type) > 0 ">
<xsl:variable name="Type" select="Type"/>
<xsl:element name="Type">
<xsl:value-of select="$Type"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Date) > 0">
<xsl:variable name="Date" select="Date"/>
<xsl:element name="Date">
<xsl:value-of select="$Date"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Num) > 0">
<xsl:variable name="Num" select="Num"/>
<xsl:element name="Num">
<xsl:value-of select="$Num"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Name) > 0">
<xsl:variable name="Name" select="Name"/>
<xsl:element name="Name">
<xsl:value-of select="$Name"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Memo) > 0">
<xsl:variable name="Memo" select="Memo"/>
<xsl:element name="Memo">
<xsl:value-of select="$Memo"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Class) > 0">
<xsl:variable name="Class" select="Class"/>
<xsl:element name="Class">
<xsl:value-of select="$Class"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Clr) > 0">
<xsl:variable name="Clr" select="Clr"/>
<xsl:element name="Clr">
<xsl:value-of select="$Clr"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Split) > 0">
<xsl:variable name="Split" select="Split"/>
<xsl:variable name="SplitNum" select="substring-before(Split,' ·')"/>
<xsl:variable name="SplitTitle" select="substring-after(Split,'· ')"/>
<xsl:choose>
<xsl:when test="contains(Split,'·')">
<xsl:element name="SplitNum">
<xsl:value-of select="$SplitNum"/>
</xsl:element>
<xsl:element name="SplitTitle">
<xsl:value-of select="$SplitTitle"/>
</xsl:element>
</xsl:when>
<xsl:otherwise>
<xsl:element name="Split">
<xsl:value-of select="$Split"/>
</xsl:element>
</xsl:otherwise>
</xsl:choose>
</xsl:if>

<xsl:if test="string-length(Amount) > 0 and string-length(Header2)=0 and string-length(Header4)=0 and not(contains(Header5,'Total')) and not(contains(Header6,'Total'))">
<xsl:variable name="Amount" select="Amount"/>
<xsl:element name="Amount">
<xsl:value-of select="$Amount"/>
</xsl:element>
</xsl:if>

<xsl:if test="string-length(Balance) > 0 and string-length(Header2)=0 and string-length(Header4)=0 and not(contains(Header5,'Total')) and not(contains(Header6,'Total'))">
<xsl:variable name="Balance" select="Balance"/>
<xsl:variable name="newLine">
<xsl:text>
</xsl:text>
</xsl:variable>
<xsl:element name="Balance">
<xsl:value-of select="$Balance"/>
</xsl:element>
<xsl:value-of select="$newLine"/>
<xsl:value-of select="$newLine"/>
</xsl:if>
<!--</Transaction>
</xsl:if>-->
</xsl:for-each>


What I need (UPDATED w/ all data):

<MonthlyPandL>
<Transaction>
<Number>63400</Number>
<Title>Interest Expense</Title>
<Type>Check</Type>
<Date>2017-05-04</Date>
<Name>Dell Financial Services</Name>
<Memo>003-8922605-001</Memo>
<Split>MCM BofA Checking</Split>
<Amount>160</Amount>
<Balance>160</Balance>
</Transaction>
<Transaction>
<Number>64000</Number>
<Title>Internet Expense</Title>
<Type>Bill</Type>
<Date>2017-05-29</Date>
<Name>Swift Systems, Inc.</Name>
<Memo>Server Hosting Fee</Memo>
<SplitNum>20000</SplitNum>
<SplitTitle>Accounts Payable</SplitTitle>
<Amount>250</Amount>
<Balance>250</Balance>
</Transaction>
<Transaction>
<Number>64500</Number>
<Title>Client Expenses</Title>
<SubNumber>64501</SubNumber>
<SubTitle>Internet Properties</SubTitle>
<Type>Deposit</Type>
<Date>2017-05-01</Date>
<Name>Network Solutions</Name>
<Memo>Deposit</Memo>
<Split>MCM BofA Checking</Split>
<Amount>-9.99</Amount>
<Balance>-9.99</Balance>
</Transaction>
<Transaction>
<Number>64500</Number>
<Title>Client Expenses</Title>
<SubNumber>64501</SubNumber>
<SubTitle>Internet Properties</SubTitle>
<Type>Check</Type>
<Date>2017-05-04</Date>
<Name>Network Solutions</Name>
<Split>MCM BofA Checking</Split>
<Amount>9.99</Amount>
<Balance>0</Balance>
</Transaction>
<Transaction>
(Multiple 64501 Transactions)
</Transaction>
<Transaction>
<Number>64500</Number>
<Title>Client Expenses</Title>
<SubNumber>64503</SubNumber>
<SubTitle>Domain &amp; User/SubAdmin Hosting</SubTitle>
<Type>Bill</Type>
<Date>2017-05-29</Date>
<Name>Swift Systems, Inc.</Name>
<Memo>email service</Memo>
<SplitNum>20000</SplitNum>
<SplitTitle>Accounts Payable</SplitTitle>
<Amount>62.4</Amount>
<Balance>62.4</Balance>
</Transaction>
<Transaction>
<Number>68400</Number>
<Title>Travel Expense</Title>
<SubNumber>68404</SubNumber>
<SubTitle>Tolls &amp; Parking</SubTitle>
<Type>Check</Type>
<Date>2017-05-11</Date>
<Name>Baltimore County Revenue</Name>
<Split>MCM BofA Checking</Split>
<Amount>2.35</Amount>
<Balance>2.35</Balance>
</Transaction>
</MonthlyPandL>


My current output isn't wrapping every individual data set in a Transaction tag and it isn't appending a header (Number to SubTitle) on each data set either. I am really struggling with getting the preceding Number and SubNumber to appear with every set of Transaction data (ranging from the tags Type to Balance) and with my XPath and my selects, so any assistance is greatly appreciated. Thanks!

2 Answers
2

Consider the Muenchian Method where you group by node name and text value and retrieve return non-empty text values. However, this may only work with the abbreviated 1 transaction you show and nodes are not in order as you desired:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">
<xsl:output indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:key name="node_key" match="Transaction/*" use="concat(name(.), text())" />

<xsl:template match="/MonthlyPandL">
<Transaction>
<xsl:apply-templates select="Transactions/Transaction/*[generate-id() =
generate-id(key('node_key', concat(name(.), text()))[1]) and text()!='']"/>
</Transaction>
</xsl:template>

<xsl:template match="Transaction/*">
<xsl:choose>
<xsl:when test="name(.)='Header5' and position() &lt; 5">
<Number><xsl:value-of select="substring-before(.,' ·')"/></Number>
</xsl:when>
<xsl:when test="name(.)='Header5' and position() &gt; 5">
<Title><xsl:value-of select="substring-after(.,'· ')"/></Title>
</xsl:when>
<xsl:when test="name(.)='Header6' and position() &lt; 5">
<SubNumber><xsl:value-of select="substring-before(.,' ·')"/></SubNumber>
</xsl:when>
<xsl:when test="name(.)='Header6' and position() &gt; 5">
<SubTitle><xsl:value-of select="substring-after(.,'· ')"/></SubTitle>
</xsl:when>
<xsl:when test="name(.)='Type'">
<Type><xsl:value-of select="."/></Type>
</xsl:when>
<xsl:when test="name(.)='Date'">
<Date><xsl:value-of select="."/></Date>
</xsl:when>
<xsl:when test="name(.)='Name'">
<Name><xsl:value-of select="."/></Name>
</xsl:when>
<xsl:when test="name(.)='Split'">
<Split><xsl:value-of select="."/></Split>
</xsl:when>
<xsl:when test="name(.)='Amount'">
<Amount><xsl:value-of select="."/></Amount>
</xsl:when>
<xsl:when test="name(.)='Balance'">
<Balance><xsl:value-of select="."/></Balance>
</xsl:when>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>


Output

<?xml version="1.0" encoding="utf-8"?>
<Transaction>
<Number>68400</Number>
<SubNumber>68404</SubNumber>
<Type>Check</Type>
<Date>2017-05-11</Date>
<Name>Baltimore County Revenue</Name>
<Split>MCM BofA Checking</Split>
<Amount>2.35</Amount>
<Balance>2.35</Balance>
<SubTitle>Tolls &amp; Parking</SubTitle>
<Title>Travel Expense</Title>
</Transaction>


XSLT Demo

<xsl:output method="xml" indent="yes"/>

<xsl:template match="Transactions">
<Transaction>
<xsl:for-each select="Transaction[1]/Header5">
<xsl:choose>
<xsl:when test="position() = 1">
<Number>
<xsl:value-of select="substring-before(.,' ·')"/>
</Number>
<Title>
<xsl:value-of select="substring-after(.,' · ')"/>
</Title>
</xsl:when>

<xsl:otherwise/>
</xsl:choose>
</xsl:for-each>
<xsl:for-each select="Transaction[2]/Header6">
<xsl:choose>
<xsl:when test="position() = 1">
<SubNumber>
<xsl:value-of select="substring-before(.,' ·')"/>
</SubNumber>
<SubTitle>
<xsl:value-of select="substring-after(.,' · ')"/>
</SubTitle>
</xsl:when>
<xsl:otherwise/>
</xsl:choose>
</xsl:for-each>
<Type>
<xsl:value-of select="Transaction[3]/Type"/>
</Type>
<Date><xsl:value-of select="Transaction[3]/Date"/></Date>
<Name><xsl:value-of select="Transaction[3]/Name"/></Name>
<Split><xsl:value-of select="Transaction[3]/Split"/></Split>
<Amount><xsl:value-of select="Transaction[3]/Amount"/></Amount>
<Balance><xsl:value-of select="Transaction[3]/Amount"/></Balance>
</Transaction>
</xsl:template>
Check it.


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.