Flatten Xml Using Xslt, Creating Nodes from Values, Dashes for Spaces

Question

Seen lots of similar questions here on StackOverflow, and I've been trying to follow those all day to get my specific situation figured out, but little luck.

I want to take this xml:

<RestaurantData>
  <Foodie>
    <Id>123</Id>
    <Name>Bob</Name>
    <Restaurants>
      <Restaurant>
        <Name>Noma</Name>
        <TimesEaten>12</TimesEaten>
      </Restaurant>
      <Restaurant>
        <Name>Eleven Madison Park</Name>
        <TimesEaten>15</TimesEaten>
      </Restaurant>
      <Restaurant>
        <Name>Mugaritz</Name>
        <TimesEaten>15</TimesEaten>
      </Restaurant>
    </Restaurants>
  </Foodie>     
  <Foodie>
    <Id>789</Id>
    <Name>Charlie</Name>
    <Restaurants>
      <Restaurant>
        <Name>Noma</Name>
        <TimesEaten>1</TimesEaten>
      </Restaurant>
      <Restaurant>
        <Name>Eleven Madison Park</Name>
        <TimesEaten>125</TimesEaten>
      </Restaurant>
    </Restaurants>
  </Foodie>
</RestaurantData>

and apply an XSLT to get this:

<RestaurantData>
  <Foodie>
    <Id>123</Id>
    <Name>Bob</Name>
    <Noma>12</Noma>
    <Eleven-Madison-Park>15</Eleven-Madison-Park>
    <Mugaritz>15</Mugaritz>
  </Foodie>  
  <Foodie>
    <Id>789</Id>
    <Name>Charlie</Name>
    <Noma>1</Noma>
    <Eleven-Madison-Park>125</Eleven-Madison-Park>        
  </Foodie>
</RestaurantData>

The closest I've come to my XSLT is this, and the results are not very close at all to the above:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:my="my:my" exclude-result-prefixes="my" >
    <xsl:output omit-xml-declaration="yes" indent="yes"/>
    <xsl:strip-space elements="*"/>

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

  <xsl:template match="*[ancestor::Restaurant]">
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="text()[ancestor::Restaurant]">
    <xsl:value-of select="normalize-space()"/>
  </xsl:template>
</xsl:stylesheet>

Any help would be greatly appreciated.

Are you sure you want to do this? It's not a good structure to have. — michael.hor257k, May 20 '14 at 23:02
Although @helderdarocha solved your transformation task I think that the original file is more in the spirit of a well-formed XML than the target one. As a rule replacing tags representing a class (e.g. restaurant name) by class instances (e.g. "Eleven Madison Park") makes the subsequent processing more difficult. — Marcus Rickert, May 20 '14 at 23:03
If you really want to "flatten" the structure into one element per restaurant, consider using attributes, e.g. `15`. — michael.hor257k, May 20 '14 at 23:18
Well, my reason for doing it was to feed SSRS with an xml data source, and I couldn't figure out how to get SSRS to handle a non-flat xml structure, so I decided to flatten it. — Marvin, May 23 '14 at 21:51

helderdarocha · Accepted Answer · 2014-05-20T23:14:15.287

Replace your second and third templates with these two:

<xsl:template match="Restaurants">
    <xsl:apply-templates/>
</xsl:template>

<xsl:template match="Restaurant">
    <xsl:element name="{translate(Name,' ','-')}">
        <xsl:value-of select="TimesEaten"/>
    </xsl:element>
</xsl:template>

The first one flattens the Restaurants tree. The second flattens each Restaurant tree replacing their children for one element. The {} are Attribute Value Templates that allow XPath expressions inside common attributes. The translate() function replaces spaces with dashes so that the name is generated correctly.

You should be aware that, depending on the names of your restaurants, the transformation may fail due to invalid tag names. Besides replacing the spaces, you should check for other situations that might generate illegal QNames for your tags, such as apostrophes, names that start with numbers, empty-strings, etc. In XML you can't have tags like <Charlie's> or <5th Ave.>, and replacing them might be tricky (at least in XSLT 1.0).

To convert Charlie's into Charlie-s, for example, you can create a variable to store the ':

<xsl:variable name="apos">'</xsl:variable>

and add the replacement to the translate:

translate(Name,concat(' ',$apos),'--')

For words starting with numbers, in XSLT 2.0 you could use replace() and regular expressions, but in XSLT 1.0 you will need to check for digits at the beginning of each name.

Thanks not only for a working solution but the explanation on how the parts all work! — Marvin, May 20 '14 at 23:29
This kind of "flattening" is interesting as an exercise, but I agree with the comments by @michael.hor257k and @ MarcusRickert. It's not really a practical solution. I think the suggestion by michael.hor257k to use attributes is a very good one. — helderdarocha, May 20 '14 at 23:33

Flatten Xml Using Xslt, Creating Nodes from Values, Dashes for Spaces

1 Answers1