Split Excel column with XML inside

Question

Is there a way to break an Excel column containing XML into several columns like this:

BEFORE

| Apples | Pears | XML                              |
| ------ | ----- | ---------------------------------|
| 35     | 18    | <Plums>34</Plums><Figs>19</Figs> |
| 86     | 55    | <Plums>12</Plums><Figs>62</Figs> |
| 99     | 12    | <Plums>18</Plums><Figs>23</Figs> |

AFTER

| Apples | Pears | Plums | Figs |
| ------ | ----- | ----- | ---- |
| 35     | 18    | 34    | 19   |
| 86     | 55    | 12    | 62   |
| 99     | 12    | 18    | 23   |

The original table comes from an SQL Server that stores XML in a text column. If the XML column contained comma-separated values, I'd tell Excel to do Text to Columns. Is there a similar feature for XML?

Answer 1

In addition to Text manipulation in Excel there are two other options.

1. You could use VBA to parse the XML. You could either use string functions to look for tags or actually use XML parsers to walk the content (see this question on SO for more info).

2. You can use the XML Source feature in Excel (see here for an overview). The easiest way to use this is to load in an XML file. Excel will automatically try and create an XML map for the loaded data and load it into a table. For it to really work well though you need to create a schema.

For your data I added a root element and saved this in a file:

<Fruits>
    <Plums>34</Plums><Figs>19</Figs>
    <Plums>12</Plums><Figs>62</Figs>
    <Plums>18</Plums><Figs>23</Figs>
</Fruits>

Loaded into Excel this creates the following table:

Answer 2

Given that the stuff you don't want is the same size (at least in the example) use text to columns with 'Fixed Width'