Importing third party XML data into XML of MS Visio

I type text into shapes in Visio drawings derived from Templates. Now I want to add automation,  by importing text automatically from various third party XMLs. Can I and ought I import the data fields of the 3rd party into MSOffice XML derived from my drawing template? I use Visio 2010 Premium.

I attach a file of third party XML (EE XML unedited example correctsppp.xml),
FROM which I would like to export the string:
"ChoiceA" in data field: assessment item/responseDeclaration/correctResponse/value
and copy the string INTO:
Visio XML file: XML from VSD test 1.0.vdx to REPLACE the string:
"This is the data field I want to access" in the data field: VisioDocument/Pages/Page/Shapes/Shape/Text/#text

I wish to make a  shell in vba to automate the import process, so that a series of third party XML specifications of the same structure but different content can be imported into different instances of the same Visio xml template.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin4Author Commented:
Yes, I have attached it. It only has one shape, as a model of more complex diagram.

Actually, if the .XML file is opened in Visio, it is automatically displayed a drawing. I think that means that the process of 'Importing' a modified Visio.XML into Visio is redundant, provided the .XML is well formed.

Thanks, Kelvin
There are two approaches to this problem.
1. Use VBA automation within Visio to change the properties of the Visio objects, based on data in some XML file.

2. Update the data directly, from vendor XML file into the exported Viso XML file.
Kelvin4Author Commented:
Yes. I favour choice 2, for a reason I'd not mentioned.

The Visio file's purpose is to turn content data (expressed in the shapes and their texts), into JSON ASCII files which constitute the data for a pre-existing data-driven web based application.

Visio is just an essential tool for allowing a human author to enter new human-readable data into a system that converts it into DB-readable ASCII code.Thus, when some of the 'human readable' data is already extant in 3rd party XML resources, we can by-pass the human authoring step, automate its entry into Visio XML, and produce the JSON automatically.

Thus I support your Choice #2. Do I recall correctly, that Excel can manage data exchange between XMLs? That would seem ideal to build a seemless application for importing XML data, yet keeping the underlying processes as modular as possible.

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Wait.  You have left significant steps out of your problem description.  

* Do you want to produce JSON as the end result?

* Do you even need a human (GUI) interface?
Kelvin4Author Commented:
Thanks for these key questions. Sometimes I burden the expert with too much info; this time I strayed the other way.

* Do you want to produce JSON as the end result?  -- Yes

* Do you even need a human (GUI) interface? -- Important point; it depends..

.. each item of content (typed in by human author, or 'lifted' from 3rd party XML) will differ in size, and this can affect the LAYOUT of content rendered on the web app. Where possible, this is taken into account in Visio, so the human authoring input IS bypassed as you suggest. However, in other cases, the effect of content size, and the benefits of adding extra Visio-based authoring procedures - means that human editing will be essential.

xml is seen as a very desirable extension component that allows the re-utilisation of some simple third party content, and save the need to typing it a second time.

There are some XML-to-JSON converters you might consider:

I'm sure there must be a really good way to do the XML updates.  I was thinking about XSLT earlier today.  However, there might be a better way that treats both XML files as data and does some sort of joined table update.
Kelvin4Author Commented:
Thank you, for information on converters of XML to JSON.  As you say, however, this is an issue of updating a minority of visio XML data, from a small third party XML.

Hopefully,  members of the XML zone will also help us.

While we're waiting for some XML experts, please tell me more about the field mapping.  It sounds like there is some variability involved.  If not, that (static relationships) might greatly simplify the updating.
Kelvin4Author Commented:
Yes, I agree. Whilst there is huge scope for variation in fields between instances of the visio file, there will be much less in the third party file, and easy to identify. For example in EE-XML-unedited-example-corrects.xml (supplied) :
assessmentItem/itemBody/choiceInteraction/   contains four 'simpleChoice' fields...

.. this number could be more of less, typically 3-7

Thus it will be a case of making an instance of visio xml appropriate to the general style of third party XML. Then, as you point out, the data transfer protocol will need to detect and respond the limited variation in the third party xml .
Or, you create some sort of mapping between various vendor XML files and your standard Visio XML file.
Kelvin4Author Commented:
Thanks for helping me present this question, but lets rest til after the Easter break.
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I can't contribute much on the XML side but can confirm that a Visio drawing saved as vdx is a full fidelity version of the drawing. If you read strings from your XML source and replacing strings in the vdx file -- and do so without damaging anything, of course -- Visio will open the vdx file directly and then render the diagram.


Is a vdx file in an XML-compliant format?
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Is a vdx file in an XML-compliant format?
Depends what you mean by XML-compliant  ;-)>

Is it properly formatted XML? Yes.  But it is Microsoft-invented XML.
Well, I was thinking that there might be a way to join these two XML files and perform an update using a data engine.
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
The required work could be as simple as:
Locate string in source XML
Read string from source XML
Find target location in Visio XML
Write string to Visio XML
I'm not exactly sure what you mean by a data engine, but if you can perform those steps it sounds like it would work.
I agree that some VBA or VBScript code might be able to do these edits using MSXML DOM object.

What I was thinking about was hooking these XML files to an Access database and joining them, perhaps with some mapping table data.  Then I could run an update query.

I just checked and you can instantiate multi-level recordsets on XML.  Unfortunately, Access2007 treats these sets of elements as distinct tables and only seems able to do an import, not a link.  :-(

I also expect that XSL will provide a solution path.  I'm looking at merge examples.
Kelvin4Author Commented:
Hi Aikimark,
I appreciated you keeping this thread open, and read your additional comments with interest.

This problem is important to me, but the exact date of a solution is currently not critical.
I attend on your thoughts.

Best regards,

Is your Visio file used as a template?  That is, are the set of strings to be replaced the same set each time the substitution process runs or does the user's edits (or prior replacement runs) establish the set of Visio strings seen by the replacement process?

Given the two XML files you posted (Visio export & vendor file), what would be the changes that should be applied to the Visio file?
Kelvin4Author Commented:

Thanks, the answer is YES.
Firstly, in third party XML: 'EE XML unedited example correctsppp.xml' the only variation we need consider is variation in the number of data fields is within the XML 'CHOICE INTERACTION' node. The example has Choices A,B,C,D. In other examples, there could be VARIANTS of the format, with more or less 'Choice' fields, eg: A-L or A-C.

I expect to make one Visio template for each third party variant. Ideally, I would like to count the number of choices in each third party XML, and select the corresponding Visio template.

I guess there are more or less simple ways of matching the visio template to the third party XML. From the visio-standpoint it would be easy to have a template with an excessive number of choice fields, and then simply ignore the empty fields, in downstream processing: for example: if Choice 15 in the Visio template has no content:
if Choice # 15 = "" then Do Nothing...
ie: the content is made up into an object that gives no sign that Choice#15 was 'absent' from the third party XML.

I dont know if that type of approach can be accommodated at the level of XML data engine, which would have to tolerate NOT having third party 'Choice' fields to meet the excess of fields in the visio template.

A simply first step that uses technology with scope for development might let me learn this stepwise?

Hope that helps

wouldn't it be easier to base the template on the vendor?  Or do you have vendors that send different XML files?
Kelvin4Author Commented:
The vendors do have different types of XML files, but not a huge range of types.

The vendor's XML files support much less complex user-experience than Visio.  Their XML files contain perhaps 5% of the data fields that are actively required by the Visio application. Most of the additional 95% of fields are generated automatically in Visio to provide enhanced user-experience.

The only reason for importing vendor XML content is to save the Visio user from re-typing Vendor textural content into Visio de novo. Transferring Vendor content to Visio enables that content be rendered as an enhanced user-experience.

In Visio-terms, the vendor content is Legacy content.
Is it the number of fields to match or the tags of these fields or both?
Kelvin4Author Commented:
Thanks, this develops post 2012-04-12 at 19:36:44.
Firstly, I have very little XML experience, so my replies should be taken at face value. If I appear to assume something, its more likely that I dont know it.

Returning to Choices A,B,C,D of that post, there is a data structure in Visio XML that will take these choices, but will be not be written the same way as the tags that define the corresponding fields in Vendor XML. So matching tag specifications between Vendor and Visio does not seem possible

So the required Vendor fields are identified by their tags, and counted. The Visio template for that field count is selected, and the content of the vendor fields is copied between the tags of the corresponding Visio fields.

I think we should ignore any variables that are defined within the tag structure itself. Simply go for the lump of text: 'Choice A', 'Choice B' etc.

There is a mapping process involved that relates source tags with target tags.  In this case, this will a manual process.  Using your posted vendor XML file and Visio XML file, please post the source tag and target tag combinations.
Kelvin4Author Commented:
Thank you. I shall get this to you Monday pm European.
Kelvin4Author Commented:
Sorry to be a day late with my details.

I think the attached word document covers the details you asked for, and gives screen shots of the Visio drawings I'm using in this thread. The doc explains that I have slightly expanded the Visio XML details, to make clear how Visio files consisting of more than one page are handled in XML. No real surprises, but it may be useful to have the details.

Visio shapes that should gain data from third party XML. I've given all details of the XML tag environment for one shape (ShapeID1), and then I cut right down to the flanking tags for the other shapes that can receive 3rd party XML data.  IDs 1-4 are each mapped to a respective "Choice letter" in the 3rd Party (vendor) XML.  Notes on Visio Shapes ID 4-6: I summarise here, and in Word doc..
-ID4 contains some unnecessary data within the third party tag, which I'd like to ignore
-ID5 would be used for XML data import, were there a fifth Choice E in the Third Party data,
-ID6 is an irrelevant shape (which needs to import NO data from XML). It represents a common class of shape that have other functions.

If you find that I'm over-complicating, making things opaque, etc - do say so..

This seems to work, but the text nodes in the Visio XML file look a little different when viewed with XML Notepad.

Option Explicit

Public Sub ParseVendorFile(parmVendorDataFile, parmVisioDataFile)
    Dim xmlDoc As Object    'New MSXML2.DOMDocument
    Dim xmlNode As Object
    Dim xmlNodeList As Object   'IXMLDOMNodeList
    Dim strKey As String
    Dim dicSourceData As Object
    Set dicSourceData = CreateObject("scripting.dictionary")
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.Load parmVendorDataFile
    For Each xmlNode In xmlDoc.selectSingleNode("assessmentItem/itemBody/choiceInteraction").childNodes
        If xmlNode.nodeName = "simpleChoice" Then
            dicSourceData.Add xmlNode.Attributes(0).Value, xmlNode.Text
        End If
    xmlDoc.Load parmVisioDataFile
    For Each xmlNode In xmlDoc.selectSingleNode("VisioDocument/Pages/Page/Shapes").childNodes
        If xmlNode.nodeName = "Shape" Then
            If xmlNode.Attributes.Length = 5 Then
                strKey = "Choice" & Mid("ABCDEFGH", xmlNode.Attributes(0).Value, 1)
                If dicSourceData.exists(strKey) Then
                    Set xmlNodeList = xmlNode.getElementsByTagName("Text")
                    xmlNodeList(0).Text = dicSourceData(strKey)
                End If
            End If
        End If
    xmlDoc.Save parmVisioDataFile
End Sub

Open in new window

You invoke the routine, passing then names of the vendor and Visio XML files.
ParseVendorFile "C:\Users\AikiMark\Downloads\EE-XML-unedited-example-corrects.xml", "C:\Users\AikiMark\Downloads\XMLNotepad-from-VSD-test-multipa.xml"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin4Author Commented:
Many thanks.

Your vba worked first time for me, and I confirm the import of vendor text into Visio XML.

Via XML Notepad, I added two more "Choice" nodes to the Vendor XML. Your vba procedure transferred the text of these nodes to Visio XML.

I'm very pleased. This will give me a great start.

Before we sign off, can I ask about your comment: "This seems to work, but the text nodes in the Visio XML file look a little different when viewed with XML Notepad."

Could you specify, please, as I'm not seeing this difference. Also, do you consider the difference to be potentially significant, down the development line?

I hope for your response, but do consider that your code (above) fully meets the terms of my question.


If you open (with XML Notepad) a copy of the Visio XML file  and then the Visio XML file after you transfer the text, you will see a slight difference in Text node.  If you don't see any difference when you open/import the updated Visio XML file in Visio, then this difference doesn't matter.  I purposefully added more letters, facilitating larger questions.
Kelvin4Author Commented:
This has been an excellent experience. The solution provides me with the functionality I requested, in an accessible way that gets me started with XML-Visio.

Tribute to 'aikimark', who took the trouble to qualify my requirements, and kept the question open to work up a very good solution.   Also thanks to EE Administration - for input.

Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
akimark and Kelvin -- in case you haven't seen it already, I thought you might be interested in this question:
To respond to it I referenced akimark's code above but provided a couple of lines of VBA to update a Visio drawing directly.


Thanks for the link.  I don't have Visio, so that solution path wasn't available for me to develop and test.  I thought about using RegExp to parse and update the data, but chose XMLDom/XPath instead.
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
akimark -- Your solution is entirely appropriate for what Kelvin asked about. I plan to file the code away for future use because I've never tried to parse XML in VBA before and wouldn't have know how to start.

You might want to look at XSLT examples, since they will show you a greater variety of XPath as well as XQuery language features (properties and methods) and behaviors.  There has been some progress in XML handling by the large database vendors, especially IBM.

There are some good EE articles on XML and XML with databases you might want to check out.

The solution in this example became do-able once the field mapping was revealed.
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Thanks... This is part of what I love about EE -- learning new stuff while observing other experts in action!
Kelvin4Author Commented:
Thanks for the extra info!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Visio

From novice to tech pro — start learning today.