Please Urgent XML import
-
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 exacuted 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.
My Xml file is listed below
<?xml version="1.0"?>
<Inspection xmlns:xsd="http://www."
xmlns:xsi="http://www.">
<FileName>S:\Sch data\MEG\280480.XML</FileName>
<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>29504 - Shirley</InspectorOin>
<ObservationTime>30</ObservationTime>
<EfType>L</EfType>
<YearGroups>3</YearGroups>
<YearGroups>4</YearGroups>
<YearGroups />
<YearGroups />
<GroupingAbility>MC</GroupingAbility>
<GroupingGender>MI</GroupingGender>
<Present>24</Present>
<Nor>27</Nor>
<SubjectCode1>MU</SubjectCode1>
<SubjectCode2>LY</SubjectCode2>
<SenSupportCount>0</SenSupportCount>
<SenAssistantCount>1</SenAssistantCount>
<EalSupportCount>0</EalSupportCount>
<EalAssistantCount>0</EalAssistantCount>
<OthSupportCount>0</OthSupportCount>
<OthAssistantCount>0</OthAssistantCount>
<InspInitialWithEf>SH29</InspInitialWithEf>
<Focus />
<Context />
<Evaluation />
<Summary />
<Overall>2</Overall>
<Standards>3</Standards>
<Progress>2</Progress>
<PersonalDev>2</PersonalDev>
<Teaching>2</Teaching>
<Curriculum>2</Curriculum>
<Care>0</Care>
<Leadership>0</Leadership>
<CsiEval />
</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><?xml version="1.0" encoding="utf-16"?>
<InspectionReport xmlns="http://www.gov" xmlns:apd="http://www.gov"
xmlns:core="http://www.gov" xmlnsdt="http://www.gov">
<InspectionKeyData>
<InspectionNumber>280480</InspectionNumber>
<InspectionBeginningDate>2005-10-31</InspectionBeginningDate>
<InspectionEndDate>2005-11-01</InspectionEndDate>
<ReportingInspector>
<InspectorName>Shirley</InspectorName>
<InspectorStatus />
</ReportingInspector>
<InspectionCategory />
<DateOfPreviousInspection>1900-01-01</DateOfPreviousInspection>
</InspectionKeyData>
<InspectionSetting>
<SettingName>West</SettingName>
<SettingAddress>
<apd:Line>West Street</apd:Line>
<apd:Line>Colne</apd:Line>
<apd:Line>L</apd:Line>
<apd:Line>W</apd:Line>
</SettingAddress>
<TelephoneNumber>01282</TelephoneNumber>
<FaxNumber>01282 863542</FaxNumber>
<School>
<UniqueReferenceNumber>119174</UniqueReferenceNumber>
<LEAName>Lancashire</LEAName>
<TypeOfSchool>primary</TypeOfSchool>
<SchoolCategory>community</SchoolCategory>
<PupilLowAge>3</PupilLowAge>
<PupilHighAge>11</PupilHighAge>
<PupilGender>mixed</PupilGender>
<NumberOnRoll>190</NumberOnRoll>
<AppropriateAuthority>The governing body</AppropriateAuthority>
<ChairOfGovernors>Ann</ChairOfGovernors>
<Headteacher>Mr</Headteacher>
</School>
</InspectionSetting>
<ReportBody>
<Introduction>
<Preamble>
<Paragraph>The inspection was carried out by two additional
inspectors.</Paragraph>
</Preamble>
<DescriptionOfSetting>
<Paragraph>West a slightly smaller Colne. </Paragraph>
<Paragraph>The majority of pupils are from a white British
background but almost a fifth of pupils are of Pakistani heritage.
Approximately half of the children have learning difficulties and a higher
number than average have statements of special need. The number of children
for whom English is an additional language is above average. </Paragraph>
</DescriptionOfSetting>
</Introduction>
<OverallEffectiveness>
<FormalStatement>Inspectors agree. </FormalStatement>
<List>
<Item>
</Item>
</List>
<Paragraph>not applicable</Paragraph>
<ToImproveFurther>
<List>
<Item>Make better use of assessment to monitor the progress of
individual pupils and to set work at the right level.</Item>
</List>
<Paragraph> </Paragraph>
<List>
<Item>Raise standards in writing, expecting pupils to produce
longer pieces of written work in English and other subjects. </Item>
</List>
</ToImproveFurther>
</OverallEffectiveness>
<AchievementAndStandards>
<Paragraph>Pupils achieve well. The school has received achievement
awards in recognition of its success for each of the past two
years.</Paragraph>
<Paragraph>Pupils continue to make good progress through the juniors
and reach average standards by the end of Year 6, as shown in the results of
national tests for the last two years. The mathematics and science results in
2004 were in line with national averages.</Paragraph>
<Paragraph>Almost half of the pupils in the current Year 6 have
learning difficulties. The systematic teaching and effective support is
helping all pupils to achieve well in relation to their
capabilities.</Paragraph>
</AchievementAndStandards>
<PersonalDevelopment>
<Paragraph>Pupils’ personal development is good. Parents see the
school as one where all children and adults are valued. Pupils behave well;
they enjoy school, work hard and are well mannered and
polite.</Paragraph><Paragraph>Grade: 2</Paragraph>
</PersonalDevelopment>
<QualityOfProvision>
<TeachingAndLearning><Paragraph>Teaching and learning are good
throughout the school, including the Foundation Stage. Most lessons include
challenging activities and active questioning, which hold pupils’ attention
and keep them engaged in learning.</Paragraph><Paragraph>In Years 1 to 6, a
variety of well planned, practical activities, especially in
mathematics.</Paragraph><Paragraph>Teaching assistants play an important
role.</Paragraph><Paragraph>Assessment in English and mathematics provides
class teachers with a clear picture of their pupils’ current progress, which
enables them to target help where it is needed. </Paragraph><Paragraph>Grade:
2</Paragraph>
</TeachingAndLearning>
<CurriculumAndOtherActivities>
<Paragraph>The curriculum is good and meets the needs of all pupils.
In the Foundation Stage, children learn through play in a stimulating
environment where each child’s needs and progress are carefully planned for
and met.</Paragraph>
<List>
<Item>Grade: 2</Item>
</List>
</CurriculumAndOtherActivities>
<CareGuidanceAndSupport>
<Paragraph>This aspect of the school’s work is outstanding and makes
a significant difference to children’s lives.</Paragraph><Paragraph>The
school makes every effort to remove risks and eliminate dangers in all
aspects of learning. From the outset, pupils are made aware of the importance
of safety. Outdoors, during play and lunchtimes, good supervision and safety
procedures ensure that all pupils are safe. A well attended breakfast club
gives a good start to the day for many pupils and encourages improved
attendance.</Paragraph><List><Item>Grade: 2</Item></List>
</CareGuidanceAndSupport>
</QualityOfProvision>
<LeadershipAndManagement>
<Paragraph>The good leadership of the headteacher and deputy
headteacher, based on a clear and accurate evaluation of the school’s work,
has been the crucial factor in improving the school.
</Paragraph><Paragraph>The school has made a good improvement in standards in
the main subjects and information and communication technology (ICT) since
the last inspection and it is clear from this that the school has a good
capacity to improve further.</Paragraph><Paragraph>Governance is satisfactory
and financial management is good. Governors are supportive of the school but
they are not sufficiently aware of its strengths and weaknesses to be able to
hold the professionals to account and therefore contribute to the school’s
improvement. However, they are now taking steps to become more closely
involved in the school’s work.</Paragraph><List><Item>Grade: 2</Item></List>
</LeadershipAndManagement>
</ReportBody>
<InspectionJudgements>
<JudgeOverallEffectiveness>
<MeetingNeeds>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</MeetingNeeds>
<PartnershipWorking>
<SchoolGrade>1</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</PartnershipWorking>
<QualityFoundation>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</QualityFoundation>
<SelfEvaluation>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</SelfEvaluation>
<ImprovementCapacity>
<SchoolYesNo>yes</SchoolYesNo>
<SixthFormYesNo>key stage not present</SixthFormYesNo>
</ImprovementCapacity>
<EffectiveSteps>
<SchoolYesNo>yes</SchoolYesNo>
<SixthFormYesNo>key stage not present</SixthFormYesNo>
</EffectiveSteps>
</JudgeOverallEffectiveness>
<JudgeAchievementAndStandards>
<LearnersAchieve>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</LearnersAchieve>
<StandardsReached>
<SchoolGrade>3</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</StandardsReached>
<ProgressMade>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</ProgressMade>
<DifficultyDisability>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</DifficultyDisability>
</JudgeAchievementAndStandards>
<JudgePersonalDevelopment>
<OverallPersonal>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</OverallPersonal>
<SpiritualCultural>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</SpiritualCultural>
<Behaviour>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</Behaviour>
<Attendance>
<SchoolGrade>3</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</Attendance>
<Enjoyment>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</Enjoyment>
<SafePractice>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</SafePractice>
<HealthyLifestyle>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</HealthyLifestyle>
<PositiveContribution>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</PositiveContribution>
<WorkplaceEconomic>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</WorkplaceEconomic>
</JudgePersonalDevelopment>
<JudgeQualityOfProvision>
<EffectiveFullRange>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</EffectiveFullRange>
<CurriculumNeedsInterests>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</CurriculumNeedsInterests>
<CareGuidance>
<SchoolGrade>1</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</CareGuidance>
</JudgeQualityOfProvision>
<JudgeLeadershipAndManagement>
<RaisingSupporting>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</RaisingSupporting>
<DirectionQuality>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</DirectionQuality>
<MonitorReview>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</MonitorReview>
<EqualityDiscrimination>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</EqualityDiscrimination>
<EffectivenessEfficiency>
<SchoolGrade>2</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</EffectivenessEfficiency>
<GovernorsSupervision>
<SchoolGrade>3</SchoolGrade>
<SixthFormGrade>9</SixthFormGrade>
</GovernorsSupervision>
<AdequacySuitability>
<SchoolYesNo>yes</SchoolYesNo>
<SixthFormYesNo>key stage not present</SixthFormYesNo>
</AdequacySuitability>
</JudgeLeadershipAndManagement>
<JudgeHealth>
<EatDrink>
<SchoolYesNo>yes</SchoolYesNo>
</EatDrink>
<Exercise>
<SchoolYesNo>yes</SchoolYesNo>
</Exercise>
<DiscourageAbuse>
<SchoolYesNo>yes</SchoolYesNo>
</DiscourageAbuse>
<SexualHealth>
<SchoolYesNo>yes</SchoolYesNo>
</SexualHealth>
</JudgeHealth>
<JudgeSafety>
<GovernmentRequirements>
<SchoolYesNo>yes</SchoolYesNo>
</GovernmentRequirements>
<RiskAssessment>
<SchoolYesNo>yes</SchoolYesNo>
</RiskAssessment>
<ReduceAntiSocial>
<SchoolYesNo>yes</SchoolYesNo>
</ReduceAntiSocial>
<AvoidKeyRisks>
<SchoolYesNo>yes</SchoolYesNo>
</AvoidKeyRisks>
</JudgeSafety>
<JudgePositive>
<Relationships>
<SchoolYesNo>yes</SchoolYesNo>
</Relationships>
<DecisionParticipate>
<SchoolYesNo>yes</SchoolYesNo>
</DecisionParticipate>
<ActivityCommunity>
<SchoolYesNo>yes</SchoolYesNo>
</ActivityCommunity>
</JudgePositive>
<JudgeEconomic>
<BasicSkills>
<SchoolYesNo>yes</SchoolYesNo>
</BasicSkills>
<Enterprise>
<SchoolYesNo>yes</SchoolYesNo>
</Enterprise>
<CareersGuidance>
<SchoolYesNo>key stage not present</SchoolYesNo>
</CareersGuidance>
<EmploymentEconomy>
<SchoolYesNo>key stage not present</SchoolYesNo>
</EmploymentEconomy>
</JudgeEconomic>
<SpecialMeasures>
<SchoolYesNo>no</SchoolYesNo>
</SpecialMeasures>
<NoticeImprove>
<SchoolYesNo>no</SchoolYesNo>
</NoticeImprove>
</InspectionJudgements>
<LetterToPupils>
<Paragraph>Alexandra House</Paragraph>
<Paragraph>Thank you for making Mr and myself so welcome when we
inspected your school recently. </Paragraph>
<Paragraph>We like the way the school council discusses your ideas and
then makes suggestions to Mr . We know that you are now enjoying extra play
equipment because of this. </Paragraph>
<Paragraph>We have asked Mr Peden and your teachers to help you to do
even better by giving you more chances to write in lessons. We have also
asked the teachers to talk to you about your own targets so that you can
continue to work hard to improve your work.</Paragraph>
<Paragraph>Yours sincerely</Paragraph>
<Paragraph>Mrs</Paragraph>
<Paragraph>Lead Inspector</Paragraph>
<Paragraph> Annex B</Paragraph>
</LetterToPupils>
</InspectionReport></TransformedXml>
<ImportedFileDetails>Filename: F:\margaret
File date/time: 11 November 2005 15:49:38
Import date: 11 November 2005 15:54:26</ImportedFileDetails>
</InspectionReport>
</Inspection>
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
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
Post a reply
XML forum discussion
-
XBrowse
by liuying9104 (3 replies)
-
generating xml file
by huangdm05941 (2 replies)
-
urgent
by huangdm05941 (2 replies)
-
How to increment variable value in xslt
by huangdm05941 (1 replies)
-
Inside Open XML
by jettimadhu (2 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
No one has replied yet! Why not be the first?
Sign in or Join us (it's free).