title: Create PivotTable from a XML File in Excel
body: <p>Is there any way to do this?</p>
  
  <p>The structure my file has is something like the one below, but the file is large (around 5000 records in an 8MB xml file)  and it has at least has one parent with many children that can vary from entry to entry (meaning that for some records, the number of children is not fixed and they use the same tag, like children of <pre>MainCourseCategories</pre> on the example below)</p>
  
  <p>When I use Insert -> PivotTable -> Use an external data source -> Choose Connection... I get an error that says "The type of connection selected cannot be used to create a PivotTable"</p>
  
  <pre><code>&lt;?xml version="1.0" ?&gt;
  &lt;institutions&gt;
      &lt;institution&gt;
          &lt;TypeOfProvider&gt;Provider Z&lt;/TypeOfProvider&gt;
          &lt;MainCourseCategories&gt;
              &lt;MainCourseCategory&gt;Category 1&lt;/MainCourseCategory&gt;
              &lt;MainCourseCategory&gt;Category 2&lt;/MainCourseCategory&gt;
              &lt;MainCourseCategory&gt;Category 3&lt;/MainCourseCategory&gt;
              &lt;MainCourseCategory&gt;Category 4&lt;/MainCourseCategory&gt;
          &lt;/MainCourseCategories&gt;
      &lt;/institution&gt;
      &lt;institution&gt;
          &lt;TypeOfProvider&gt;Provider A&lt;/TypeOfProvider&gt;
          &lt;MainCourseCategories&gt;
              &lt;MainCourseCategory&gt;Category 1&lt;/MainCourseCategory&gt;
              &lt;MainCourseCategory&gt;Category 2&lt;/MainCourseCategory&gt;
              &lt;MainCourseCategory&gt;Category 7&lt;/MainCourseCategory&gt;
          &lt;/MainCourseCategories&gt;
      &lt;/institution&gt;
      &lt;/institutions&gt;
  </code></pre>