Convert xml to heirarchical - need a nudge

Question

I have studied various solutions on this board but I am afraid I am still a bit stuck - do I need to use Muenchian grouping using keys or will preceding sibling help with item type W.

In brief, there are multiple faRecord nodes - the first item element in each record denotes what type of record it is - C, W here, but there are other types too.

The XSL I have till now will convert these records into Categories and workcodes but I am stumped as to how I can get workcodes to nest inside categories. The sequence could be C W W W C W C C W W W and so on. None of the values are unique. (There will be items nested inside workcodes and so on - but this is the first step. Each category will have 0 or more workcodes. Each workcode may have 0 or more items.)

This is the XML

<?xml version="1.0" encoding="UTF-8"?>
<EstimateDisplayRequest>
   <facXML>      
      <faRecord>
         <recordCode>33</recordCode>
         <item>
            <itemMapCode>1</itemMapCode>
            <itemValue>C</itemValue>
         </item>
         <item>
            <itemMapCode>80</itemMapCode>
            <itemValue>0</itemValue>
         </item>
         <item>
            <itemMapCode>81</itemMapCode>
            <itemValue>INTERNAL CREATIVE</itemValue>
         </item>
         <item>
            <itemMapCode>82</itemMapCode>
            <itemValue>TOTAL INTERNAL CREAT</itemValue>
         </item>             
      </faRecord>
      <faRecord>
         <recordCode>33</recordCode>
         <item>
            <itemMapCode>1</itemMapCode>
            <itemValue>W</itemValue>
         </item>
         <item>
            <itemMapCode>41</itemMapCode>
            <itemValue>0</itemValue>
         </item>
         <item>
            <itemMapCode>42</itemMapCode>
            <itemValue>TI</itemValue>
         </item>
         <item>
            <itemMapCode>43</itemMapCode>
            <itemValue>Time Work Code</itemValue>
         </item>
      </faRecord>
      <faRecord>
         <recordCode>33</recordCode>
         <item>
            <itemMapCode>1</itemMapCode>
            <itemValue>W</itemValue>
         </item>
         <item>
            <itemMapCode>41</itemMapCode>
            <itemValue>0</itemValue>
         </item>
         <item>
            <itemMapCode>42</itemMapCode>
            <itemValue>TI</itemValue>
         </item>
         <item>
            <itemMapCode>43</itemMapCode>
            <itemValue>Time Work Code</itemValue>
         </item>
      </faRecord>
      <faRecord>
         <recordCode>33</recordCode>
         <item>
            <itemMapCode>1</itemMapCode>
            <itemValue>C</itemValue>
         </item>
         <item>
            <itemMapCode>80</itemMapCode>
            <itemValue>1</itemValue>
         </item>
         <item>
            <itemMapCode>81</itemMapCode>
            <itemValue>EXTERNAL CREATIVE</itemValue>
         </item>
         <item>
            <itemMapCode>82</itemMapCode>
            <itemValue>TOTAL EXTERNAL CREAT</itemValue>
         </item>             
      </faRecord>
      <faRecord>
         <recordCode>33</recordCode>
         <item>
            <itemMapCode>1</itemMapCode>
            <itemValue>W</itemValue>
         </item>
         <item>
            <itemMapCode>41</itemMapCode>
            <itemValue>0</itemValue>
         </item>
         <item>
            <itemMapCode>42</itemMapCode>
            <itemValue>TI2</itemValue>
         </item>
         <item>
            <itemMapCode>43</itemMapCode>
            <itemValue>Time Work Code</itemValue>
         </item>
      </faRecord>
   </facXML>
</EstimateDisplayRequest>

This is the XSL I have so far:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="EstimateDisplayRequest">
        <MCSResponse>
            <EstimateDisplayResponse>
                <xsl:apply-templates select="*" mode="EstimateDisplayResponse" />
            </EstimateDisplayResponse>
        </MCSResponse>
    </xsl:template>

    <xsl:template match="faRecord[item/itemMapCode='1' and item/itemValue='C']" mode="EstimateDisplayResponse">
            <EstimateDisplayResponseDetailCategory>
                    <xsl:attribute name="id"><xsl:number/></xsl:attribute>
                    <xsl:apply-templates select="child::*[not(self::recordCode)]" />
            </EstimateDisplayResponseDetailCategory>
    </xsl:template>

    <xsl:template match="faRecord[item/itemMapCode='1' and item/itemValue='W']" mode="EstimateDisplayResponse">
            <EstimateDisplayResponseDetailWorkcode>
                    <xsl:attribute name="id"><xsl:number/></xsl:attribute>
                    <xsl:apply-templates select="child::*[not(self::recordCode)]" />
            </EstimateDisplayResponseDetailWorkcode>
    </xsl:template>


    <xsl:template match="item[itemMapCode='1']" />     

    <xsl:template match="item[itemMapCode='80']" >
        <CategoryCode>
            <xsl:value-of select="./itemValue" />
        </CategoryCode>
    </xsl:template>
    <xsl:template match="item[itemMapCode='81']" >
        <CategoryName>
            <xsl:value-of select="./itemValue" />
        </CategoryName>
    </xsl:template>
    <xsl:template match="item[itemMapCode='82']" >
        <CategoryTotalName>
            <xsl:value-of select="./itemValue" />
        </CategoryTotalName>
    </xsl:template>    

    <xsl:template match="item[itemMapCode='41']" >
        <WorkCodeCategory>
            <xsl:value-of select="./itemValue" />
        </WorkCodeCategory>
    </xsl:template>
    <xsl:template match="item[itemMapCode='42']" >
        <WorkCode>
            <xsl:value-of select="./itemValue" />
        </WorkCode>
    </xsl:template>
    <xsl:template match="item[itemMapCode='43']" >
        <WorkCodeName>
            <xsl:value-of select="./itemValue" />
        </WorkCodeName>
    </xsl:template>    
</xsl:stylesheet>

TIA!

[Sorry, I should've listed the output as well - a list of categories. I tried using a for-each for categories with following-siblings but I end up getting ALL remaining workcode elements as I go down the nodes - which is expected I guess :( ]

<mcsresponse>
    <estimatedisplayresponse>
        <estimatedisplayresponsedetailcategory id="1">
            <categorycode>0</categorycode>
            <categoryname>INTERNAL CREATIVE</categoryname>
            <categorytotalname>TOTAL INTERNAL CREAT</categorytotalname>                
            <estimatedisplayresponsedetailworkcode id="2">
                <workcodecategory>0</workcodecategory>
                <workcode>TI</workcode>
                <workcodename>Time Work Code</workcodename>                    
            </estimatedisplayresponsedetailworkcode>
            <estimatedisplayresponsedetailworkcode id="3">
                <workcodecategory>0</workcodecategory>
                <workcode>TI</workcode>
                <workcodename>Time Work Code</workcodename>                    
            </estimatedisplayresponsedetailworkcode>
        </estimatedisplayresponsedetailcategory>
        <estimatedisplayresponsedetailcategory id="4">
            <categorycode>0</categorycode>
            <categoryname>INTERNAL CREATIVE</categoryname>
            <categorytotalname>TOTAL INTERNAL CREAT</categorytotalname>               
            <else>YNNYN</else>
            <estimatedisplayresponsedetailworkcode id="5">
                <workcodecategory>0</workcodecategory>
                <workcode>TI</workcode>
                <workcodename>Time Work Code</workcodename>                    
            </estimatedisplayresponsedetailworkcode>
        </estimatedisplayresponsedetailcategory>
    </estimatedisplayresponse>
</mcsresponse>

So you're saying with a sequence of `C W W W C W C C W W W` the first 3 `W` "belong" to the first `C`, and so on? Please post the exact output you expect for such a case. — Tomalak, Jul 15 '14 at 12:06
Apologies. Added expected output. Tried a for-each with C and following-siblings - but each C will list all remaining Ws as expected. — user2668539, Jul 15 '14 at 12:16
Also see: ["using XPath to select contiguous elements with a certain attribute value"](http://stackoverflow.com/questions/8961220/using-xpath-to-select-contiguous-elements-with-a-certain-attribute-value) — Tomalak, Jul 15 '14 at 12:18

Tomalak · Accepted Answer · 2014-07-15T12:30:10.440

This XSLT 1.0 transformation:

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

  <xsl:key name="kWork" match="faRecord[item[itemMapCode = 1]/itemValue = 'W']" use="
    generate-id(preceding-sibling::faRecord[item[itemMapCode = 1]/itemValue = 'C'][1])
  " />

  <xsl:template match="EstimateDisplayRequest">
    <MCSResponse>
      <EstimateDisplayResponse>
        <xsl:apply-templates select="*/faRecord[item[itemMapCode = 1]/itemValue = 'C']" />
      </EstimateDisplayResponse>
    </MCSResponse>
  </xsl:template>

  <xsl:template match="faRecord[item[itemMapCode = 1]/itemValue = 'C']">
    <EstimateDisplayResponseDetailCategory>
      <xsl:attribute name="id"><xsl:number/></xsl:attribute>
      <xsl:apply-templates select="item" />
      <xsl:apply-templates select="key('kWork', generate-id())" />
    </EstimateDisplayResponseDetailCategory>
  </xsl:template>

  <xsl:template match="faRecord[item[itemMapCode = 1]/itemValue = 'W']">
    <EstimateDisplayResponseDetailWorkcode>
      <xsl:attribute name="id"><xsl:number/></xsl:attribute>
      <xsl:apply-templates select="item" />
    </EstimateDisplayResponseDetailWorkcode>
  </xsl:template>

  <!-- this catches <recordCode> and unknown elements -->
  <xsl:template match="faRecord/*" />

  <xsl:template match="faRecord/item">
    <xsl:variable name="elemName">
      <xsl:choose>
        <xsl:when test="itemMapCode = 41">WorkCodeCategory</xsl:when>
        <xsl:when test="itemMapCode = 42">WorkCode</xsl:when>
        <xsl:when test="itemMapCode = 43">WorkCodeName</xsl:when>
        <xsl:when test="itemMapCode = 80">CategoryCode</xsl:when>
        <xsl:when test="itemMapCode = 81">CategoryName</xsl:when>
        <xsl:when test="itemMapCode = 82">CategoryTotalName</xsl:when>
      </xsl:choose>
    </xsl:variable>

    <xsl:if test="$elemName != ''">
      <xsl:element name="{$elemName}">
        <xsl:value-of select="./itemValue" />
      </xsl:element>
    </xsl:if>
    <xsl:if test="$elemName = '' and itemMapCode != 1">
      <xsl:message>Warning: no mapping for itemMapCode '<xsl:value-of select="itemMapCode" />'</xsl:message>
    </xsl:if>
  </xsl:template>
</xsl:stylesheet>

gives me

<MCSResponse>
   <EstimateDisplayResponse>
      <EstimateDisplayResponseDetailCategory id="1">
         <CategoryCode>0</CategoryCode>
         <CategoryName>INTERNAL CREATIVE</CategoryName>
         <CategoryTotalName>TOTAL INTERNAL CREAT</CategoryTotalName>
         <EstimateDisplayResponseDetailWorkcode id="2">
            <WorkCodeCategory>0</WorkCodeCategory>
            <WorkCode>TI</WorkCode>
            <WorkCodeName>Time Work Code</WorkCodeName>
         </EstimateDisplayResponseDetailWorkcode>
         <EstimateDisplayResponseDetailWorkcode id="3">
            <WorkCodeCategory>0</WorkCodeCategory>
            <WorkCode>TI</WorkCode>
            <WorkCodeName>Time Work Code</WorkCodeName>
         </EstimateDisplayResponseDetailWorkcode>
      </EstimateDisplayResponseDetailCategory>
      <EstimateDisplayResponseDetailCategory id="4">
         <CategoryCode>1</CategoryCode>
         <CategoryName>EXTERNAL CREATIVE</CategoryName>
         <CategoryTotalName>TOTAL EXTERNAL CREAT</CategoryTotalName>
         <EstimateDisplayResponseDetailWorkcode id="5">
            <WorkCodeCategory>0</WorkCodeCategory>
            <WorkCode>TI2</WorkCode>
            <WorkCodeName>Time Work Code</WorkCodeName>
         </EstimateDisplayResponseDetailWorkcode>
      </EstimateDisplayResponseDetailCategory>
   </EstimateDisplayResponse>
</MCSResponse>

The <xsl:key> indexes every type W record by the unique ID of the immediately preceding type C record.

This way it's easy to process all type C records via

<xsl:apply-templates select="*/faRecord[item[itemMapCode = 1]/itemValue = 'C']" />

and their respective type W records via

<xsl:apply-templates select="`key('kWork', generate-id()) />

Sorry, Tomalak, please see my expected output - with my original xsl, I already get what your output is. I need the workcodes to be nested under the preceding category record. Many apologies. — user2668539, Jul 15 '14 at 12:15
See modified answer. (BTW, why is your expected output all lower-case? Additionally, your expected output does not match your input. For example, where is the `TI2` in your version?) — Tomalak, Jul 15 '14 at 12:36
Many thanks! I will give this a go. expected output lowercase - I don't have a client tool so using a website called XsltCake :( That is converting all to lower case. And TI2 - typo, I edited it manually. — user2668539, Jul 15 '14 at 13:08
What do you mean by "I don't have a client tool"? o_O Why not install one? There are plenty. Almost every programming language has XSLT support as well. — Tomalak, Jul 15 '14 at 13:21

Convert xml to heirarchical - need a nudge

1 Answers1