Please Urgent XML import

  • 15 years ago

    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

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Owning a computer without programming is like having a kitchen and using only the microwave oven” - Charles Petzold