retrieve recursive information from xml file with .vbs script

Posted on 2014-08-12
Last Modified: 2014-08-12
Hi Experts,

I've got this script that does its job excellent in a situation where my XML has one parent child level.
But the XML (created by a program) sometimes creates a structure that nests multiple structures and this makes that my script does no longer work.

The structure has nodes called "folders" that contain one or multiple nodes "folder" but every folder can contain (optional) child nodes called "folders" and so on.

So now I need to take it to a next level where it can retrieve the information recursive from the XML. And the main goal is to retrieve the information from the "document" nodes in combination of the parent folder ID and folder name

But how can I make this script recursive?

Set xmlDOM = CreateObject("MSXML2.DOMDocument")
xmlDOM.async = False
xmlDOM.Load "D:\OG\index.xml"
Set folder = XMLDom.SelectNodes("index/folders/folder")
Set db = CreateObject("ADODB.Connection")
CS = "Provider=SQLOLEDB;Data Source=mySource;Initial Catalog=myCatalog; User ID=myuser; Password=mypassword;" CS

For Each folderItem In folder
   folderid =folderItem.SelectSingleNode("id").text
   		for each documentItem in folderItem.SelectNodes("documents/document")
			docid = documentItem.SelectSingleNode("id").text
			version = documentItem.SelectSingleNode("version").text
			title = documentItem.SelectSingleNode("title").text
			extension = documentItem.SelectSingleNode("extension").text
			summary = documentItem.SelectSingleNode("summary").text
			viewerurl = documentItem.SelectSingleNode("viewerurl").text
			Sql = "select * FROM externedocumenten"
			Set RecordSet = CreateObject("ADODB.Recordset")
			RecordSet.Open Sql, CS, 3, 3
			RecordSet.Fields("categorieID").Value = folderid
			RecordSet.Fields("categorienaam").Value = replace(foldername,"'", "")
			RecordSet.Fields("id").Value = docID
			RecordSet.Fields("versie").Value = version
			RecordSet.Fields("titel").Value = replace(title,"'", "")
			RecordSet.Fields("extentie").Value = "application/" & extension
			RecordSet.Fields("samenvatting").Value = replace(summary,"'", "")
			RecordSet.Fields("URL").Value = viewerurl
			Set mstream = CreateObject("ADODB.Stream")
			mstream.Type = 1
			mstream.LoadFromFile viewerurl
			RecordSet.Fields("bestand").Value = mstream.Read
			Set mstream = Nothing
i = 0
Set rs = Nothing 
Set xmlDOM = Nothing

Open in new window

Question by:Steynsk
    LVL 35

    Accepted Solution

    Should be as easy as changing line 4 to:
    Set folder = XMLDom.SelectNodes("//folders/folder")

    Open in new window

    Tested with this script:
    Option Explicit
    Dim msg, xmlDOM, folder, folderItem, folderid, foldername, documentItem, docid, title
    msg = ""
    Set xmlDOM = CreateObject("MSXML2.DOMDocument")
    xmlDOM.LoadXml "<?xml version=""1.0"" encoding=""utf-8""?> <index> <exportdate>2014-01-01</exportdate> <folders> <folder> <id>1</id> <name>O&amp;G</name> <documents/> <folders> <folder> <id>2</id> <name>Beleid</name> <documents/> <folders> <folder> <id>3</id> <name>Algemeen</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>4</id> <name>x</name> <documents> <document> <id>4</id> <version>1.0</version> <title>doc4</title> <extension>.docx</extension> <summary/> <iconurl/> <viewerurl/> </document> </documents> <folders/> </folder> </folders> </folder> <folder> <id>5</id> <name>y</name> <documents/> <folders/> </folder> <folder> <id>6</id> <name>z</name> <documents/> <folders/> </folder> </folders> </folder> </folders> </index>"
    Set folder = XMLDom.SelectNodes("//folders/folder")
    For Each folderItem In folder
       folderid =folderItem.SelectSingleNode("id").text
    		msg = msg & "FLD " & folderid & " - " & foldername & vbcrlf
       		for each documentItem in folderItem.SelectNodes("documents/document")
    			docid = documentItem.SelectSingleNode("id").text
    			title = documentItem.SelectSingleNode("title").text
    			msg = msg & "DOC " & docid & " - " & title & vbcrlf
    Set xmlDOM = Nothing
    msgbox msg

    Open in new window

    this produces the following output:
    FLD 1 - O&G
    FLD 2 - Beleid
    FLD 3 - Algemeen
    DOC 1 - Directiebeoordeling O&G
    DOC 2 - doc2
    DOC 3 - doc3
    FLD 4 - x
    DOC 4 - doc4
    FLD 5 - y
    FLD 6 - z

    Open in new window

    LVL 1

    Author Closing Comment

    Hi Robert, You've helped me out BIG time.

    Reuze bedankt.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    759 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

    14 Experts available now in Live!

    Get 1:1 Help Now