0

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>
  • What is the complicated/worse way you are using now? Also, you show five permissions for the applications, but each application only has three permissions. Do you need to add permissions also? – Ron Rosenfeld May 15 at 19:05
  • @RonRosenfeld: now, it is not working because it adds this "ns1:" prefix. the 5 permissions are option that I can fill there. no I don't need to add new permissions per application there are the permissions (ABC) defined and only the values are changed. – Paul Edison May 15 at 22:30
  • I'm sorry. But the problem with your current method is not clear to me. If the only problem you are having is adding an ns1: prefix, then you need to change something in your save routine. – Ron Rosenfeld May 16 at 0:39
  • @RonRosenfeld: The Problem is 1: On opening I don't see what value is wath permission. 2: on saving the change of the structure. it is excel ... how could I change the save routine? – Paul Edison May 16 at 16:52
0

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 -

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.