XML Import via ActiveX/VB

[stiej]

Hi,

This may not be the correct forum but if you could point me in the right direction it would be much appreciated. I am trying to import a number of rather complicated XML file into SQL Server 2000 using VB in an ActiveX script executed through a DTS pack, but my knowlede of xml is limited and my VB isn't brilliant hence using some code that I found, I have had to do it this way due to the files being so large.

I only want the data from the transformedXML tag at the bottom, you can see some of the fields that I want referenced in the VB script

When the script is run, it ends successfully, though there is nothing in the table.

Any idea????


My Xml file is listed below
<?xml version="1.0"?>
<Inspection xmlns:xsd="

http://www.w3"

xmlns:xsi="

http://www.w3">

<FileName>S:\Schools data\MEG\280480.XML</FileName>
<InstitutionType>School</InstitutionType>
<InstitutionName>West Street</InstitutionName>
<InstitutionNumber>119174</InstitutionNumber>
<InspectionNumber>280480</InspectionNumber>
<Date>31/10/2005</Date>
<IndependentSchool>false</IndependentSchool>
<InspectionType>S5</InspectionType>
<Inspectors>
<OIN>29504</OIN>
<Name>Shirley</Name>
<LeadInspector>true</LeadInspector>
<AssistantInspector>false</AssistantInspector>
</Inspectors>
<Inspectors>
<OIN>18146</OIN>
<Name>Michael</Name>
<LeadInspector>false</LeadInspector>
<AssistantInspector>true</AssistantInspector>
</Inspectors>
<SchoolEfs>
<InspectorOin>18146 - Michael</InspectorOin>
<ObservationTime>30</ObservationTime>
<EfType>L</EfType>
<YearGroups>1</YearGroups>
<YearGroups />
<YearGroups />
<YearGroups />
<GroupingAbility>MC</GroupingAbility>
<GroupingGender>MI</GroupingGender>
<Present>18</Present>
<Nor>20</Nor>
<SubjectCode1>MA</SubjectCode1>
<SubjectCode2>NY</SubjectCode2>
<SenSupportCount>0</SenSupportCount>
<SenAssistantCount>1</SenAssistantCount>
<EalSupportCount>1</EalSupportCount>
<EalAssistantCount>0</EalAssistantCount>
<OthSupportCount>0</OthSupportCount>
<OthAssistantCount>1</OthAssistantCount>
<InspInitialWithEf>MO03</InspInitialWithEf>
<Focus />
<Context />
<Evaluation />
<Summary />
<Overall>2</Overall>
<Standards>3</Standards>
<Progress>2</Progress>
<PersonalDev>0</PersonalDev>
<Teaching>2</Teaching>
<Curriculum>2</Curriculum>
<Care>2</Care>
<Leadership>0</Leadership>
<CsiEval />
</SchoolEfs>
<SchoolEfs>
REPEATS AS ABOVE (A FEW OF THESE TAGS)
</SchoolEfs>
<SchoolKsYg xsi:type="xsd:string">Key Stage F</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Key Stage 1</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Key Stage 2</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group N</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group R</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group 1</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group 2</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group 3</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group 4</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group 5</SchoolKsYg>
<SchoolKsYg xsi:type="xsd:string">Year Group 6</SchoolKsYg>
<InspectionReport>
<TransformedXml>&lt;?xml version="1.0" encoding="utf-16"?&gt;
&lt;InspectionReport xmlns="

http://www.go"

xmlns:apd="

http://www.go"

xmlns:core="

http://www.go"

xmlnsdt="

http://www.gov"&gt;

&lt;InspectionKeyData&gt;
&lt;InspectionNumber&gt;280480&lt;/InspectionNumber&gt;
&lt;InspectionBeginningDate&gt;2005-10-31&lt;/InspectionBeginningDate&gt;
&lt;InspectionEndDate&gt;2005-11-01&lt;/InspectionEndDate&gt;
&lt;ReportingInspector&gt;
&lt;InspectorName&gt;Shirley Herring&lt;/InspectorName&gt;
&lt;InspectorStatus /&gt;
&lt;/ReportingInspector&gt;
&lt;InspectionCategory /&gt;
&lt;DateOfPreviousInspection&gt;1900-01-01&lt;/DateOfPreviousInspection&gt;
&lt;/InspectionKeyData&gt;
&lt;InspectionSetting&gt;
&lt;SettingName&gt;West Street&lt;/SettingName&gt;
&lt;SettingAddress&gt;
&lt;apd:Line&gt;West Street&lt;/apd:Line&gt;
&lt;apd:Line&gt;C&lt;/apd:Line&gt;
&lt;apd:Line&gt;Lanc&lt;/apd:Line&gt;
&lt;apd:Line&gt;BBW&lt;/apd:Line&gt;
&lt;/SettingAddress&gt;
&lt;TelephoneNumber&gt;01282&lt;/TelephoneNumber&gt;
&lt;FaxNumber&gt;01282&lt;/FaxNumber&gt;
&lt;School&gt;
&lt;UniqueReferenceNumber&gt;119174&lt;/UniqueReferenceNumber&gt;
&lt;LEAName&gt;Lan&lt;/LEAName&gt;
&lt;TypeOfSchool&gt;primary&lt;/TypeOfSchool&gt;
&lt;SchoolCategory&gt;community&lt;/SchoolCategory&gt;
&lt;PupilLowAge&gt;3&lt;/PupilLowAge&gt;
&lt;PupilHighAge&gt;11&lt;/PupilHighAge&gt;
&lt;PupilGender&gt;mixed&lt;/PupilGender&gt;
&lt;NumberOnRoll&gt;190&lt;/NumberOnRoll&gt;
&lt;AppropriateAuthority&gt;The governing body&lt;/AppropriateAuthority&gt;
&lt;ChairOfGovernors&gt;Ann&lt;/ChairOfGovernors&gt;
&lt;Headteacher&gt;Mr Ron Peden&lt;/Headteacher&gt;
&lt;/School&gt;
&lt;/InspectionSetting&gt;
&lt;InspectionJudgements&gt;
&lt;JudgeOverallEffectiveness&gt;
&lt;MeetingNeeds&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/MeetingNeeds&gt;
&lt;PartnershipWorking&gt;
&lt;SchoolGrade&gt;1&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/PartnershipWorking&gt;
&lt;QualityFoundation&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/QualityFoundation&gt;
&lt;SelfEvaluation&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/SelfEvaluation&gt;
&lt;ImprovementCapacity&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;SixthFormYesNo&gt;key stage not present&lt;/SixthFormYesNo&gt;
&lt;/ImprovementCapacity&gt;
&lt;EffectiveSteps&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;SixthFormYesNo&gt;key stage not present&lt;/SixthFormYesNo&gt;
&lt;/EffectiveSteps&gt;
&lt;/JudgeOverallEffectiveness&gt;
&lt;JudgeAchievementAndStandards&gt;
&lt;LearnersAchieve&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/LearnersAchieve&gt;
&lt;StandardsReached&gt;
&lt;SchoolGrade&gt;3&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/StandardsReached&gt;
&lt;ProgressMade&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/ProgressMade&gt;
&lt;DifficultyDisability&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/DifficultyDisability&gt;
&lt;/JudgeAchievementAndStandards&gt;
&lt;JudgePersonalDevelopment&gt;
&lt;OverallPersonal&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/OverallPersonal&gt;
&lt;SpiritualCultural&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/SpiritualCultural&gt;
&lt;Behaviour&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/Behaviour&gt;
&lt;Attendance&gt;
&lt;SchoolGrade&gt;3&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/Attendance&gt;
&lt;Enjoyment&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/Enjoyment&gt;
&lt;SafePractice&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/SafePractice&gt;
&lt;HealthyLifestyle&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/HealthyLifestyle&gt;
&lt;PositiveContribution&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/PositiveContribution&gt;
&lt;WorkplaceEconomic&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/WorkplaceEconomic&gt;
&lt;/JudgePersonalDevelopment&gt;
&lt;JudgeQualityOfProvision&gt;
&lt;EffectiveFullRange&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/EffectiveFullRange&gt;
&lt;CurriculumNeedsInterests&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/CurriculumNeedsInterests&gt;
&lt;CareGuidance&gt;
&lt;SchoolGrade&gt;1&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/CareGuidance&gt;
&lt;/JudgeQualityOfProvision&gt;
&lt;JudgeLeadershipAndManagement&gt;
&lt;RaisingSupporting&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/RaisingSupporting&gt;
&lt;DirectionQuality&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/DirectionQuality&gt;
&lt;MonitorReview&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/MonitorReview&gt;
&lt;EqualityDiscrimination&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/EqualityDiscrimination&gt;
&lt;EffectivenessEfficiency&gt;
&lt;SchoolGrade&gt;2&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/EffectivenessEfficiency&gt;
&lt;GovernorsSupervision&gt;
&lt;SchoolGrade&gt;3&lt;/SchoolGrade&gt;
&lt;SixthFormGrade&gt;9&lt;/SixthFormGrade&gt;
&lt;/GovernorsSupervision&gt;
&lt;AdequacySuitability&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;SixthFormYesNo&gt;key stage not present&lt;/SixthFormYesNo&gt;
&lt;/AdequacySuitability&gt;
&lt;/JudgeLeadershipAndManagement&gt;
&lt;JudgeHealth&gt;
&lt;EatDrink&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/EatDrink&gt;
&lt;Exercise&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/Exercise&gt;
&lt;DiscourageAbuse&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/DiscourageAbuse&gt;
&lt;SexualHealth&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/SexualHealth&gt;
&lt;/JudgeHealth&gt;
&lt;JudgeSafety&gt;
&lt;GovernmentRequirements&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/GovernmentRequirements&gt;
&lt;RiskAssessment&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/RiskAssessment&gt;
&lt;ReduceAntiSocial&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/ReduceAntiSocial&gt;
&lt;AvoidKeyRisks&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/AvoidKeyRisks&gt;
&lt;/JudgeSafety&gt;
&lt;JudgePositive&gt;
&lt;Relationships&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/Relationships&gt;
&lt;DecisionParticipate&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/DecisionParticipate&gt;
&lt;ActivityCommunity&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/ActivityCommunity&gt;
&lt;/JudgePositive&gt;
&lt;JudgeEconomic&gt;
&lt;BasicSkills&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/BasicSkills&gt;
&lt;Enterprise&gt;
&lt;SchoolYesNo&gt;yes&lt;/SchoolYesNo&gt;
&lt;/Enterprise&gt;
&lt;CareersGuidance&gt;
&lt;SchoolYesNo&gt;key stage not present&lt;/SchoolYesNo&gt;
&lt;/CareersGuidance&gt;
&lt;EmploymentEconomy&gt;
&lt;SchoolYesNo&gt;key stage not present&lt;/SchoolYesNo&gt;
&lt;/EmploymentEconomy&gt;
&lt;/JudgeEconomic&gt;
&lt;SpecialMeasures&gt;
&lt;SchoolYesNo&gt;no&lt;/SchoolYesNo&gt;
&lt;/SpecialMeasures&gt;
&lt;NoticeImprove&gt;
&lt;SchoolYesNo&gt;no&lt;/SchoolYesNo&gt;
&lt;/NoticeImprove&gt;
&lt;/InspectionJudgements&gt;
&lt;/InspectionReport&gt;</TransformedXml>
<ImportedFileDetails>Filename: F:\doc
File date/time: 11 November 2005 15:49:38
Import date: 11 November 2005 15:54:26</ImportedFileDetails>
</InspectionReport>
</Inspection>



Function Main()
Dim objFSO
Dim objFolder
Dim objFilesColl
Dim iFilesCount
Dim objFile

Dim objXMLDOM
Dim objNodes
Dim objNodeItem

Dim objADORS
Dim objADOCnn

Dim strCurFileName

'Create and initialize (Open) ADO Connection
Set objADOCnn = CreateObject("ADODB.Connection")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=Server;UID=UID;PWD=password;DATABASE=Database;"

'Create MSXML 4.0 DOM Object and initialize it
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False

'Get a list of files in the specified directory
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFilesPath)
Set objFilesColl = objFolder.Files

'Load each file in MSXML DOM and use ADO to insert data into the table
For Each objFile in objFilesColl

strCurFileName = strFilesPath & "\" & objFile.Name

'Load the XML file
'No error handling done
objXMLDOM.load strCurFileName

Set objNodes = objXMLDOM.selectNodes("/TransformedXML")

'Create and Open the recordset
Set objADORS = CreateObject("ADODB.Recordset")
objADORS.Open "SELECT * FROM DataSet WHERE 1 = 2", objADOCnn,
adOpenKeyset, adLockOptimistic

'Add records
For Each objNodeItem In objNodes
With objADORS
.AddNew


.fields("InspectionNumber") =
objNodeItem.selectSingleNode("InspectionNumber").nodeTypedValue
.fields("InspectionBeginningDate") =
objNodeItem.selectSingleNode("InspectionBeginningDate").nodeTypedValue
.fields("InspectionEndDate") =
objNodeItem.selectSingleNode("InspectionEndDate").nodeTypedValue


' .Update
End With
Next

objADORS.Close

'Message box for debugging purposes
'MsgBox "Copied data from " & strCurFileName & " into the database"
Next

objADOCnn.Close

Set objADORS = Nothing
Set objADOCnn = Nothing
Set objXMLDOM = Nothing
Set objFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

Thanks PD