complex scenario for xslt

Question

Updated to include some additional nuances

I have an XML scenario that will require some complex XSLT. I've been trying to figure it out on my own but haven't been unsuccessful so far.

To start with, here is a mock XML structure.

<Author id="1234">
  <reviews>poor</reviews>
  <Media>     
     <MediaSet>
         <MediaCode type="CD">474747</MediaCode>
     </MediaSet>
     <MediaSet>
       <MediaCode type="CD">535353</MediaCode>
     </MediaSet>
     <MediaSet> 
        <MediaCode type="eBook">989898</MediaCode>
     </MediaSet>
     <MediaSet>
        <MediaCode type="download">202020</MediaCode>
     </MediaSet>
     <MediaSet>
         <MediaCode type="book">161616</MediaCode>
     </MediaSet>
     <MediaSet>
         <MediaCode type="DVD">828282</MediaCode>
     </MediaSet>
     <MediaSet>
         <OtherCode type="widget" number="747474"/>   <!--note different element name and structure-->
     </MediaSet>
   </Media>
  <name>JimBob</name>
</Author>

This example is a much simplified version of what I'm dealing with but I want to create an output that looks something like this for a database import:

<row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="CD">474747</field>
    <field name="name">JimBob</field>
</row>
<row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="CD">535353</field>
    <field name="name">JimBob</field>
</row>
<row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="eBook">989898</field>
    <field name="name">JimBob</field>
</row>
<row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="download">989898</field>
    <field name="name">JimBob</field>
</row>
<row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="widget">555555</field>
    <field name="name">JimBob</field>
</row>

• CD, eBooks, downloads can occur zero or more times.
• I need to create a separate row for each instance
• There are certain elements(e.g. "book", "DVD", and many more) that I want to ignore.
• There are 100,000s of 'authors' each with their own unique combination of 'MediaCodes'.
• There are mixed elements that I need to pull data from

I have the code to take it from the starting XML structure to row/field XML structure for database import working fine, the problem I'm dealing with is iterating through the XML and creating multiple rows when multiple data points exists.

Is this something that can be managed with XSLT alone or will I have to use another language to process?

Of note, the XML file I'm processing has much more complex structure and is roughly 325MB.

Answer 1

First, both your examples, input and output, are invalid. The input particularly so, due to numerous mismatches between opening and closing tags, for example:

<MediaCode type="eBook">989898</Type>

Assuming a corrected input, which also includes a root element:

<Authors>
    <Author id="1234">
       <reviews>poor</reviews>
       <MediaSet>
          <MediaCode type="CD">101</MediaCode>
          <MediaCode type="CD">102</MediaCode>
          <MediaCode type="eBook">111</MediaCode>
          <MediaCode type="download">121</MediaCode>
          <MediaCode type="book">131</MediaCode>
       </MediaSet>
       <name>Adam</name>
    </Author>
    <Author id="5678">
       <reviews>good</reviews>
       <MediaSet>
          <MediaCode type="CD">201</MediaCode>
          <MediaCode type="eBook">202</MediaCode>
          <MediaCode type="download">203</MediaCode>
          <MediaCode type="book">204</MediaCode>
       </MediaSet>
       <name>Betty</name>
    </Author>
</Authors>

you can use a stylesheet like this:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">
<rows>
    <xsl:for-each select="Authors/Author/MediaSet/MediaCode[@type!='book']">
        <row>
            <field name="authorID"><xsl:value-of select="../../@id" /></field>
            <field name="reviews"><xsl:value-of select="../../reviews" /></field>
            <field name="{@type}"><xsl:value-of select="." /></field>
            <field name="name"><xsl:value-of select="../../name" /></field>
        </row>
    </xsl:for-each>
</rows>
</xsl:template>
</xsl:stylesheet>

to produce the following result (again, with a root element):

<?xml version="1.0" encoding="utf-8"?>
<rows>
  <row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="CD">101</field>
    <field name="name">Adam</field>
  </row>
  <row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="CD">102</field>
    <field name="name">Adam</field>
  </row>
  <row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="eBook">111</field>
    <field name="name">Adam</field>
  </row>
  <row>
    <field name="authorID">1234</field>
    <field name="reviews">poor</field>
    <field name="download">121</field>
    <field name="name">Adam</field>
  </row>
  <row>
    <field name="authorID">5678</field>
    <field name="reviews">good</field>
    <field name="CD">201</field>
    <field name="name">Betty</field>
  </row>
  <row>
    <field name="authorID">5678</field>
    <field name="reviews">good</field>
    <field name="eBook">202</field>
    <field name="name">Betty</field>
  </row>
  <row>
    <field name="authorID">5678</field>
    <field name="reviews">good</field>
    <field name="download">203</field>
    <field name="name">Betty</field>
  </row>
</rows>

Answer 2

AFAIKS, this is not complex at all:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">

  <xsl:output indent="yes"/>

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

  <xsl:template match="text()"/>

  <xsl:template match="Author/MediaSet/MediaCode[@type!='book']">
    <row>
      <field name="authorId"><xsl:value-of select="../../@id"/></field>
      <field name="reviews"><xsl:value-of select="../../reviews"/></field>
      <field name="{@type}"><xsl:value-of select="."/></field>
      <field name="name"><xsl:value-of select="../../name"/></field>
    </row>
  </xsl:template>
</xsl:stylesheet>