retrieve recursive information from xml file with .vbs script

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.

xml.JPG
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")
waarden=""
i=0
Set db = CreateObject("ADODB.Connection")
CS = "Provider=SQLOLEDB;Data Source=mySource;Initial Catalog=myCatalog; User ID=myuser; Password=mypassword;"
db.open CS

For Each folderItem In folder
   folderid =folderItem.SelectSingleNode("id").text
   foldername=folderItem.SelectSingleNode("name").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.AddNew
			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.Open
			mstream.LoadFromFile viewerurl
			RecordSet.Fields("bestand").Value = mstream.Read
			RecordSet.Update
			Set mstream = Nothing
			i=i+1
  		next
Next
i = 0
Set rs = Nothing 
Set xmlDOM = Nothing

Open in new window

LVL 1
SteynskAsked:
Who is Participating?
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.

Robert SchuttSoftware EngineerCommented:
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
   foldername=folderItem.SelectSingleNode("name").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

  		next

Next
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

0

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
SteynskAuthor Commented:
Hi Robert, You've helped me out BIG time.

Reuze bedankt.
0
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
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.