vba code to parse xml file

looking for some vba coding to parse thru an xml file, need to extract : the following fields:Vendor FirmID and Name,UserID,lastname,firstname,email,along with the credits and credits detail information.
New to XML and lost as where to begin
CEM_PLI2.xml
Clipboard01.jpg
durickAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Fabrice LambertFabrice LambertCommented:
Hi,

Wich datas do you need from credits and creditDetails ?
I can offer a solution but you need to elaborate.
0
ste5anSenior DeveloperCommented:
You need to set a reference to Microsoft XML. E.g.

Option Compare Database
Option Explicit


Public Sub TestCountGetElementsByTagName()

  'Using reference to MSXML2.
  Dim document As MSXML2.DOMDocument60
  Dim credits As MSXML2.IXMLDOMNodeList
  Dim credit As MSXML2.IXMLDOMNode
  Dim vendors As MSXML2.IXMLDOMNodeList
  Dim vendor As MSXML2.IXMLDOMNode
  Dim user As MSXML2.IXMLDOMNode
  
  Dim XPath As String
   
  Set document = New MSXML2.DOMDocument60
  document.SetProperty "SelectionLanguage", "XPath"
  document.Load "C:\Temp\CEM_PLI2.xml"
  XPath = "//CEM/Vendor"
  Set vendors = document.documentElement.selectNodes(XPath)
  For Each vendor In vendors
    Debug.Print vendor.baseName
    Debug.Print vendor.Attributes.getNamedItem("Name").Text
    Debug.Print vendor.Attributes.getNamedItem("VendorFirmID").Text
    Set user = vendor.selectSingleNode("User")
    Debug.Print user.Attributes.getNamedItem("id").Text
    Debug.Print user.selectSingleNode("LastName").Text
    Set credits = user.selectNodes("Credits")
    For Each credit In credits
      Debug.Print Chr(9) & credit.Attributes.getNamedItem("TotalCredits").Text
      Debug.Print Chr(9) & credit.Attributes.getNamedItem("Item_SK").Text
    Next credit
  Next vendor
  
  Set credits = Nothing
  Set credit = Nothing
  Set user = Nothing
  Set vendor = Nothing
  Set vendors = Nothing
  Set document = Nothing
   
End Sub

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
Fabrice LambertFabrice LambertCommented:
@ste5an:
I wouldn't set a reference, as it is a major source of troubles, but use late binding instead.
Plus you're retrieving data from the 1st user only, and missing credit détails.

@durick:
You'll find an object oriented approach in the workbook attached below, see the test() procedure in the test module.
It isn't completed yet tho, as I'm still waiting for your answer about credit and credit details data.
EE.xlsm
0
ste5anSenior DeveloperCommented:
It was just a sample, not ment to be complete.. and while development I use always early binding. IntelliSense, you know ;)
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
XML

From novice to tech pro — start learning today.