Excel – Split Excel column with XML inside

microsoft excelmicrosoft-excel-2010xml

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?

Best Answer

  • 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:

    enter image description here

  • Related Question