Convert (part of) XML data back to tabular data or CSV/tab separated without using scripting

Question

Is there an easy way to convert (part of) XML data back to tabular data, so comma separated values or tab separated or Excel? I sometimes get XML log files, and for clarity and comparing it more easily with other sources it would be easier to extract the data just as a flat table, for example in Excel.

I've looked at the XMLtools plug-in and other plug-ins but couldn't find any option. So for example the data is like this:

<ChildTaskStatus>
  <LoadSubStatus>
    <TaskStatus>RanToCompletion</TaskStatus>
    <Template>NL_CM_0_1_1</Template>
    <ItemsTotal>41219</ItemsTotal>
    <ElapsedTime>00:00:04</ElapsedTime>
  </LoadSubStatus>
  <LoadSubStatus>
    <TaskStatus>RanToCompletion</TaskStatus>
    <Template>NL_CM_18_7_1</Template>
    <ItemsTotal>168930</ItemsTotal>
    <ElapsedTime>00:00:53</ElapsedTime>
  </LoadSubStatus>
  <LoadSubStatus>
    <TaskStatus>RanToCompletion</TaskStatus>
    <Template>NL_CM_12_8_1</Template>
    <ItemsTotal>231122</ItemsTotal>
    <ElapsedTime>00:00:43</ElapsedTime>
  </LoadSubStatus>
</ChildTaskStatus>

And I would like to extract the data to just this (exact format doesn't matter, any csv/tab separated or tabular format will do):

TaskStatus	Template	ItemsTotal	ElapsedTime
RanToCompletion	NL_CM_0_1_1	41219	00:00:04
RanToCompletion	NL_CM_18_7_1	168930	00:00:53
RanToCompletion	NL_CM_12_8_1	231122	00:00:43

BTW, the actual file is larger obviously, and with more tree nodes.

So, is there any way to do this easily without having to write a script for each different instance, for example an option in Excel or Notepad++? I understand I would have to at least specify the XML tags for the four columns, but I mean is there a tool or app to do this?

Answer 1

Excel is able to import XML data into a worksheet in versions 2007 - 2021 and also latest Office 365.

There are many ways to load the data. They're listed here. To quote the simpliest way:

Open an XML data file to import its data

1. Click File > Open.
   
   If you're using Excel 2007, click Microsoft Office Button > Open.

2. In the Open dialog box, click the drive, folder, or Internet location that has the file that you want to open.

3. Select the file and click Open.

4. If the Import XML dialog box appears, the file you opened refers to one or more Extensible Stylesheet Language Transformation (XSLT) style sheets, so you can click one of the following options:

• Open the file without applying a style sheet - The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings, and data in rows below the column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row. In this case, Excel doesn't infer a schema, and you can't use an XML Map.

• Open the file with the following style sheet applied (select one) -
  Select the style sheet that you want to apply, and then click OK. The XML data is formatted according to the style sheet that you selected.
  
  Note: The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel doesn't infer a schema, and you can't use an XML Map.

5. If the Open XML dialog box appears, the XML file doesn't have any XSLT style sheet references. To open the file, click one of the following options:

• Click As an XML table to create an XML table in a new workbook.
  
  The contents of the file are imported into the XML table. If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.

• Click As a read-only workbook.
  
  The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings, and data in rows below the column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row. In this case, Excel doesn't infer a schema, and you can't use an XML Map.
  
  The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel doesn't infer a schema, and you can't use an XML Map.

Click Use the XML Source task pane.

The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.

If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.

Or just drag & drop the .xml file to a new worksheet.