Edit XML files in Excel

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="" xmlns:xsi="" xsi:schemaLocation=" 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>

1 Answer

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

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

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

-