Excel – Edit XML files in Excel

microsoft excelxml

Is there a simple/better way in Excel to EDIT XML files?
I got a XML file (see below). And I would like to edit directly some inner values and save it in the same format again.
Form Below I would like to change:
Username (MisterX)
User Role (Admin)
Values of the permissions for all the applications (YES/NO/Numeric value)
(adding now permissions aside from A,B,C is not needed just changing the value)

The XSD to the XML is available.
When I open the XML I got three options displayed by Excel:
1: As an XML Table -> Selecting this displays a Excel file and all Values are displayed in one row: 

[MisterX][Admin][YES][NO][1.5][YES][YES][2.0][YES][12][NO]

This is stupid because I don't see that names of the permissions. Saving it from here as XML removes the link to the XSD and adds the "ns1:" as prefix.

2: As a read only Workbook -> Selecting this displays a Excel file and all Values are displayed in one row + a header for the row with the names of the values like:

[/username][/userrole][AppDesc/Applic1/PermissionA][AppDesc/Applic1/PermissionB] ...  
[MisterX][Admin][YES][NO] ...

This would be nice if it wouln't be in a row because this way I cant easily read the names of the values. Saving is not possible as XML (no mapping)

3: Use XML Source Task pane -> Shows an empty Excel + opened "XML Source" pane.
Here I could create a mapping between the XML fields and cells for saving.
But still I don't see the current values.

Is there a way to edit the Excel file in a more comfortable way in Excel?
(Microsoft Excel 365 Pro)

<?xml version="1.0" encoding="UTF-8"?>
<desclist xmlns="http://www.example.com/ws/aimsl/desclist" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.com/ws/desclist desclist.xsd">
        <username>MisterX</username>
        <userrole>Admin</userrole>
        <AppDesc>
            <Applic1>
                <PermissionA>YES</PermissionA>
                <PermissionB>NO</PermissionB>
                <PermissionC>1.5</PermissionC>
            </Applic1>
            <Applic2>
                <PermissionA>YES</PermissionA>
                <PermissionB>YES</PermissionB>
                <PermissionC>2.0</PermissionC>
            </Applic2>
            <Applic3>
                <PermissionA>YES</PermissionA>
                <PermissionB>12</PermissionB>
                <PermissionC>NO</PermissionC>
            </Applic3>
        </AppDesc>
    </desclist>

Best Answer

To edit your xml data manually, you can do the following

  • On the Developer Ribbon, XML Tab, Import
  • You may see this window:

enter image description here

  • Click OK and import to A1
  • On that same tab, click Source which will open the XML Source pane with the mappings
  • As you select cells on the worksheet, the different nodes will be highlighted in the XML Source pane, so you can see what is being referred to.
  • You can then alter the different values and save the document back as an XML document. The changes will be maintained.
  • With regard to the ns1:, I refer you to Excel to XML Namespace Prefix Customization. But there are other articles in SO that have can guide you in removing the ns1:, should you choose to do that.

enter image description here -

Related Question