Extract data fields from XML into Excel

Question

I have a huge Excel spreadsheet that contains records of Customers where each column is a field. There's a field called Demographics which contains survey results of Customers and it's entirely in XML format. That is each Customer has a survey result on their demographic info like Gender, Marital Status, Income, Age, etc. which is given in XML format. It is notable that the whole XML is like a big chunk of text in a cell of the spreadsheet which I can't use to analyze the data.

The problem is now I want to extract the demographic data of each Customer and present it as fields in the same spreadsheet, i.e., each Customer has an Age, Gender, etc. How can I do this? I have tried to Google this but it seems like nobody has the same problem. I'm using Excel 2007/2010.

Answer 1

If the XML has a flat structure like this:

you could just convert the "XML" column into an XML file and re-import it into your excel sheet. To do so, just

copy & paste the XML column into a text editor,

<customer><age>34</age><gender>m</gender></customer>
<customer><age>38</age><gender>f</gender></customer>

add a opening/closing root element,

<customers>
    <customer><age>34</age><gender>m</gender></customer>
    <customer><age>38</age><gender>f</gender></customer>
</customers>

and save as customers.xml.

Now. import it back into excel, using XML-> Import from the Developer Tools Ribbon. Based on the "flat" XML structure, Excel will create columns for the single values:

Of course this will work only if the XML structure is suitable, but if it does, it works without writing a macro or parsing the "XML" text content manually.