xsl transform of dataset.writexml XML

Question

I have a scenario where I am exporting a .net dataset to an xml file, but I want to transform the stucture of the xml output to a more hierarchical structure. Below is the format of the dataset as exported by dataset.xmlwrite() method.

<NewDataSet>
    <Table>
        <id>100</id>
        <empname>Joe Smith</empname>
        <phone>111-111-1111</phone>
        <mobile>222-222-2222</mobile>

    </Table>
    <Table>
        <id>101</id>
        <empname>Ann Jensen</empname>
        <phone>111-111-0000</phone>
        <mobile>222-222-0000</mobile>
    </Table>
<NewDataSet>

I want to convert it to the below structure. I am a newbie at xsl transforms and I am not sure how keep the <Table> element from repeating for every record in the dataset.

<NewDataSet>
    <Table>
        <employee id="100">
            <empname>Joe Smith</empname>
            <phone>111-111-1111</phone>
            <mobile>222-222-2222</mobile>
        </employee>
        <employee id="101">
            <empname>Ann Jensen</empname>
            <phone>111-111-0000</phone>
            <mobile>222-222-0000</mobile>
        </employee>
    </Table>
<NewDataSet>

I tried using a combination of xsl:for-each and xsl:if statements to get what I wanted, but so far I have not been able to get it to work. Any assistance would be greatly appreciated.

Good question, +1. See my answer for a simple and powerful solution that is fully in the spirit of XSLT: uses and overrides the identity rule. Note also that no conditional XSLT instructions are used at all. :) — Dimitre Novatchev, Jan 11 '11 at 05:04

Dimitre Novatchev · Accepted Answer · 2011-01-11T05:01:18.930

This transformation:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <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="Table[1]">
  <Table>
   <xsl:apply-templates select="../Table/id"/>
  </Table>
 </xsl:template>

 <xsl:template match="Table[position()>1]"/>

 <xsl:template match="Table/id">
  <employee id="{.}">
   <xsl:apply-templates select=
      "following-sibling::node()"/>
  </employee>
 </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document (corrected to be well-formed):

<NewDataSet>
    <Table>
        <id>100</id>
        <empname>Joe Smith</empname>
        <phone>111-111-1111</phone>
        <mobile>222-222-2222</mobile>
    </Table>
    <Table>
        <id>101</id>
        <empname>Ann Jensen</empname>
        <phone>111-111-0000</phone>
        <mobile>222-222-0000</mobile>
    </Table>
</NewDataSet>

produces the wanted, correct result:

<NewDataSet>
   <Table>
      <employee id="100">
         <empname>Joe Smith</empname>
         <phone>111-111-1111</phone>
         <mobile>222-222-2222</mobile>
      </employee>
      <employee id="101">
         <empname>Ann Jensen</empname>
         <phone>111-111-0000</phone>
         <mobile>222-222-0000</mobile>
      </employee>
   </Table>
</NewDataSet>

Explanation:

The identity rule copies every node "as-is".
The first Table element is matched by an overriding template. This creates the only Table in the result and applies templates to the children of all Table elements.
The id element is matched by an overriding template, that converts it to an employee element having an id attribute with the value of the id element. This also applies templates from inside the employee element to all other siblings of id and they are copied as-is by the identity template.

@dvhh: Yes, XSLT is an extremely elegant and powerful language -- largely due to the "identity rule" design pattern. — Dimitre Novatchev, Jan 11 '11 at 05:25
It is clear I have a lot to learn about XSLT. I understand most of what you did (thanks for the explanation). But one clarification - The following-sibling::node() code selects all of the siblings to the id element - not just the immediate "following" sibling? I would not have expected that. — Clinemi, Jan 11 '11 at 15:26
@Clinemi: Yes, the `following-sibling::` axis selects *all* nodes from the node-test that are following siblings of the context node. If you want only the first following sibling, you specify this, for example: `following-sibling::node()[1]` — Dimitre Novatchev, Jan 11 '11 at 15:32
@Dimitre: +1 Better answer. But a little confuse... Wouldn't it be better something like `/` -> `NewDataSet`; `NewDataSet` -> `Table`; `Table` -> `employee`; `id` -> nothing? — , Jan 13 '11 at 15:08
@Alejandro: I am not sure I understand your question. I wrote this code aiming at it to be as short (in number of templates) as possible. — Dimitre Novatchev, Jan 13 '11 at 16:16

score 2 · Answer 2 · answered Jan 11 '11 at 04:58

this should do the trick

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
    <Table>
    <xsl:for-each select="/NewDataSet/Table">
        <employee>
            <xsl:attribute name="id"><xsl:value-of select="id/."/></xsl:attribute>
            <xsl:for-each select="*">
                <xsl:choose>
                    <xsl:when test="name() = 'id' "/>
                    <xsl:otherwise>
                        <xsl:copy-of select="."/>
                    </xsl:otherwise>
                </xsl:choose>
            </xsl:for-each>
        </employee>
    </xsl:for-each>
    </Table>
</xsl:template>
</xsl:stylesheet>

don't mention it, my answer is a little bit simple compared to @Dimitre's one — dvhh, Jan 11 '11 at 16:29
Thanks, I prefer the looping solutions over the template based ones as I can get my head around them more readily and hence bend them to my will! — Jon H, Dec 05 '11 at 11:58

xsl transform of dataset.writexml XML

2 Answers2