Importing third party XML data into XML of MS Visio

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
VSD-for-XML-test-1.0.vsd

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.

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.

Kelvin

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?

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.

Kelvin

There are some XML-to-JSON converters you might consider:
http://code.google.com/p/xml2json-xslt/
http://www.thomasfrank.se/xml_to_json.html
http://www.bramstein.com/projects/xsltjson/
http://www.ibm.com/developerworks/xml/library/x-xml2jsonphp/

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

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.


Kelvin

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.

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.

Thanks for helping me present this question, but lets rest til after the Easter break.

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.

Scott

@Scott

Is a vdx file in an XML-compliant format?

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.

The required work could be as simple as:
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.

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,
Kelvin

@Kelvin

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?

@Aikimark

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

Kelvin

wouldn't it be easier to base the template on the vendor?  Or do you have vendors that send different XML files?

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?

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.

@Kelvin

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.

@aikimark
Thank you. I shall get this to you Monday pm European.

@aikimark
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..


KelvinItemisation-of-XML-and-screen-sh.docxVSD-for-XML-test-multipage-2.0.vsdXMLNotepad-from-VSD-test-multipa.xmlEE-XML-unedited-example-corrects.xml

@aikimark
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.

compliments,
Kelvin

@Kelvin

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.

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.

Cheers,
Kelvin

akimark and Kelvin -- in case you haven't seen it already, I thought you might be interested in this question:
https://www.experts-exchange.com/questions/27673952/Import-data-into-visio-file-from-XML.html
To respond to it I referenced akimark's code above but provided a couple of lines of VBA to update a Visio drawing directly.

Scott

@Scott

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.

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.

@Scott,

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.

Thanks... This is part of what I love about EE -- learning new stuff while observing other experts in action!

Both...
Thanks for the extra info!
Kelvin