Databases
StackOverflow2013
Postsdbo
POFlat to hierarchical XML transformation
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFlat to hierarchical XML transformation
    text
    copied!<p>Here is the source XML:</p> <pre><code>&lt;CellData&gt; &lt;Cell CellOrdinal="0"&gt; &lt;Value&gt;actual&lt;/Value&gt; &lt;FmtValue&gt;actual&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="1"&gt; &lt;Value&gt;630961942&lt;/Value&gt; &lt;FmtValue&gt;630961942&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="2"&gt; &lt;Value&gt;2.045711422E7&lt;/Value&gt; &lt;FmtValue&gt;20457114.2200&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="3"&gt; &lt;Value&gt;9.997105219639378E1&lt;/Value&gt; &lt;FmtValue&gt;99.9711&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="4"&gt; &lt;Value&gt;3.33E1&lt;/Value&gt; &lt;FmtValue&gt;33.0000&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="5"&gt; &lt;Value&gt;2.046303782E7&lt;/Value&gt; &lt;FmtValue&gt;20463037.8200&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="6"&gt; &lt;Value&gt;deposit&lt;/Value&gt; &lt;FmtValue&gt;deposit&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="7"&gt; &lt;Value&gt;144783359&lt;/Value&gt; &lt;FmtValue&gt;144783359&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="8"&gt; &lt;Value&gt;2.1388E2&lt;/Value&gt; &lt;FmtValue&gt;213.8800&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="9"&gt; &lt;Value&gt;1.0452016063370595E-3&lt;/Value&gt; &lt;FmtValue&gt;0.0010&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="10"&gt; &lt;Value&gt;6.67E1&lt;/Value&gt; &lt;FmtValue&gt;67.0000&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="11"&gt; &lt;Value&gt;2.046303782E7&lt;/Value&gt; &lt;FmtValue&gt;20463037.8200&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="12"&gt; &lt;Value&gt;deposit&lt;/Value&gt; &lt;FmtValue&gt;deposit&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="13"&gt; &lt;Value&gt;304011203&lt;/Value&gt; &lt;FmtValue&gt;304011203&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="14"&gt; &lt;Value&gt;5.70972E3&lt;/Value&gt; &lt;FmtValue&gt;5709.7200&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="15"&gt; &lt;Value&gt;2.7902601999882342E-2&lt;/Value&gt; &lt;FmtValue&gt;0.0279&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="16"&gt; &lt;Value&gt;6.67E1&lt;/Value&gt; &lt;FmtValue&gt;67.0000&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;Cell CellOrdinal="17"&gt; &lt;Value&gt;2.046303782E7&lt;/Value&gt; &lt;FmtValue&gt;20463037.8200&lt;/FmtValue&gt; &lt;/Cell&gt; &lt;/CellData&gt; </code></pre> <p>This list contains 6-column table data. Data is ordered by 1-st column and contain 'type', which will come in order: <code>actual</code>, <code>accumulation</code>, <code>deposit</code> but some can be absent at all (accumulation in example). i.e. it's actually contain this data:</p> <pre><code>contract_type contract_id sum percentage contract_type_percentage balance_total actual 630961942 20457114.2200 99.9711 33.0000 20463037.8200 deposit 144783359 213.8800 0.0010 67.0000 20463037.8200 deposit 304011203 5709.7200 0.0279 67.0000 20463037.8200 </code></pre> <p>Here is desired XML output (based on example):</p> <pre><code>&lt;body&gt; &lt;actual_accounts&gt; &lt;actual_account&gt; &lt;contract_id&gt;630961942&lt;/contract_id&gt; &lt;sum&gt;20457114.2200&lt;/sum&gt; &lt;percentage&gt;99.9711&lt;/percentage&gt; &lt;/actual_account&gt; &lt;actual_percentage&gt;33.0000&lt;/actual_percentage&gt; &lt;/actual_accounts&gt; &lt;accumulation_accounts&gt; &lt;accumulation_percentage&gt;0&lt;/accumulation_percentage&gt; &lt;/accumulation_accounts&gt; &lt;deposits&gt; &lt;deposit&gt; &lt;contract_id&gt;144783359&lt;/contract_id&gt; &lt;sum&gt;213.8800&lt;/sum&gt; &lt;percentage&gt;0.0010&lt;/percentage&gt; &lt;/deposit&gt; &lt;deposit&gt; &lt;contract_id&gt;304011203&lt;/contract_id&gt; &lt;sum&gt;5709.7200&lt;/sum&gt; &lt;percentage&gt;0.0279&lt;/percentage&gt; &lt;/deposit&gt; &lt;deposit_percentage&gt;67.0000&lt;/deposit_percentage&gt; &lt;/deposits&gt; &lt;balance_total&gt;20463037.8200&lt;/balance_total&gt; &lt;/body&gt; </code></pre> <p>Where <code>*_percentage</code> tags should contain value of 5th column from any row from associated <code>*</code> set.</p> <p>Here is that I got so far:</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:output indent="yes" omit-xml-declaration="yes" method="xml" version="1.0"/&gt; &lt;xsl:template match="/"&gt; &lt;body&gt; &lt;actual_accounts&gt; &lt;xsl:apply-templates select="//Cell"&gt; &lt;xsl:with-param name="cellType" select="'actual_accounts'"/&gt; &lt;/xsl:apply-templates&gt; &lt;actual_percentage&gt;0&lt;/actual_percentage&gt; &lt;/actual_accounts&gt; &lt;accumulation_accounts&gt; &lt;xsl:apply-templates select="//Cell"&gt; &lt;xsl:with-param name="cellType" select="'accumulation_accounts'"/&gt; &lt;/xsl:apply-templates&gt; &lt;accumulation_percentage&gt;0&lt;/accumulation_percentage&gt; &lt;/accumulation_accounts&gt; &lt;deposits&gt; &lt;xsl:apply-templates select="//Cell"&gt; &lt;xsl:with-param name="cellType" select="'deposits'"/&gt; &lt;/xsl:apply-templates&gt; &lt;deposit_percentage&gt;0&lt;/deposit_percentage&gt; &lt;/deposits&gt; &lt;xsl:choose&gt; &lt;xsl:when test="count(CellData/Cell) &amp;gt;= 6"&gt; &lt;balance_total&gt; &lt;xsl:value-of select="CellData/Cell[6]/FmtValue"/&gt; &lt;/balance_total&gt; &lt;/xsl:when&gt; &lt;xsl:otherwise&gt; &lt;balance_total&gt;0&lt;/balance_total&gt; &lt;/xsl:otherwise&gt; &lt;/xsl:choose&gt; &lt;/body&gt; &lt;/xsl:template&gt; &lt;xsl:template match="//Cell"&gt; &lt;xsl:param name="cellType"/&gt; &lt;xsl:if test="@CellOrdinal mod 6 = 0"&gt; &lt;xsl:variable name="contract_type" select="FmtValue"/&gt; &lt;xsl:variable name="contract_id" select="@CellOrdinal + 2"/&gt; &lt;xsl:variable name="sum" select="@CellOrdinal + 3"/&gt; &lt;xsl:variable name="percentage" select="@CellOrdinal + 4"/&gt; &lt;xsl:variable name="type_percentage" select="@CellOrdinal + 5"/&gt; &lt;xsl:variable name="balance_total" select="@CellOrdinal + 6"/&gt; &lt;xsl:if test="$contract_type = 'actual' and $cellType = ('actual_accounts')"&gt; &lt;actual_account&gt; &lt;contract_id&gt; &lt;xsl:value-of select="parent::CellData/Cell[$contract_id]/FmtValue"/&gt; &lt;/contract_id&gt; &lt;sum&gt; &lt;xsl:value-of select="parent::CellData/Cell[$sum]/FmtValue"/&gt; &lt;/sum&gt; &lt;percentage&gt; &lt;xsl:value-of select="parent::CellData/Cell[$percentage]/FmtValue"/&gt; &lt;/percentage&gt; &lt;/actual_account&gt; &lt;/xsl:if&gt; &lt;xsl:if test="$contract_type = 'accumulation' and $cellType = ('accumulation_accounts')"&gt; &lt;accumulation_account&gt; &lt;contract_id&gt; &lt;xsl:value-of select="parent::CellData/Cell[$contract_id]/FmtValue"/&gt; &lt;/contract_id&gt; &lt;sum&gt; &lt;xsl:value-of select="parent::CellData/Cell[$sum]/FmtValue"/&gt; &lt;/sum&gt; &lt;percentage&gt; &lt;xsl:value-of select="parent::CellData/Cell[$percentage]/FmtValue"/&gt; &lt;/percentage&gt; &lt;/accumulation_account&gt; &lt;/xsl:if&gt; &lt;xsl:if test="$contract_type = 'deposit' and $cellType = 'deposits'"&gt; &lt;deposit&gt; &lt;contract_id&gt; &lt;xsl:value-of select="parent::CellData/Cell[$contract_id]/FmtValue"/&gt; &lt;/contract_id&gt; &lt;sum&gt; &lt;xsl:value-of select="parent::CellData/Cell[$sum]/FmtValue"/&gt; &lt;/sum&gt; &lt;percentage&gt; &lt;xsl:value-of select="parent::CellData/Cell[$percentage]/FmtValue"/&gt; &lt;/percentage&gt; &lt;/deposit&gt; &lt;/xsl:if&gt; &lt;/xsl:if&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p>which do everything except whose <code>*_percentage</code> tags.. I'm limited to XSLT 1.0.</p> <p><strong>UPDATE WITH FINAL ANSWER:</strong> with small fixes to Maesto13 solution, and only works on MSXML4.0+, .NET1.0+</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxml="urn:schemas-microsoft-com:xslt" extension-element-prefixes="msxml"&gt; &lt;xsl:output indent="yes" omit-xml-declaration="yes" method="xml" version="1.0"/&gt; &lt;xsl:key name="CellGroup" use="@CellOrdinal - (@CellOrdinal mod 6)" match="CellData/Cell"/&gt; &lt;xsl:template match="CellData"&gt; &lt;xsl:variable name="Cells"&gt; &lt;xsl:apply-templates select="Cell[generate-id() = generate-id(key('CellGroup', @CellOrdinal - (@CellOrdinal mod 6))[1])]" mode="group"/&gt; &lt;/xsl:variable&gt; &lt;body&gt; &lt;actual_accounts&gt; &lt;xsl:for-each select="msxml:node-set($Cells)/Cell[contract-type='actual']"&gt; &lt;actual_account&gt; &lt;xsl:copy-of select="contract-type"/&gt; &lt;xsl:copy-of select="sum"/&gt; &lt;xsl:copy-of select="percentage"/&gt; &lt;/actual_account&gt; &lt;/xsl:for-each&gt; &lt;xsl:variable name="type_percentage" select="msxml:node-set($Cells)/Cell[contract-type='actual'][1]/contract-type-percentage[1]"&gt;&lt;/xsl:variable&gt; &lt;xsl:choose&gt; &lt;xsl:when test="boolean($type_percentage)"&gt; &lt;actual_percentage&gt;&lt;xsl:value-of select="$type_percentage"/&gt;&lt;/actual_percentage&gt; &lt;/xsl:when&gt; &lt;xsl:otherwise&gt; &lt;actual_percentage&gt;0&lt;/actual_percentage&gt; &lt;/xsl:otherwise&gt; &lt;/xsl:choose&gt; &lt;/actual_accounts&gt; &lt;accumulation_accounts&gt; &lt;xsl:for-each select="msxml:node-set($Cells)/Cell[contract-type='accumulation']"&gt; &lt;accumulation_account&gt; &lt;xsl:copy-of select="contract-type"/&gt; &lt;xsl:copy-of select="sum"/&gt; &lt;xsl:copy-of select="percentage"/&gt; &lt;/accumulation_account&gt; &lt;/xsl:for-each&gt; &lt;xsl:variable name="type_percentage" select="msxml:node-set($Cells)/Cell[contract-type='accumulation'][1]/contract-type-percentage[1]"&gt;&lt;/xsl:variable&gt; &lt;xsl:choose&gt; &lt;xsl:when test="boolean($type_percentage)"&gt; &lt;accumulation_percentage&gt;&lt;xsl:value-of select="$type_percentage"/&gt;&lt;/accumulation_percentage&gt; &lt;/xsl:when&gt; &lt;xsl:otherwise&gt; &lt;accumulation_percentage&gt;0&lt;/accumulation_percentage&gt; &lt;/xsl:otherwise&gt; &lt;/xsl:choose&gt; &lt;/accumulation_accounts&gt; &lt;deposits&gt; &lt;xsl:for-each select="msxml:node-set($Cells)/Cell[contract-type='deposit']"&gt; &lt;deposit&gt; &lt;xsl:copy-of select="contract-type"/&gt; &lt;xsl:copy-of select="sum"/&gt; &lt;xsl:copy-of select="percentage"/&gt; &lt;/deposit&gt; &lt;/xsl:for-each&gt; &lt;xsl:variable name="type_percentage" select="msxml:node-set($Cells)/Cell[contract-type='deposit'][1]/contract-type-percentage[1]"&gt;&lt;/xsl:variable&gt; &lt;xsl:choose&gt; &lt;xsl:when test="boolean($type_percentage)"&gt; &lt;deposit_percentage&gt;&lt;xsl:value-of select="$type_percentage"/&gt;&lt;/deposit_percentage&gt; &lt;/xsl:when&gt; &lt;xsl:otherwise&gt; &lt;deposit_percentage&gt;0&lt;/deposit_percentage&gt; &lt;/xsl:otherwise&gt; &lt;/xsl:choose&gt; &lt;/deposits&gt; &lt;xsl:variable name="total" select="msxml:node-set($Cells)/Cell[1]/balance-total"&gt;&lt;/xsl:variable&gt; &lt;xsl:choose&gt; &lt;xsl:when test="boolean($total)"&gt; &lt;balance_total&gt; &lt;xsl:value-of select="$total"/&gt; &lt;/balance_total&gt; &lt;/xsl:when&gt; &lt;xsl:otherwise&gt;&lt;balance_total&gt;0&lt;/balance_total&gt;&lt;/xsl:otherwise&gt; &lt;/xsl:choose&gt; &lt;/body&gt; &lt;/xsl:template&gt; &lt;xsl:template match="Cell" mode="group"&gt; &lt;Cell&gt; &lt;xsl:variable name="Cells" select="key('CellGroup', @CellOrdinal - (@CellOrdinal mod 6))"/&gt; &lt;contract-type&gt;&lt;xsl:value-of select="$Cells[1]/FmtValue"/&gt;&lt;/contract-type&gt; &lt;contract-id&gt;&lt;xsl:value-of select="$Cells[2]/FmtValue"/&gt;&lt;/contract-id&gt; &lt;sum&gt;&lt;xsl:value-of select="$Cells[3]/FmtValue"/&gt;&lt;/sum&gt; &lt;percentage&gt;&lt;xsl:value-of select="$Cells[4]/FmtValue"/&gt;&lt;/percentage&gt; &lt;contract-type-percentage&gt;&lt;xsl:value-of select="$Cells[5]/FmtValue"/&gt;&lt;/contract-type-percentage&gt; &lt;balance-total&gt;&lt;xsl:value-of select="$Cells[6]/FmtValue"/&gt;&lt;/balance-total&gt; &lt;/Cell&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload