Solved

Importing third party XML data into XML of MS Visio

Posted on 2012-04-04
41
1,027 Views
Last Modified: 2012-04-23
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.

Thanks

Kelvin4
EE-XML-unedited-example-corrects.xml
XML-from-VSD.xml
0
Comment
Question by:Kelvin4
  • 16
  • 15
  • 6
41 Comments
 

Author Comment

by:Kelvin4
ID: 37805481
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37805487
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.
0
 

Author Comment

by:Kelvin4
ID: 37805544
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37806088
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?
0
 

Author Comment

by:Kelvin4
ID: 37806353
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37806556
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.
0
 

Author Comment

by:Kelvin4
ID: 37808192
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37808474
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.
0
 

Author Comment

by:Kelvin4
ID: 37809559
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 .
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37810657
Or, you create some sort of mapping between various vendor XML files and your standard Visio XML file.
0
 

Author Comment

by:Kelvin4
ID: 37811619
Thanks for helping me present this question, but lets rest til after the Easter break.
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 37813949
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37813969
@Scott

Is a vdx file in an XML-compliant format?
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 37814018
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37814042
Well, I was thinking that there might be a way to join these two XML files and perform an update using a data engine.
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 37814192
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
Repeat
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37814475
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.
0
 

Author Comment

by:Kelvin4
ID: 37831250
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Expert Comment

by:aikimark
ID: 37832274
@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?
0
 

Author Comment

by:Kelvin4
ID: 37839352
@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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37840118
wouldn't it be easier to base the template on the vendor?  Or do you have vendors that send different XML files?
0
 

Author Comment

by:Kelvin4
ID: 37841201
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37841245
Is it the number of fields to match or the tags of these fields or both?
0
 

Author Comment

by:Kelvin4
ID: 37841297
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37842301
@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.
0
 

Author Comment

by:Kelvin4
ID: 37842371
@aikimark
Thank you. I shall get this to you Monday pm European.
0
 

Author Comment

by:Kelvin4
ID: 37855458
@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
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 37878451
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
    Next
    
    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
    Next
    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

0
 

Author Comment

by:Kelvin4
ID: 37880357
@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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37880738
@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.
0
 

Author Closing Comment

by:Kelvin4
ID: 37880798
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
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 37880803
akimark and Kelvin -- in case you haven't seen it already, I thought you might be interested in this question:
http://www.experts-exchange.com/Microsoft/Applications/Microsoft_Visio/Q_27673952.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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37880912
@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.
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 37880960
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37881010
@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.
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 37881120
Thanks... This is part of what I love about EE -- learning new stuff while observing other experts in action!
0
 

Author Comment

by:Kelvin4
ID: 37882177
Both...
Thanks for the extra info!
Kelvin
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now