Excel to XML Namespace Prefix Customization

Question

I am using Excel 2013 to convert data from a spreadsheet into XML format by using an XML map. The conversion works well, but Excel defaults all XML namespace prefixes in the output file to "ns1", as shown in the code below.

XML to Excel Conversion

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ns1:CarrierInterface xmlns:ns1="http:/exampleurl.com/">
    <ns1:Sender>sender</ns1:Sender>
    <ns1:Receiver>receiver</ns1:Receiver>
    <ns1:RecordCount>1</ns1:RecordCount>
    <ns1:SequenceID>143</ns1:SequenceID>
    <ns1:Device>
        <ns1:ID>123456789</ns1:ID>
        <ns1:Make>make</ns1:Make>
        <ns1:Model>model</ns1:Model>
        <ns1:ModelYear>2017</ns1:ModelYear>
        <ns1:DeviceWiFiType>True</ns1:DeviceWiFiType>
        <ns1:PairingState>Initial</ns1:PairingState>
    </ns1:Device>
</ns1:CarrierInterface>

Desired Output

<?xml version="1.0" encoding="UTF-8"?> 
    <carrierInterface:CarrierInterface     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:carrierInterface="http:/exampleurl.com/" xsi:schemaLocation="http:/exampleurl.com/"> 
    <carrierInterface:Sender>sender</carrierInterface:Sender> 
    <carrierInterface:Receiver>receiver</carrierInterface:Receiver> 
    <carrierInterface:RecordCount>1</carrierInterface:RecordCount> 
    <carrierInterface:SequenceID>143</carrierInterface:SequenceID> 
    <carrierInterface:Device> 
       <carrierInterface:ID>123456789</carrierInterface:ICCID> 
       <carrierInterface:Make>make</carrierInterface:Make>  
       <carrierInterface:Model>model</carrierInterface:Model> 
       <carrierInterface:ModelYear>2017</carrierInterface:ModelYear> 
       <carrierInterface:DeviceWiFiType>True</carrierInterface:DeviceWiFiType> 
       <carrierInterface:PairingState>Initial</carrierInterface:PairingState> 
    </carrierInterface:Device> 
</carrierInterface:CarrierInterface>

Is anyone aware of an easy way to customize these namespace prefixes without having to write a macro? Also, does anyone know why Excel drops the URLs given for CarrierInterface's namespace definition shown in the following lines?

Desired Output

<carrierInterface:CarrierInterface     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:carrierInterface="http:/exampleurl.com/" xsi:schemaLocation="http:/exampleurl.com/">

Actual Output

<ns1:CarrierInterface xmlns:ns1="http:/exampleurl.com/">

kjhughes · Accepted Answer · 2016-07-19 16:26:09Z

2

Namespace prefix names themselves are insignificant; it is only through their binding to a namespace value that they derive meaning.

No conformant XML processor cares about the specific namespace prefix being used, and neither should you -- it's only the namespace value that matters.

answered Jul 19, 2016 at 16:26

kjhughes

113k31 gold badges198 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kjhughes Over a year ago

@user2988717: I accept your apology for "living in a real World," but this answer applies to this real world. If you have a question about some other world, please post a proper question, not a weak dig hidden in an poorly expressed query buried in an insolent comment. Thank you.

Collectives™ on Stack Overflow

Excel to XML Namespace Prefix Customization

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related