XML Import issue

Using VB6, MSXML4 and ADO2.7

[infernofiasco]

I am tryin to import a xml file which has been generated by a java based api.

Example of XML:

<?xml version="1.0" encoding="UTF-8"?>
<BusinessObjects xmlns="http://schemas.primavera.com/2004b/API/MorganXMLExporter" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.primavera.com/2004b/API/prmbo c:\Program Files\Primavera\IntegrationAPI\schema\PRMBO.xsd">
<Project>
<Id>WMB-CS</Id>
<Activity>
<Id>WMB-CS</Id>
<ResourceAssignment>
<ResourceId>CALZON</ResourceId>
<PlannedUnits>40.0</PlannedUnits>
<ActualUnits>0.0</ActualUnits>
</ResourceAssignment>
<ResourceAssignment>
<ResourceId>CALPAR</ResourceId>
<PlannedUnits>40.0</PlannedUnits>
<ActualUnits>0.0</ActualUnits>
</ResourceAssignment>
</Activity>
</Project>
</BusinessObjects>

When I am trying to import it using VB6, it seems to not want to process any nodes after the businessobjects node. If I manually remove the businessobjects node and replace with a single "projects" node with no attributes it works fine! Anyone come across this? Do I need to escape the businessobjects node attributes?

Cheers

Chris

[choster]

The input XML appears to be well-formed and the namespace declarations on <BusinessObjects> should not in and of themselves have any impact on the ability to process the nodes.

What is it you want to do with the input, and what code are you using? I'm not familiar with VB myself but another poster might be able to shed some light.

[infernofiasco]

It turrned out to be an issue with name space!

Originally I had been selecting the nodes with plain old "/BusinessObjects", I had to set a property which pointed to the namespace within the file: objXMLDOM.setProperty "SelectionNamespaces" ..

I then had to use the associated namespace variable within my node selection: "/xsi:BusinessObjects".

This now works fine :)

Private Sub Form_Load()

' Author: Chris Belfield
' Description: Morgan EST API Import routine for MSSQL 2000 using MSXML 4.0, ADO 2.7 and VBSendMail

Dim objXMLDOM
Dim objProjects

Dim sProjList
Dim sFileName
Dim sResult
Dim sProjID

sResult = "Import was completed successfully."

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim objADORS As New ADODB.Recordset
Dim objADOCnn As New ADODB.Connection

objADOCnn.Open "PROVIDER=SQLOLEDB;SERVER=localhost;UID=sa;PWD=inf3rno;DATABASE=XMLTest;"

sSql = "Delete FROM t_import2"
Set objADODel = objADOCnn.Execute(sSql)

objADORS.Open "SELECT * FROM t_import2 WHERE 1 = 2", objADOCnn, adOpenKeyset, adLockOptimistic

Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False

' Set to export folder of API
objXMLDOM.setProperty "SelectionNamespaces", "xmlns:xsi='http://schemas.primavera.com/2004b/API/MorganXMLExporter'"
sFileName = "c:\xml\export6.xml"

objXMLDOM.Load sFileName

If Not objXMLDOM.Load(sFileName) Then
sResult = "Error: " & objXMLDOM.parseError.reason
End If

Set objProjects = objXMLDOM.selectNodes("/xsi:BusinessObjects/xsi:Project")

For Each objProjIDNodes In objProjects

sProjID = objProjIDNodes.selectSingleNode("xsi:Id").nodeTypedValue

Set objActivities = objXMLDOM.selectNodes("/xsi:BusinessObjects/xsi:Project[xsi:Id[text()='" & sProjID & "']]/xsi:Activity")

' Selecting each Activity

For Each objActivityNode In objActivities

sActivityID = objActivityNode.selectSingleNode("xsi:Id").nodeTypedValue

' Selecting each Resource
Set objResources = objXMLDOM.selectNodes("/xsi:BusinessObjects/xsi:Project[xsi:Id[text()='" & sProjID & "']]/xsi:Activity[xsi:Id[text()='" & sActivityID & "']]/xsi:ResourceAssignment")

For Each objResourceNode In objResources

sResourceID = objResourceNode.selectSingleNode("xsi:ResourceId").nodeTypedValue
lPlannedUnits = objResourceNode.selectSingleNode("xsi:PlannedUnits").nodeTypedValue
lActualUnits = objResourceNode.selectSingleNode("xsi:ActualUnits").nodeTypedValue

' Update Table
With objADORS
.AddNew
.Fields("ProjectID") = sProjID
.Fields("ActivityID") = sActivityID
.Fields("ResourceID") = sResourceID
.Fields("PlannedUnits") = lPlannedUnits
.Fields("ActualUnits") = lActualUnits
.Update
End With

Next

Next

Next

objADORS.Close
objADOCnn.Close

Set objProjects = Nothing
Set objActivities = Nothing
Set objResources = Nothing
Set objADORS = Nothing
Set objADOCnn = Nothing

End Sub