Retrieve parent - child relation from a XML file

Posted on 2014-08-12
Last Modified: 2014-08-13
Hi experts,

I've got this XML file that represents a nested parent-child relationship between folders.

I'd like to save these relationships in to my database but before I can save them I have to retrieve them. And I have little knowledge reading XML in VBS.

My table is simple it has these fields :

ID (record identifier)

I've got a XML structure like in the image beneath. As you can see I've simplified it caling the folders grandmother, parent1, child1,2:

The desired result I like to be able to collect in my table should be something like:


But the DB part is no problem and can be done by myself.
Question by:Steynsk
    LVL 35

    Accepted Solution

    Based on the script from your previous question http:/Q_28495636.html and assuming ID is automatically generated:
    Option Explicit
    Dim msg, xmlDOM, folder, folderItem, folderid, foldername, parentID
    msg = ""
    Set xmlDOM = CreateObject("MSXML2.DOMDocument")
    xmlDOM.LoadXml "<?xml version=""1.0"" encoding=""utf-8""?> <index> <exportdate>2014-01-01</exportdate> <folders> <folder> <id>384</id> <name>Grandmother</name> <documents/> <folders> <folder> <id>404</id> <name>Parent1</name> <documents/> <folders> <folder> <id>405</id> <name>Child1.1</name> <documents> <document> <id>1</id> <version>1.0</version> <title>Directiebeoordeling O&amp;G</title> <extension>.docx</extension> <summary/> <iconurl/> <viewerurl/> </document> <document> <id>2</id> <version>1.0</version> <title>doc2</title> <extension>.docx</extension> <summary/> <iconurl/> <viewerurl/> </document> <document> <id>3</id> <version>1.0</version> <title>doc3</title> <extension>.docx</extension> <summary/> <iconurl/> <viewerurl/> </document> </documents> <folders/> </folder> <folder> <id>407</id> <name>Child1.2</name> <documents> <document> <id>4</id> <version>1.0</version> <title>doc4</title> <extension>.docx</extension> <summary/> <iconurl/> <viewerurl/> </document> </documents> <folders/> </folder> <folder> <id>408</id> <name>Child1.3</name> <documents/> <folders/> </folder> </folders> </folder> <folder> <id>389</id> <name>Parent2</name> <documents/> <folders> <folder> <id>385</id> <name>Child2.1</name> <documents/> <folders/> </folder> </folders> </folder> </folders> </folder> </folders> </index>"
    Set folder = XMLDom.SelectNodes("//folders/folder")
    For Each folderItem In folder
    	folderid =folderItem.SelectSingleNode("id").text
    	If folderItem.parentNode.parentNode.nodeName = "folder" Then
    		parentID = folderItem.parentNode.parentNode.selectSingleNode("id").Text
    		parentID = 0 ' top level
    	End If
    	msg = msg & "INSERT INTO ... (parentID, folderID, folderName) VALUES (" & parentID & ", " & folderid & ", '" & Replace(foldername, "'", "''") & "')" & vbcrlf
    Set xmlDOM = Nothing
    msgbox msg

    Open in new window

    LVL 1

    Author Closing Comment

    Again Robert. Thank you very much for excellent your help.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now