Setting multiple namespaces in an XML with VBA

Question

I am trying to set multiple namespaces with prefixes to my root element.

I managed partially to do so with .DocumentElement.setAttribute:

In excel I am using reference to MicrosoftXML, v6.0.

Sub ExportAsXML()

    Set oXMLDoc = CreateObject("MSXML2.DOMDocument")
    Set oPI = oXMLDoc.createProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""")
    Set oRootDeklaracja = oXMLDoc.createNode(1, "Deklaracja", "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/")
    oXMLDoc.appendChild oRootDeklaracja
    oXMLDoc.DocumentElement.setAttribute "xmlns:kpkd", "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/02/01/eD/KodyPKD/"
    oXMLDoc.DocumentElement.setAttribute "xmlns:etd", "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/08/24/eD/DefinicjeTypy/"
    oXMLDoc.DocumentElement.setAttribute "xmlns:tns", "http://crd.gov.pl/wzor/2019/08/07/8334/"
    oXMLDoc.DocumentElement.setAttribute "xsi:schemaLocation", "http://crd.gov.pl/wzor/2019/08/07/8334/"
    oXMLDoc.DocumentElement.setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance"
    oXMLDoc.InsertBefore oPI, oXMLDoc.ChildNodes.Item(0)

oXMLDoc.Save "test.xml"

End Sub

The output of the above is (please also note that the "ns1:" prefix in front of "Deklaracja" element is missing):

<?xml version="1.0" encoding="utf-8"?>
<Deklaracja xmlns="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/" xmlns:kpkd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/02/01/eD/KodyPKD/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/08/24/eD/DefinicjeTypy/" xmlns:tns="http://crd.gov.pl/wzor/2019/08/07/8334/" xsi:schemaLocation="http://crd.gov.pl/wzor/2019/08/07/8334/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

The result I intend to achieve is as follows:

<?xml version="1.0" encoding="utf-8"?>
<tns:Deklaracja xmlns="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/" xmlns:kpkd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/02/01/eD/KodyPKD/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/08/24/eD/DefinicjeTypy/" xmlns:tns="http://crd.gov.pl/wzor/2019/08/07/8334/" xsi:schemaLocation="http://crd.gov.pl/wzor/2019/08/07/8334/ schema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

Cindy Meister · Accepted Answer · 2019-08-24 14:35:44Z

What you expect:

<tns:Deklaracja xmlns="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/" >

And how you declare the tns prefix:

oXMLDoc.DocumentElement.setAttribute "xmlns:tns", "http://crd.gov.pl/wzor/2019/08/07/8334/"

And how you create the root node:

Set oRootDeklaracja = oXMLDoc.createNode(1, "Deklaracja", "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/")

Don't match. These are two different name spaces.

In order to assign a prefix to an element name when creating it the language reference for createNode states

namespaceURI A string defining the namespace URI. If specified, the node is created in the context of the namespaceURI parameter with the prefix specified on the node name. If the name parameter does not have a prefix, this is treated as the default namespace.

So, change the create node line to something like this:

Set oRootDeklaracja = OXMLDoc.createNode( _
      1, "x:Deklaracja", _
      "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/")

Then the result of the code in the question becomes

<?xml version="1.0"?>
<x:Deklaracja xmlns:x="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/" xmlns:kpkd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/02/01/eD/KodyPKD/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/08/24/eD/DefinicjeTypy/" xmlns:tns="http://crd.gov.pl/wzor/2019/08/07/8334/" xsi:schemaLocation="http://crd.gov.pl/wzor/2019/08/07/8334/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

I leave it up to you to decide how to sort out the prefixes and the URIs...

Rubens Farias · Accepted Answer · 2019-08-24 15:01:01Z

2

You need to provide the correct namespace when creating your root element. Try this:

Set dict = New Scripting.Dictionary
    dict.Add "xmlns"     , "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2013/05/23/eD/KodyCECHKRAJOW/"
    dict.Add "xmlns:kpkd", "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/02/01/eD/KodyPKD/"
    dict.Add "xmlns:etd" , "http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2018/08/24/eD/DefinicjeTypy/"
    dict.Add "xmlns:tns" , "http://crd.gov.pl/wzor/2019/08/07/8334/"
    dict.Add "xsi:schemaLocation", "http://crd.gov.pl/wzor/2019/08/07/8334/schema.xsd"
    dict.Add "xmlns:xsi" , "http://www.w3.org/2001/XMLSchema-instance"

Set xmlDoc = CreateObject("MSXML2.DOMDocument")    
Set dek = xmlDoc.createNode(1, "tns:Deklaracja", dict("xmlns:tns"))
    dek.setAttribute "xmlns", dict("xmlns")
    dek.setAttribute "xmlns:kpkd", dict("xmlns:kpkd")
    dek.setAttribute "xmlns:etd", dict("xmlns:etd")
    dek.setAttribute "xsi:schemaLocation", dict("xsi:schemaLocation")
    dek.setAttribute "xmlns:xsi", dict("xmlns:xsi")

answered Aug 24, 2019 at 15:01

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

3 Comments

Thomas J. Over a year ago

Thank you! Out of curiosity (I am an absolute newbie), what is the advantage of using dictionaries instead of for example just an array in this case?

Rubens Farias Over a year ago

I understand you're going to create several elements to build your XML; in that case would be useful to have all namespaces in a easy to use structure, as a dictionary. Or maybe constants.

Thomas J. Over a year ago

Further question - it seems that .setatribute method is not supported. Is there a workaround for that?

Collectives™ on Stack Overflow

Setting multiple namespaces in an XML with VBA

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related