?
Solved

VBA - XML selectNode and/or selectSingleNode help needed.

Posted on 2006-11-20
7
Medium Priority
?
2,009 Views
Last Modified: 2008-01-09
I have a similar questions posted, but I need to clarify and start again.

Basically I have data in XML format as a DOM Document in memory in my VBA program.

  Dim xmlResponseDoc As New DOMDocument40
  Dim xmlJobResponse As IXMLDOMNode
  Dim xmlJobQueryNode As IXMLDOMNode
  Dim xmlFilterNode As IXMLDOMNode
  Dim xmlNodeList As IXMLDOMNodeList



Here's the XML data in memory as xmlJobResponse:


<JBXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<JBXMLRespond>
<JobQueryRs ErrorCode="0" Message="">
<ID>SDK</ID>
<LastUpdated>2006-11-17T09:12:00</LastUpdated>
<BuildtoStock>false</BuildtoStock>
<TimeandMaterial>false</TimeandMaterial>
<Status>Active</Status>
<Priority>5</Priority>
....
....
.... above is "header" information for a single Job.  now is where the question is.  A job can have many <RoutingLine> sections similar to below.
....
<RoutingLine>
<ID>1824</ID>
<Priority>5</Priority>
<Sequence>0</Sequence>
<Status>O</Status>
<WorkCenteRef ID="90 PRESS"/>
</RoutingLine>
<RoutingLine>
<ID>1825</ID>
<Priority>5</Priority>
<Sequence>0</Sequence>
<Status>O</Status>
<WorkCenteRef ID="Lathe"/>
</RoutingLine>
</JobQueryRs>
</JBXMLRespond>
</JBXML>


I need to be able to select into memory the RoutingLine.ID(1824 or 1825) based off of the WorkCenterRef ID attribute!

So basically I need to query all routing line sections in this XML Document in memory and select by WorkCenterRef ID ="specified value" and then store the ID from that RoutingLine section.

example:
   
          search for WorkCenterRef ID="Lathe" and return the ID value of 1825

I Know how to selectSingleNode, but I don't know how to return a NodeList or section of an XML document and then access node values in that "section" for my one to many scenario.


THANKS!

JMO9966





0
Comment
Question by:JMO9966
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:EDDYKT
ID: 17981593
Private Sub Command1_Click()
Dim xmlApps As IXMLDOMElement
Dim xmlDoc As New DOMDocument40

xmlDoc.Load ("yourxmlfile")   ' use loadxml if you pass as xml
For Each xmlchild In xmlDoc.selectSingleNode("//WorkCenteRef [@ID='Lathe']").parentNode.childNodes
    If (xmlchild.baseName = "ID") Then Debug.Print xmlchild.Text
Next
End Sub
0
 

Author Comment

by:JMO9966
ID: 17982671
Thanks EDDYKT,

Okay, I tried calling your subroutine wiith (Call Op) after my ResponseDoc has been built in memory, but I get an run-time 91 error - Object Variable or With Block variable not set when I call the For Each loop in your subroutine.

Here's my code, maybe this will help.

Public Sub Load(jb As JBRequestProcessor, sessionID As String)
'''''''''''''''''''''''''''''''''''''
' Retrieve a job from JobBOSS.      '
' jb is an active JBXML session.    '
' sessionID is the session # of jb. '
' Populate Job properties.          '
'''''''''''''''''''''''''''''''''''''
  Dim xmlRequestdoc As DOMDocument40
  Dim xmlResponseDoc As New DOMDocument40
  Dim xmlJobResponse As IXMLDOMNode
  Dim xmlJobQueryNode As IXMLDOMNode
  Dim xmlFilterNode As IXMLDOMNode
  Dim xmlNodeList As IXMLDOMNodeList
  Dim response As String


  Set xmlRequestdoc = newJBXMLRequest(sessionID)
 
  Set xmlJobQueryNode = createJBNode(xmlRequestdoc, "JobQueryRq")
    Set xmlFilterNode = createJBNode(xmlRequestdoc, "JobQueryFilter")
    xmlJobQueryNode.appendChild xmlFilterNode
      xmlFilterNode.appendChild createJBNode(xmlRequestdoc, "ID", ID)
      xmlFilterNode.appendChild createJBNode(xmlRequestdoc, "IncludeAdditionalCharges", "1")
      xmlFilterNode.appendChild createJBNode(xmlRequestdoc, "IncludeDeliveries", "1")
      xmlFilterNode.appendChild createJBNode(xmlRequestdoc, "IncludeRoutingLines", "1")
      xmlFilterNode.appendChild createJBNode(xmlRequestdoc, "IncludeMaterialRequirements", "1")
      xmlFilterNode.appendChild createJBNode(xmlRequestdoc, "IncludeComponents", "1")
      xmlRequestdoc.selectSingleNode("JBXML/JBXMLRequest").appendChild xmlJobQueryNode
    Set xmlFilterNode = Nothing
  Set xmlJobQueryNode = Nothing
 
  response = jb.ProcessRequest(xmlRequestdoc.XML)
  Set xmlRequestdoc = Nothing
 
 
  xmlResponseDoc.loadXML response
    Call Op                                            ******************************************here's where I call your subroutine since my xmlResponseDoc is in memory now
   
  Set xmlJobResponse = xmlResponseDoc.selectSingleNode("JBXML/JBXMLRespond/JobQueryRs")
  Set xmlResponseDoc = Nothing

these two "selects" work but they are simple single nodes
  ID = xmlJobResponse.selectSingleNode("ID").Text
  LastUpdated = xmlJobResponse.selectSingleNode("LastUpdated").Text


Private Sub Op()
Dim xmlApps As IXMLDOMElement
Dim xmlDoc As New DOMDocument40

'xmlDoc.Load ("xmlResponseDoc")   ' use loadxml if you pass as xml
xmlDoc.loadXML ("xmlResponsedoc")

For Each xmlchild In xmlDoc.selectSingleNode("//WorkCenteRef [@ID='Lathe']").parentNode.childNodes        *************dies on this line
    If (xmlchild.baseName = "ID") Then Debug.Print xmlchild.Text
Next
End Sub



Thanks!
0
 

Author Comment

by:JMO9966
ID: 17985014
Increasing points!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:EDDYKT
ID: 17986325
what is your xml looks like?

i use this to test


<JBXML>
<JBXMLRespond>
<JobQueryRs>
<RoutingLine>
<ID>1824</ID>
<Priority>5</Priority>
<Sequence>0</Sequence>
<Status>O</Status>
<WorkCenteRef ID="90 PRESS"/>
</RoutingLine>
<RoutingLine>
<ID>1825</ID>
<Priority>5</Priority>
<Sequence>0</Sequence>
<Status>O</Status>
<WorkCenteRef ID="Lathe"/>
</RoutingLine>
</JobQueryRs>
</JBXMLRespond>
</JBXML>
0
 

Author Comment

by:JMO9966
ID: 17986722
Here's the whole document, this document is in memory and not a physical file while my program runs.  Thanks!


<JBXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<JBXMLRespond>
<JobQueryRs ErrorCode="0" Message="">
<ID>SDK</ID>
<LastUpdated>2006-11-17T09:12:00</LastUpdated>
<BuildtoStock>false</BuildtoStock>
<TimeandMaterial>false</TimeandMaterial>
<Status>Active</Status>
<Priority>5</Priority>
<CertsReqd>false</CertsReqd>
<LeadDays>2</LeadDays>
<OrderDate>2006-11-17</OrderDate>
<OrderQty>100</OrderQty>
<UnitPrice>5</UnitPrice>
<PriceUofM>ea</PriceUofM>
<TotalPrice>500</TotalPrice>
<ShippedQty>0</ShippedQty>
<ReturnedQty>0</ReturnedQty>
<ScrapQty>0</ScrapQty>
<CompletedQty>0</CompletedQty>
<EstimatedTotalHours>33</EstimatedTotalHours>
<EstimatedServiceCost>0</EstimatedServiceCost>
<EstimatedLaborCost>880</EstimatedLaborCost>
<EstimatedLaborBurden>0</EstimatedLaborBurden>
<EstimatedMachineBurden>770</EstimatedMachineBurden>
<EstimatedGABurden>0</EstimatedGABurden>
<EstimatedMaterial>0</EstimatedMaterial>
<ActualRevenue>0</ActualRevenue>
<ActualHours>0</ActualHours>
<ActualLaborCost>0</ActualLaborCost>
<ActualLaborBurden>0</ActualLaborBurden>
<ActualMachineBurden>0</ActualMachineBurden>
<ActualGABurden>0</ActualGABurden>
<ActualMaterial>0</ActualMaterial>
<Source>0</Source>
<CustomerRef ID="CATINC"/>
<CountactRef ID="0"/>
<ShipToRef ID="2"/>
<TermsCodeRef ID="Net 30 days"/>
<SalesCodeRef ID="Machine Sales"/>
<SalesRepRef ID="JR"/>
<ShipViaRef ID="Consolidated Fr"/>
<UserValues>
<Amount1>0.00</Amount1>
<Amount2>0.00</Amount2>
<Numeric1>0.0</Numeric1>
<Numeric2>0.0</Numeric2>
<Decimal1>0.0</Decimal1>
</UserValues>
<BuildQuantities>
<ExtraQuantity>0</ExtraQuantity>
<PickQty>0</PickQty>
</BuildQuantities>
<PricingCalculator>
<ProfitorMarkup>M</ProfitorMarkup>
<ProfitPercent>15</ProfitPercent>
<LaborMarkup>20</LaborMarkup>
<MaterialMarkup>20</MaterialMarkup>
<ServiceMarkup>20</ServiceMarkup>
<LaborBurdenMarkup>20</LaborBurdenMarkup>
<MachineBurdenMarkup>10</MachineBurdenMarkup>
<GABurdenMarkup>10</GABurdenMarkup>
</PricingCalculator>
<PartSpecification>
<PartNumber>PART</PartNumber>
<Description>test</Description>
<ScrapPercent>0</ScrapPercent>
</PartSpecification>
<RoutingLine>
<ID>1824</ID>
<LastUpdated>2006-11-17T09:12:01</LastUpdated>
<Priority>5</Priority>
<Sequence>0</Sequence>
<Status>O</Status>
<CompletedQty>0</CompletedQty>
<PercentAttended>100</PercentAttended>
<SetUpPercentComplete>0</SetUpPercentComplete>
<RunPercentComplete>0</RunPercentComplete>
<EstimateRunTime>20</EstimateRunTime>
<EstimateTotalTime>22</EstimateTotalTime>
<EstimateSetUpTime>2</EstimateSetUpTime>
<SetUpLabor>0</SetUpLabor>
<RunLabor>0</RunLabor>
<LaborBurden>0</LaborBurden>
<MachineBurden>0</MachineBurden>
<GABurden>0</GABurden>
<ActualRunTime>0</ActualRunTime>
<ActualSetUpTime>0</ActualSetUpTime>
<ActualTotalTime>0</ActualTotalTime>
<ActualScrapQty>0</ActualScrapQty>
<ReworkRunTime>0</ReworkRunTime>
<ReworkSetUpTime>0</ReworkSetUpTime>
<ReworkSetUpLabor>0</ReworkSetUpLabor>
<ReworkRunLabor>0</ReworkRunLabor>
<ReworkGABurden>0</ReworkGABurden>
<ReworkLaborBurden>0</ReworkLaborBurden>
<ReworkMachineBurden>0</ReworkMachineBurden>
<ReworkRunQty>0</ReworkRunQty>
<ReworkScrapQty>0</ReworkScrapQty>
<RemainingRunTime>20</RemainingRunTime>
<RemainingSetUpTime>2</RemainingSetUpTime>
<RemainingTotalTime>22</RemainingTotalTime>
<WorkCenteRef ID="90 PRESS"/>
<SetUpCalculator>
<Hours>2</Hours>
</SetUpCalculator>
<RunTimeCalculator>
<RunMethod>Parts/Hr</RunMethod>
<Run>5</Run>
<Efficiency>100</Efficiency>
</RunTimeCalculator>
<OverlapSpecifier>
<Method>Qty</Method>
<Overlap>0</Overlap>
</OverlapSpecifier>
<RouteSchedule>
<StartTime>0</StartTime>
<EndTime>0</EndTime>
</RouteSchedule>
</RoutingLine>
<RoutingLine>
<ID>1825</ID>
<LastUpdated>2006-11-17T09:12:01</LastUpdated>
<Priority>5</Priority>
<Sequence>1</Sequence>
<Status>O</Status>
<CompletedQty>0</CompletedQty>
<PercentAttended>100</PercentAttended>
<SetUpPercentComplete>0</SetUpPercentComplete>
<RunPercentComplete>0</RunPercentComplete>
<EstimateRunTime>10</EstimateRunTime>
<EstimateTotalTime>11</EstimateTotalTime>
<EstimateSetUpTime>1</EstimateSetUpTime>
<SetUpLabor>0</SetUpLabor>
<RunLabor>0</RunLabor>
<LaborBurden>0</LaborBurden>
<MachineBurden>0</MachineBurden>
<GABurden>0</GABurden>
<ActualRunTime>0</ActualRunTime>
<ActualSetUpTime>0</ActualSetUpTime>
<ActualTotalTime>0</ActualTotalTime>
<ActualScrapQty>0</ActualScrapQty>
<ReworkRunTime>0</ReworkRunTime>
<ReworkSetUpTime>0</ReworkSetUpTime>
<ReworkSetUpLabor>0</ReworkSetUpLabor>
<ReworkRunLabor>0</ReworkRunLabor>
<ReworkGABurden>0</ReworkGABurden>
<ReworkLaborBurden>0</ReworkLaborBurden>
<ReworkMachineBurden>0</ReworkMachineBurden>
<ReworkRunQty>0</ReworkRunQty>
<ReworkScrapQty>0</ReworkScrapQty>
<RemainingRunTime>10</RemainingRunTime>
<RemainingSetUpTime>1</RemainingSetUpTime>
<RemainingTotalTime>11</RemainingTotalTime>
<WorkCenteRef ID="LATHE"/>
<SetUpCalculator>
<Hours>1</Hours>
</SetUpCalculator>
<RunTimeCalculator>
<RunMethod>Parts/Hr</RunMethod>
<Run>10</Run>
<Efficiency>100</Efficiency>
</RunTimeCalculator>
<OverlapSpecifier>
<Method>Qty</Method>
<Overlap>0</Overlap>
</OverlapSpecifier>
<RouteSchedule>
<StartTime>0</StartTime>
<EndTime>0</EndTime>
</RouteSchedule>
</RoutingLine>
</JobQueryRs>
</JBXMLRespond>
</JBXML>
0
 
LVL 26

Accepted Solution

by:
EDDYKT earned 1600 total points
ID: 17988255
You may want to test it first before loop through

i.e.


Private function Op(byval ID as string) as string
Dim xmlChild
Dim xmlApps As IXMLDOMElement
Dim xmlDoc As New DOMDocument40

'xmlDoc.Load ("xmlResponseDoc")   ' use loadxml if you pass as xml
xmlDoc.loadXML ("xmlResponsedoc")
If (Not xmlDoc.selectSingleNode("//WorkCenteRef [@ID='" & ID & "']") Is Nothing) Then
For Each xmlchild In xmlDoc.selectSingleNode("//WorkCenteRef [@ID='" & ID & "']").parentNode.childNodes
    If (xmlchild.baseName = "ID") Then Op=xmlchild.Text
Next
end if
End Sub


where ID is LATHE

XML is case sensitive, if you pass Lathe you will get nothing back
0
 

Author Comment

by:JMO9966
ID: 18042375
Thank You EDDYKT.

I will test this out and let you know, I've accepted for now.  I apologize, I've been out of town.

JMO9966
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

770 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