Parse XML Using Wildcards

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Parse XML Using Wildcards

Logged in as: Guest

arrSession:exec spGetSession 2,2,34080

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Parse XML Using Wildcards
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1] 2 next > >>

Message

<< Older Topic Newer Topic >>

Parse XML Using Wildcards - 4/30/2006 2:38:27 PM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

Help! Some knowlege of vbscript, but new to xml.

Here is an example of the xml file I am trying to parse. I need to pull out all or most of the values of the childnodes under <ContentElement> when <IdNumber> is equal to *-ABC. I've looked through this forum and several other sites, tried different code, but I can't seem to put it all together. Heck, I'm not even sure if you can use wildcards in this case.
If someone could point me in the right direction or give me an example to get me on the right track, I would appreciate it.

<IPContents>
    <ContentElement>
         <IdNumber>001A1234-ABC</IdNumber>
         <InstanceIdentifier>001005200000008</InstanceIdentifier>
         <Type>INST</Type>
         <MFGAvail>R(1-9999)</MFGAvail>
         <LineApp>(4001,4005)</LineApp>
         <State>Approved</State>
    </ContentElement>
    <ContentElement>
         <IdNumber>002A5678-ABC</IdNumber>
         <InstanceIdentifier>001005200000008</InstanceIdentifier>
         <Type>INST</Type>
         <MFGAvail>R(1-9999)</MFGAvail>
         <LineApp>(4006,4007)</LineApp>
         <State>InWork</State>
    </ContentElement>
    <ContentElement>
         <IdNumber>002A1234-DEF</IdNumber>
         <InstanceIdentifier>001005200000008</InstanceIdentifier>
         <Type>INST</Type>
         <MFGAvail>R(1-9999)</MFGAvail>
         <LineApp>(4001,4005)</LineApp>
         <State>Approved</State>
    </ContentElement>
    <ContentElement>
         <IdNumber>002A5678-DEF</IdNumber>
         <InstanceIdentifier>001005200000008</InstanceIdentifier>
         <Type>INST</Type>
         <MFGAvail>R(1-9999)</MFGAvail>
         <LineApp>(4006,4007)</LineApp>
         <State>InWork</State>
     </ContentElement>
</IPContents>

Some of the code I've been working with just to see if I can return IdNumber *-ABC... Didn't work...

'Create an XML Document Object
Set objDoc = CreateObject("Microsoft.XMLDOM")
objDoc.async = False
'LoadXML for input
objDoc.Load "Test1.xml"
'Return a nodelist with all the records
Set objNodeList = objDoc.GetElementsByTagName("IPContents")
'Loop through the nodelist to get the fields needed
For Each objNode In objNodeList
WScript.Echo
objNode.SelectSingleNode("ContentElement/IdNumber").GetAttribute("*-ABC")
Next

Post #: 1

RE: Parse XML Using Wildcards - 5/1/2006 1:31:11 AM

ebgreen

Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline

I would recommend looking into an XPath query to accomplish this. You can find several XPath examples via google.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to who10)

Post #: 2

RE: Parse XML Using Wildcards - 5/1/2006 3:59:36 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

Thanks. I'll do some searching on XPath.

(in reply to ebgreen)

Post #: 3

RE: Parse XML Using Wildcards - 5/1/2006 3:19:44 PM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

Ok, did some searching and this is what I've come up with. when I run this code, I get no errors, but I get no results either... Any ideas as to what I'm doing wrong? At this point, I'm just trying to return the value for IdNumber where it is equal to *ABC.

Set oXMLDoc = CreateObject("MSXML2.DOMDocument")
oXMLDoc.async = False
If Not oXMLDoc.load("C:\Scripts\test1.xml") Then
Set oErr = oXMLDoc.parseError
sErrMsg = "XML Parsing Error. File: " & oErr.url & " Reason : " & oErr.reason &
" Line: " & oErr.line & ", Character: " & oErr.linepos & ", Text: " & oErr.srcText
Err.Raise 999, , sErrMsg
End If

Set oNodes = oXMLDoc.selectNodes("//ContentElement[@IdNumber='ABC']")
For i = 0 To oNodes.length - 1
wscript.echo "Test=" & oNodes(i).selectSingleNode("@IdNumber").Text & "<br>"
Next

(in reply to ebgreen)

Post #: 4

RE: Parse XML Using Wildcards - 5/2/2006 1:36:17 AM

ebgreen

Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline

Is this all of your code? I will try to poke around with it if I get a chance later today.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to who10)

Post #: 5

RE: Parse XML Using Wildcards - 5/2/2006 1:56:11 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

That's all so far. I'm very new to this, so I'm just trying to take it one step at a time to better understand it. There are a lot of code snippets out there, but it's kind of tough finding one site that brings it all together. Thanks

(in reply to ebgreen)

Post #: 6

RE: Parse XML Using Wildcards - 5/2/2006 10:25:39 PM 1 votes

ehvbs

Posts: 2204
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi who10,

a simple example doing something like you want to by using VBScript code:

If you prefer to use (more) XPath code, I'm afraid you'll have to spend some time browseing
the XML/XPath Docs.

(in reply to who10)

Post #: 7

RE: Parse XML Using Wildcards - 5/3/2006 1:59:40 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

Thanks for the example ehvbs. I'll play with this today to see if I can get it to work.

(in reply to ehvbs)

Post #: 8

RE: Parse XML Using Wildcards - 5/3/2006 2:15:23 AM 1 votes

ebgreen

Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline

ehvbs' solution is perfectly valid. If you want the XPath way, this is it. Notice that I output the XML of each node. I did this simply to show that it was getting the correct nodes. If you want to gather data from the nodes you should either do that in your XPath or you should iterate the child nodes of the nodes that you find with the XPath.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to ehvbs)

Post #: 9

RE: Parse XML Using Wildcards - 5/3/2006 2:27:50 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

ebgreen,

Thanks for the example I think you and ehvbs have given me enough info. to get going on this. I'll be working on it today and should reply later as to how things went.

Thanks again.

(in reply to ebgreen)

Post #: 10

RE: Parse XML Using Wildcards - 5/3/2006 2:52:03 AM

ehvbs

Posts: 2204
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi who10,

don't feel pressed! It's just that I like your problem/question (and ebgreens contribution).
Therefore I'd like to add two variations on the XPath query:

Set objNodeList = objDoc.selectNodes( "//ContentElement[contains(IdNumber, ""ABC"")]" )
(obviously stolen from ebgreen)

Set objNodeList = objDoc.selectNodes( "//ContentElement[starts-with(IdNumber, ""001"")]" )

Set objNodeList = objDoc.selectNodes( "//ContentElement[""ABC"" = substring-after( IdNumber, ""-"" )]" )

and ask: As there is no "ends-with" function - how can I do the equivalence of VBcript's Right()?

(in reply to who10)

Post #: 11

RE: Parse XML Using Wildcards - 5/3/2006 2:57:59 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

I've got IE 6.0 installed. Do I need anything else installed in order to run these scripts?

I noticed when I ran ehvbs's example, it ran with no errors, but returned no results.

When I ran your example, it ran with no erorrs, but returned the string //ContentElement[contains(IdNumber, ""ABC"")]
as if it didn't know how to processes the DOM objects.

(in reply to ebgreen)

Post #: 12

RE: Parse XML Using Wildcards - 5/3/2006 3:10:19 AM 1 votes

ehvbs

Posts: 2204
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi who10,

I kind of started with your sample code, assuming this would avoid problems. I see
two possible difficulties:

(1) did you adjust for my sneakish renaming of the xml file?
(2) what happens if you replace

       "Msxml2.DOMDocument.4.0"

     with plain

       "Msxml2.DOMDocument"?

I *thought* that modern Windows comes with msxml*.dll; but you could make
sure by asking for "Msxml2.DOMDocument.i.0" with i = 4, 5, 6, 7 (this should
give errors like "can't create object". If "4" fails, consider an upgrade, because
older versions could be challenged by XPath magic.

(in reply to who10)

Post #: 13

RE: Parse XML Using Wildcards - 5/3/2006 3:21:21 AM

ebgreen

Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline

quote:

s there is no "ends-with" function - how can I do the equivalence of VBcript's Right()?

Not tested, but:

Set objNodeList = objDoc.selectNodes( "//ContentElement[""ABC"" = substring( IdNumber, 9)]" )

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to ehvbs)

Post #: 14

RE: Parse XML Using Wildcards - 5/3/2006 3:23:20 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

ehvbs,

Yes, I did catch the renaming of the xml file and made the adjustment.
I am able to run some code using Msxml2.DOMDocument and I tried that with
yours and got the issue with no results. I suspect the next step as you've stated is to
upgrade to 4.0.

Thanks,
who10

(in reply to ehvbs)

Post #: 15

RE: Parse XML Using Wildcards - 5/3/2006 3:31:56 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

Well, I did a search my system for msxml and found the msxml4.dll and msxml5.dll.
Now I'm wondering if it's not a different problem.

(in reply to ehvbs)

Post #: 16

RE: Parse XML Using Wildcards - 5/3/2006 3:37:06 AM

ehvbs

Posts: 2204
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi who10,

my first sample code 'WScripts.Echos' results, even if I create the object with
     Set objDoc = CreateObject( "Msxml2.DOMDocument.3.0" )
so I'd would double check before I'd start to upgrade. You are aware of

    Microsoft Core XML Services (MSXML) 6.0
    (http://www.microsoft.com/downloads/details.aspx?familyid=993C0BCF-3BCF-4009-BE21-27E85E1857B1&displaylang=en)

(not sure if that's relevant for you)

Hi ebgreen:

I tried

   Set objNodeList = objDoc.selectNodes( "//ContentElement[""ABC"" = substring( IdNumber, 9)]" )

but got better results with

   Set objNodeList = objDoc.selectNodes( "//ContentElement[""ABC"" = substring( IdNumber, 10)]" )

From the XML SDK Docs:

        Each character in the string is considered to have a numeric position: the position of the first character
        is 1, the position of the second character is 2, and so on.

Just when I thought, counting from 1 went away with BASIC, IBM OS/2 and the dinos.

(in reply to who10)

Post #: 17

RE: Parse XML Using Wildcards - 5/3/2006 3:49:09 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

I'll double check everything before I upgrade and yes, I ran across the 6.0 info as I was looking this morning.

Thanks again for the help,
who10

(in reply to ehvbs)

Post #: 18

RE: Parse XML Using Wildcards - 5/3/2006 1:36:53 PM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

ehvbs & ebgreen,

Your examples worked Great on my home PC after upgrading to msxml 4.0.
Earlier problems were probably due to rights on the pc I was using at work,
or the fact that it may not have the correct version of msxml installed.

No doubt you've saved me from hours of frustration and enhanced my knowlege.

I really appreciate your help!

(in reply to who10)

Post #: 19

RE: Parse XML Using Wildcards - 5/4/2006 9:06:28 AM

who10

Posts: 33
Score: 0
Joined: 4/29/2006
Status: offline

Ok, one more question... Say I have 3 xml files just like the ones I described before in a folder and I want to loop through the files to get the results based on the example you provided.
I made an attempt to do this, but I got errors saying Invalid procedure call or argument: 'Load'. I suspect I'm loading the document the wrong way or there's a problem with my For each statement when I start the loop. Can you help?

Here's my code

Option Explicit
'***Define Variables***
Dim ipdlFolder, ipdlFiles, file
Dim objDoc, objNodeList, objNode, objSubNode
Dim objFSO, strFoundIrm, strIrmData, objOutputFile

'***Set Objects***
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Create an XML Document Object
Set objDoc = CreateObject( "Msxml2.DOMDocument.4.0" )
objDoc.async = False

'***Loop through files in folder***
Set ipdlFolder = objFSO.GetFolder("C:\Temp\TestData\")
Set ipdlFiles = ipdlFolder.files
For each file in ipdlFiles
If "Test" = left(file.name, 4) Then
'LoadXML for input
   objDoc.Load file

'Return a nodelist with all the ContentElement 'records'
   Set objNodeList = objDoc.GetElementsByTagName("ContentElement")

'Loop through the nodelist to get the fields needed
   For Each objNode In objNodeList
     If "-ABC" = Right( objNode.firstChild.Text, 4 ) Then
     wscript.echo "found   " & objNode.firstChild.Text
       For Each objSubNode In objNode.childNodes
         wscript.echo " " + objSubNode.tagName + ": " + objSubNode.text
       Next
     End If
   Next
End If
Next

set objDoc = nothing
set objNodeList = nothing
set objFSO = nothing
WScript.Quit()

(in reply to ehvbs)