Search code examples
for-loopxsltcounting

XSLT scheme for create a table whose header and rows are separate and hierarchical

I have a problem in order to convert a file that looks like this into a "flat" table These files are uploaded during the exchange. There are many of them, but they have one template. First a description of the headers, then only the values.

<?xml version="1.0" encoding="UTF-8"?>
<extdata user="test">
   <scheme name="CRMOrder" request="get" success="true">
      <data>
         <s>
            <d name="CRMOrder">
               <f name="ActionDate" type="Date" />
               <f name="CRMClientId" type="String" />
               <f name="CreateId" type="Date" />
               <f name="StatusId" type="String" />
               <f name="Summa" type="Decimal" />
               <f name="WareHouseId" type="String" />
               <d name="CRMOrderLine">
                  <f name="Price" type="Decimal" />
                  <f name="LineNumber" type="Integer" />
                  <f name="Quantity" type="Decimal" />
                  <f name="Discount" type="Integer" />
               </d>
               <d name="CRMOrderOption">
                  <f name="OptionTypeId" type="String" />
                  <f name="Value" type="String" />
                  <f name="OptionTypeName" type="String" />
               </d>
            </d>
         </s>
         <o>
            <d name="CRMOrder">
               <r>
                  <f>2022-01-11T00:00:00</f>
                  <f>69244</f>
                  <f>2142256774</f>
                  <f>Accepted</f>
                  <f>2318.0600</f>
                  <f>62</f>
                  <d name="CRMOrderLine">
                     <r>
                        <f>64.7800</f>
                        <f>1</f>
                        <f>18.0000</f>
                        <f>62</f>
                     </r>
                     <d name="CRMOrderOption">
                        <r>
                           <f>2022-01-10T00:00:00</f>
                           <f>Comment</f>
                           <f>1</f>
                        </r>
                     </d>
                  </d>
               </r>
            </d>
         </o>
      </data>
   </scheme>
</extdata>

XSLT

   <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
       <xsl:output method="xml" indent="yes" />
       <xsl:template match="/extdata/scheme/data">
          <ValueTable xmlns="http://v8.1c.ru/8.1/data/core" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<-- get the total number of columns -->
             <xsl:variable name="recordColumn" select="count(s/d/f/@name) + count(s/d/d/f/@name)" />
<-- I didn’t understand how to create headers in one cycle, so there are two cycles with the same content -->
             <xsl:for-each select="s/d/f">
                <column>
                   <Name xsi:type="xs:string">
                      <xsl:value-of select="@name" />
                   </Name>
                   <ValueType>
                      <xsl:if test="@type= 'String'">
                         <Type>xs:string</Type>
                         <StringQualifiers>
                            <Length>150</Length>
                            <AllowedLength>Variable</AllowedLength>
                         </StringQualifiers>
                      </xsl:if>
                      <xsl:if test="@type= 'Date'">
                         <Type>xs:dateTime</Type>
                         <DateQualifiers>
                            <DateFractions>DateTime</DateFractions>
                         </DateQualifiers>
                      </xsl:if>
                      <xsl:if test="@type= 'Decimal' or @type= 'Currency'">
                         <Type>xs:decimal</Type>
                         <NumberQualifiers>
                            <Digits>20</Digits>
                            <FractionDigits>4</FractionDigits>
                            <AllowedSign>Any</AllowedSign>
                         </NumberQualifiers>
                      </xsl:if>
                      <xsl:if test="@type= 'Integer'">
                         <Type>xs:decimal</Type>
                         <NumberQualifiers>
                            <Digits>20</Digits>
                            <FractionDigits>0</FractionDigits>
                            <AllowedSign>Any</AllowedSign>
                         </NumberQualifiers>
                      </xsl:if>
                   </ValueType>
                </column>
             </xsl:for-each>
             <xsl:for-each select="s/d/d/f">
                <column>
                   <Name xsi:type="xs:string">
                      <xsl:value-of select="@name" />
                   </Name>
                   <ValueType>
                      <xsl:if test="@type= 'String'">
                         <Type>xs:string</Type>
                         <StringQualifiers>
                            <Length>150</Length>
                            <AllowedLength>Variable</AllowedLength>
                         </StringQualifiers>
                      </xsl:if>
                      <xsl:if test="@type= 'Date'">
                         <Type>xs:dateTime</Type>
                         <DateQualifiers>
                            <DateFractions>DateTime</DateFractions>
                         </DateQualifiers>
                      </xsl:if>
                      <xsl:if test="@type= 'Decimal' or @type= 'Currency'">
                         <Type>xs:decimal</Type>
                         <NumberQualifiers>
                            <Digits>20</Digits>
                            <FractionDigits>4</FractionDigits>
                            <AllowedSign>Any</AllowedSign>
                         </NumberQualifiers>
                      </xsl:if>
                      <xsl:if test="@type= 'Integer'">
                         <Type>xs:decimal</Type>
                         <NumberQualifiers>
                            <Digits>20</Digits>
                            <FractionDigits>0</FractionDigits>
                            <AllowedSign>Any</AllowedSign>
                         </NumberQualifiers>
                      </xsl:if>
                   </ValueType>
                </column>
             </xsl:for-each>
             <xsl:for-each select="o/d/r/f">
<-- Problem is here -->
                <xsl:variable name="counter" select="position()" />
                <xsl:if test="$counter = 1">
                   <xsl:text disable-output-escaping="yes">&lt;row&gt;</xsl:text>
                </xsl:if>
                <xsl:if test="$counter mod $recordColumn = 0">
                   <xsl:text disable-output-escaping="yes">&lt;/row&gt;</xsl:text>
                   <xsl:if test="$counter != last() ">
                      <xsl:text disable-output-escaping="yes">&lt;row&gt;</xsl:text>
                   </xsl:if>
                </xsl:if>
                <xsl:if test="$counter mod $recordColumn != 0">
                   <Value>
                      <xsl:value-of select="." />
                   </Value>
                </xsl:if>
             </xsl:for-each>
          </ValueTable>
       </xsl:template>
    </xsl:stylesheet>

How do i get the table from this file. The beginning and end of the row about the string and value probably did not look very beautiful, but it works. There are now 12 columns, as a result, my table row contains only 6 first values.

Expected result

  <ValueTable xmlns="http://v8.1c.ru/8.1/data/core"
            xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <column>
      <Name xsi:type="xs:string">ActionDate</Name>
      <ValueType>
         <Type>xs:dateTime</Type>
         <DateQualifiers>
            <DateFractions>DateTime</DateFractions>
         </DateQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">CRMClientId</Name>
      <ValueType>
         <Type>xs:string</Type>
         <StringQualifiers>
            <Length>150</Length>
            <AllowedLength>Variable</AllowedLength>
         </StringQualifiers>
      </ValueType>
   </column>
    <column>
      <Name xsi:type="xs:string">CreateId</Name>
      <ValueType>
         <Type>xs:dateTime</Type>
         <DateQualifiers>
            <DateFractions>DateTime</DateFractions>
         </DateQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">StatusId</Name>
      <ValueType>
         <Type>xs:string</Type>
         <StringQualifiers>
            <Length>150</Length>
            <AllowedLength>Variable</AllowedLength>
         </StringQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">Summa</Name>
      <ValueType>
         <Type>xs:decimal</Type>
         <NumberQualifiers>
            <Digits>20</Digits>
            <FractionDigits>4</FractionDigits>
            <AllowedSign>Any</AllowedSign>
         </NumberQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">WareHouseId</Name>
      <ValueType>
         <Type>xs:string</Type>
         <StringQualifiers>
            <Length>150</Length>
            <AllowedLength>Variable</AllowedLength>
         </StringQualifiers>
      </ValueType>
   </column>
    <column>
      <Name xsi:type="xs:string">Price</Name>
      <ValueType>
         <Type>xs:decimal</Type>
         <NumberQualifiers>
            <Digits>20</Digits>
            <FractionDigits>4</FractionDigits>
            <AllowedSign>Any</AllowedSign>
         </NumberQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">LineNumber</Name>
      <ValueType>
         <Type>xs:decimal</Type>
         <NumberQualifiers>
            <Digits>20</Digits>
            <FractionDigits>0</FractionDigits>
            <AllowedSign>Any</AllowedSign>
         </NumberQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">Quantity</Name>
      <ValueType>
         <Type>xs:decimal</Type>
         <NumberQualifiers>
            <Digits>20</Digits>
            <FractionDigits>4</FractionDigits>
            <AllowedSign>Any</AllowedSign>
         </NumberQualifiers>
      </ValueType>
   </column>
    <column>
      <Name xsi:type="xs:string">Discount</Name>
      <ValueType>
         <Type>xs:decimal</Type>
         <NumberQualifiers>
            <Digits>20</Digits>
            <FractionDigits>0</FractionDigits>
            <AllowedSign>Any</AllowedSign>
         </NumberQualifiers>
      </ValueType>
   </column>
       <Name xsi:type="xs:string">OptionTypeId</Name>
      <ValueType>
         <Type>xs:string</Type>
         <StringQualifiers>
            <Length>150</Length>
            <AllowedLength>Variable</AllowedLength>
         </StringQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">Value</Name>
      <ValueType>
         <Type>xs:string</Type>
         <StringQualifiers>
            <Length>150</Length>
            <AllowedLength>Variable</AllowedLength>
         </StringQualifiers>
      </ValueType>
   </column>
   <column>
      <Name xsi:type="xs:string">OptionTypeName</Name>
      <ValueType>
         <Type>xs:string</Type>
         <StringQualifiers>
            <Length>150</Length>
            <AllowedLength>Variable</AllowedLength>
         </StringQualifiers>
      </ValueType>
   </column>
<row>
 <Value>2022-01-11T00:00:00</Value>
 <Value>69244</Value> 
 <Value>2142256774</Value>
 <Value>Accepted</Value>
 <Value>2318.0600</Value>
 <Value>62</Value>
 <Value>64.7800</Value>
 <Value>1</Value>
 <Value>18.0000</Value>
 <Value>62</Value>
 <Value>2022-01-10T00:00:00</Value>
 <Value>Comment</Value>
 <Value>1</Value>
 </row>
  </ValueTable>
Solution

  • The given example is somewhat ambiguous. Assuming that s is the header row, and that o is a data row containing exactly one value for each column in the header row, and in the same order (IOW, that the input is already a "flat" table), the result shown could be produced using:

    XSLT 1.0

    <xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="http://v8.1c.ru/8.1/data/core"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/extdata">
    <ValueTable>
        <!-- header -->
        <xsl:for-each select="scheme/data/s//f">
            <column>
                <Name xsi:type="xs:string">
                    <xsl:value-of select="@name"/>
                </Name>
                <ValueType>
                    <xsl:choose>
                        <xsl:when test="@type= 'String'">
                            <Type>xs:string</Type>
                            <StringQualifiers>
                                <Length>150</Length>
                                <AllowedLength>Variable</AllowedLength>
                            </StringQualifiers>
                        </xsl:when>
                        <xsl:when test="@type='Date'">
                            <Type>xs:dateTime</Type>
                            <DateQualifiers>
                                <DateFractions>DateTime</DateFractions>
                            </DateQualifiers>
                        </xsl:when>
                        <xsl:when test="@type='Decimal' or @type='Currency'">
                            <Type>xs:decimal</Type>
                            <NumberQualifiers>
                                <Digits>20</Digits>
                                <FractionDigits>4</FractionDigits>
                                <AllowedSign>Any</AllowedSign>
                            </NumberQualifiers>
                        </xsl:when>
                        <xsl:when test="@type='Integer'">
                            <Type>xs:decimal</Type>
                            <NumberQualifiers>
                                <Digits>20</Digits>
                                <FractionDigits>0</FractionDigits>
                                <AllowedSign>Any</AllowedSign>
                            </NumberQualifiers>
                        </xsl:when>             
                    </xsl:choose>
                </ValueType>
            </column>
        </xsl:for-each>
        <!-- data -->
        <xsl:for-each select="scheme/data/o">
            <row>
                <xsl:for-each select=".//f">
                    <Value>
                        <xsl:value-of select="."/>
                    </Value>
                </xsl:for-each>
            </row>
        </xsl:for-each>
    </ValueTable>
</xsl:template>

</xsl:stylesheet>