Solved

Need help on Muenchian grouping  syntax

Posted on 2010-09-05
20
519 Views
Last Modified: 2013-11-18
Hello Experts,
I have an application which call an SP in SQL Server and the output from SQL Server are then passed through an XSLT before passed as an XML file. The stored Procedure generates multiple rows depending on what is being retrieved from the database (example attached ...SPOutput or Dataset sample.xslx).
Initially where calling only one student at a time but have decided to call one or more in just one transaction thus rendering our existing XSLT  unusable.
Anyway what i wanted to happen is to introduce a 'Muenchian' grouping in the ....
  <xsl:template name="callPupil">
...using StudentID as the referrence. I have attached my original XSLT which won't work on our new project.(multiple student data set)
<?xml version="1.0" encoding="iso-8859-1" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="xml" version="1.0" indent="yes" encoding="iso-8859-1" />

  <xsl:template match="/">
    <CTfile>
      <xsl:call-template name="callHeader" />
      <xsl:call-template name="callCTFpupilData" />
    </CTfile>
  </xsl:template>

  <xsl:template name="callHeader">
    <Header>
      <DocumentName>
        <xsl:value-of select="/NewDataSet/Table/DocumentName" />
      </DocumentName>
      <CTFversion>
        <xsl:value-of select="/NewDataSet/Table/CTFVersion" />
      </CTFversion>
      <DateTime>
        <xsl:value-of select="/NewDataSet/Table/DateTime" />
      </DateTime>
      <DocumentQualifier>
        <xsl:value-of select="/NewDataSet/Table/DocumentQualifier" />
      </DocumentQualifier>
      <SupplierID>
        <xsl:value-of select="/NewDataSet/Table/SupplierID" />
      </SupplierID>
      <xsl:call-template name="callSourceSchool" />
      <xsl:call-template name="callDestSchool" />
    </Header>
  </xsl:template>

  <xsl:template name="callSourceSchool">
    <SourceSchool>
      <LEA>
        <xsl:value-of select="/NewDataSet/Table/SchoolSourceLEACode" />
      </LEA>
      <Estab>
        <xsl:value-of select="/NewDataSet/Table/SourceSchoolEstablishmentCode" />
      </Estab>
      <SchoolName>
        <xsl:value-of select="/NewDataSet/Table/SchoolName" />
      </SchoolName>
      <AcademicYear>
        <xsl:value-of select="/NewDataSet/Table/AcademicYear" />
      </AcademicYear>
    </SourceSchool>
  </xsl:template>

  <xsl:template name="callDestSchool">
    <DestSchool>
      <LEA>
        <xsl:value-of select="/NewDataSet/Table/DestLEA" />
      </LEA>
      <Estab>
        <xsl:value-of select="/NewDataSet/Table/DestEstab" />
      </Estab>
    </DestSchool>
  </xsl:template>


  <xsl:template name="callCTFpupilData">
    <CTFpupilData>
      <xsl:call-template name="callPupil" />
    </CTFpupilData>
  </xsl:template>


  <xsl:template name="callPupil">
    <Pupil>
      <UPN>
        <xsl:value-of select="/NewDataSet/Table/NationalUPN" />
      </UPN>
      <Surname>
        <xsl:value-of select="/NewDataSet/Table/LegalSurname" />
      </Surname>
      <Forename>
        <xsl:value-of select="/NewDataSet/Table/Firstname" />
      </Forename>
      <DOB>
        <xsl:value-of select="/NewDataSet/Table/DOB" />
      </DOB>
      <Gender>
        <xsl:value-of select="/NewDataSet/Table/Sex" />
      </Gender>
      <xsl:call-template name="callBasicDetails" />
      <xsl:call-template name="callAddress" />
      <xsl:call-template name="callPGContacts" />
      <xsl:call-template name="callSchoolHistory" />
      <xsl:call-template name="callSuppInfo" />
    </Pupil>
  </xsl:template>

  <xsl:template name="callBasicDetails">
    <BasicDetails>
      <FormerUPN>
        <xsl:value-of select="/NewDataSet/Table/PreviousNationalUPN" />
      </FormerUPN>
      <PreferredSurname>
        <xsl:value-of select="/NewDataSet/Table/Surname" />
      </PreferredSurname>
      <FormerSurname>
        <xsl:value-of select="/NewDataSet/Table/FormerSurname" />
      </FormerSurname>
      <PreferredForename>
        <xsl:value-of select="/NewDataSet/Table/Firstname" />
      </PreferredForename>
      <MiddleNames>
        <xsl:value-of select="/NewDataSet/Table/OtherNames" />
      </MiddleNames>
      <NCyearActual>
        <xsl:value-of select="/NewDataSet/Table/NCYearActual" />
      </NCyearActual>
      <Ethnicity>
        <xsl:value-of select="/NewDataSet/Table/Ethnicity" />
      </Ethnicity>
      <EthnicitySource>
        <xsl:value-of select="/NewDataSet/Table/EthnicitySource" />
      </EthnicitySource>
      <xsl:call-template name="callLanguages" />
      <FSMeligible>
        <xsl:value-of select="/NewDataSet/Table/FSMeligible" />
      </FSMeligible>
      <MedicalFlag>
        <xsl:value-of select="/NewDataSet/Table/MedicalFlagQualifier" />
      </MedicalFlag>
      <EnrolStatus>
        <xsl:value-of select="/NewDataSet/Table/EnrolStatus" />
      </EnrolStatus>
      <Statemented>
        <xsl:value-of select="/NewDataSet/Table/SENStatemented" />
      </Statemented>
    </BasicDetails>
  </xsl:template>

  <xsl:template name="callLanguages">
    <Languages>
      <Type>
        <LanguageQualifier>
          <xsl:value-of select="/NewDataSet/Table/LanguageQualifier" />
        </LanguageQualifier>
        <Language>
          <xsl:value-of select="/NewDataSet/Table/Language" />
        </Language>
      </Type>
    </Languages>
  </xsl:template>


  <xsl:template name="callAddress">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="/NewDataSet/Table/AddressLine1" />
        </Line1>
        <Line2>
          <xsl:value-of select="/NewDataSet/Table/AddressLine2" />
        </Line2>
        <Line3>
          <xsl:value-of select="/NewDataSet/Table/AddressLine3" />
        </Line3>
        <Line4>
          <xsl:value-of select="/NewDataSet/Table/Town" />
        </Line4>
        <Line5>
          <xsl:value-of select="/NewDataSet/Table/County" />
        </Line5>
        <Postcode>
          <xsl:value-of select="/NewDataSet/Table/PostCode" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>
    
  <xsl:template name="callPGContacts">
    <Contacts>
      <xsl:call-template name="callPGContact1" />
      <xsl:call-template name="callPGContact2" />
      <xsl:call-template name="callPGContact3" />
    </Contacts>
  </xsl:template>

  <xsl:template name="callPGContact1">
    <Contact>
      <Title>
        <xsl:value-of select="/NewDataSet/Table/studContTitle1" />
      </Title>
      <Surname>
        <xsl:value-of select="/NewDataSet/Table/studContSurname1" />
      </Surname>
      <Forename>
        <xsl:value-of select="/NewDataSet/Table/studContFirstname1" />
      </Forename>
      <Gender>
        <xsl:value-of select="/NewDataSet/Table/studContSex1" />
      </Gender>
      <Relationship>
        <xsl:value-of select="/NewDataSet/Table/studContRelationship1" />
      </Relationship>
      <Responsibility>
        <xsl:value-of select="/NewDataSet/Table/studContResponsibility1" />
      </Responsibility>
      <xsl:call-template name="callPGAddress1" />
      <xsl:call-template name="callPGPhones1" />
      <xsl:call-template name="callPGSuppInfo1" />
    </Contact>
  </xsl:template>

  <xsl:template name="callPGAddress1">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="/NewDataSet/Table/studContAddress11" />
        </Line1>
        <Line2>
          <xsl:value-of select="/NewDataSet/Table/studContAddress21" />
        </Line2>
        <Line3>
          <xsl:value-of select="/NewDataSet/Table/studContAddress31" />
        </Line3>
        <Line4>
          <xsl:value-of select="/NewDataSet/Table/studContTown1" />
        </Line4>
        <Line5>
          <xsl:value-of select="/NewDataSet/Table/studContCountry1" />
        </Line5>
        <Postcode>
          <xsl:value-of select="/NewDataSet/Table/studContPostCode1" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>

  <xsl:template name="callPGPhones1">
    <Phones>
      <xsl:call-template name="callPGPhoneType1" />
      <xsl:call-template name="callPGPhoneType2" />
      <xsl:call-template name="callPGPhoneType3" />
    </Phones>
  </xsl:template>

  <xsl:template name="callPGPhoneType1">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType11" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/studContMobile1" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType2">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType12" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/stuContWorkPhone1" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType3">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType13" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/studContFax1" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGSuppInfo1">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="/NewDataSet/Table/studContSuppID1" />
      </SuppID>
      <RelLnkParentNo>
        <xsl:value-of select="/NewDataSet/Table/studContParentNum1" />
      </RelLnkParentNo>
    </SuppInfo>
  </xsl:template>

  <xsl:template name="callPGContact2">
    <Contact>
      <Title>
        <xsl:value-of select="/NewDataSet/Table/studContTitle2" />
      </Title>
      <Surname>
        <xsl:value-of select="/NewDataSet/Table/studContSurname2" />
      </Surname>
      <Forename>
        <xsl:value-of select="/NewDataSet/Table/studContFirstname2" />
      </Forename>
      <Gender>
        <xsl:value-of select="/NewDataSet/Table/studContSex2" />
      </Gender>
      <Relationship>
        <xsl:value-of select="/NewDataSet/Table/studContRelationship2" />
      </Relationship>
      <Responsibility>
        <xsl:value-of select="/NewDataSet/Table/studContResponsibility2" />
      </Responsibility>
      <xsl:call-template name="callPGAddress2" />
      <xsl:call-template name="callPGPhones2" />
      <xsl:call-template name="callPGSuppInfo2" />
    </Contact>
  </xsl:template>

  <xsl:template name="callPGAddress2">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="/NewDataSet/Table/studContAddress12" />
        </Line1>
        <Line2>
          <xsl:value-of select="/NewDataSet/Table/studContAddress22" />
        </Line2>
        <Line3>
          <xsl:value-of select="/NewDataSet/Table/studContAddress32" />
        </Line3>
        <Line4>
          <xsl:value-of select="/NewDataSet/Table/studContTown2" />
        </Line4>
        <Line5>
          <xsl:value-of select="/NewDataSet/Table/studContCountry2" />
        </Line5>
        <Postcode>
          <xsl:value-of select="/NewDataSet/Table/studContPostCode2" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>

  <xsl:template name="callPGPhones2">
    <Phones>
      <xsl:call-template name="callPGPhoneType21" />
      <xsl:call-template name="callPGPhoneType22" />
      <xsl:call-template name="callPGPhoneType23" />
    </Phones>
  </xsl:template>

  <xsl:template name="callPGPhoneType21">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType12" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/studContMobile2" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType22">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType22" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/stuContWorkPhone2" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType23">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType23" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/studContFax2" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGSuppInfo2">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="/NewDataSet/Table/studContSuppID2" />
      </SuppID>
      <RelLnkParentNo>
        <xsl:value-of select="/NewDataSet/Table/studContParentNum2" />
      </RelLnkParentNo>
    </SuppInfo>
  </xsl:template>

  <xsl:template name="callPGContact3">
    <Contact>
      <Title>
        <xsl:value-of select="/NewDataSet/Table/studContTitle3" />
      </Title>
      <Surname>
        <xsl:value-of select="/NewDataSet/Table/studContSurname3" />
      </Surname>
      <Forename>
        <xsl:value-of select="/NewDataSet/Table/studContFirstname3" />
      </Forename>
      <Gender>
        <xsl:value-of select="/NewDataSet/Table/studContSex3" />
      </Gender>
      <Relationship>
        <xsl:value-of select="/NewDataSet/Table/studContRelationship3" />
      </Relationship>
      <Responsibility>
        <xsl:value-of select="/NewDataSet/Table/studContResponsibility3" />
      </Responsibility>
      <xsl:call-template name="callPGAddress3" />
      <xsl:call-template name="callPGPhones3" />
      <xsl:call-template name="callPGSuppInfo3" />
    </Contact>
  </xsl:template>

  <xsl:template name="callPGAddress3">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="/NewDataSet/Table/studContAddress13" />
        </Line1>
        <Line2>
          <xsl:value-of select="/NewDataSet/Table/studContAddress23" />
        </Line2>
        <Line3>
          <xsl:value-of select="/NewDataSet/Table/studContAddress33" />
        </Line3>
        <Line4>
          <xsl:value-of select="/NewDataSet/Table/studContTown3" />
        </Line4>
        <Line5>
          <xsl:value-of select="/NewDataSet/Table/studContCountry3" />
        </Line5>
        <Postcode>
          <xsl:value-of select="/NewDataSet/Table/studContPostCode3" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>

  <xsl:template name="callPGPhones3">
    <Phones>
      <xsl:call-template name="callPGPhoneType31" />
      <xsl:call-template name="callPGPhoneType32" />
      <xsl:call-template name="callPGPhoneType33" />
    </Phones>
  </xsl:template>

  <xsl:template name="callPGPhoneType31">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType13" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/studContMobile13" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType32">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType23" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/stuContWorkPhone3" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType33">
    <Phone>
      <PhoneType>
        <xsl:value-of select="/NewDataSet/Table/stuContType33" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="/NewDataSet/Table/studContFax3" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGSuppInfo3">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="/NewDataSet/Table/studContSuppID3" />
      </SuppID>
      <RelLnkParentNo>
        <xsl:value-of select="/NewDataSet/Table/studContParentNum3" />
      </RelLnkParentNo>
    </SuppInfo>
  </xsl:template>

  <xsl:template name="callSchoolHistory">
    <SchoolHistory>
      <xsl:for-each select="/NewDataSet/Table">
        <School>
          <LEA>
            <xsl:value-of select="MovPrevSchoolLEA" />
          </LEA>
          <Estab>
            <xsl:value-of select="MovPrevSchoolEstab" />
          </Estab>
          <SchoolName>
            <xsl:value-of select="MovPrevSchoolName" />
          </SchoolName>
          <EntryDate>
            <xsl:value-of select="MovEntryDate" />
          </EntryDate>
          <LeavingDate>
            <xsl:value-of select="MovLeaveDate" />
          </LeavingDate>
          <LeavingReason>
            <xsl:value-of select="MovReasonForLeavingCode" />
          </LeavingReason>
        </School>
      </xsl:for-each>
    </SchoolHistory>
  </xsl:template>
  
  <xsl:template name="callSuppInfo">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="/NewDataSet/Table/SupplierID" />
      </SuppID>
      <StudentDetails>
        <SSABSANumber>
          <xsl:value-of select="/NewDataSet/Table/SSABSANumber" />
        </SSABSANumber>
      </StudentDetails>
    </SuppInfo>
  </xsl:template>
</xsl:stylesheet>

Open in new window

SPOutput-or-Dataset-sample.xls
0
Comment
Question by:jsuanque
  • 11
  • 7
  • 2
20 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33609270
mmh, there is more to it here than just introducing muenchian.

Allthrough your stylesheet you are using absolute paths where you should now use relative paths
Named templates is not the best mechanism for handling nodes,
you should use apply-templates and match templates with it.
I advice you to completely rerwrite this stylesheet,
I am happy to help, but could you provide an expected end result?
0
 

Author Comment

by:jsuanque
ID: 33609723
Hello Gertone,
Thanks in advance for bearing with me...anyway, I've attached a sample output based on the previously attached data set.

Sample-Output.xml
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33609730
Also, Looking at your database export.
This is excel, not XML, can you pass on the source XML for your XSLT as well?

I assume you are combining multiple tables in your database export
please note that XML is hierarchcal as a data format

you are doing something like this
<address1>...
<address2>...
<address3>...

where this would be easier to work from
<addresses>
   <address>...
   <address>...
   <address>...
not necessarily having exactly three address fields, but exactly as many as you have values
There are many ways in which you could improve your export procedure.
Basically, if you do the export based on the StudentID and build up the XML from there,
you would not even need Muenchian.

It all depends on where you want to do the action,
but it seems you allready end up with sort of a complex SQL to get this one table out.
You might consider moving some action inside the database.
XQuery might be a helpfull filter... all depends on how comfortable you are with the DB side of things
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:jsuanque
ID: 33609971
Hello Gertone,
Actually the data set output is not in XML or excel, it is generated via a stored procedure i just placed the value on excel for the purpose of showing what i retrieved from DB or the stored procedure.
Also the tags represented(including its indentation) are essential coz eventually the output XML (or CTF) is to be used as an import file for a very old application (Not sure if you have heard about OMNIS).
Thus your suggestion might work in generation of the output but unfortunately it won't work once imported to OMNIS application.
As for XQuery, might be a good venue learning it but at this stage i might not have enough time.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33610690
I understand that you want to stick to the old plan.

I am a bit puzzled about the data not being XML.
If it ain't XML the XSLT will not work

so you are importing the result in Omnis?
I thought Omnis was a  form client application.
Does information go back to the database through Omnis?

I see that I can deal with the Excel, that will likely mean exporting and transforming myself :-)
0
 

Author Comment

by:jsuanque
ID: 33611192
Hello Gertone,
As for OMNIS, our setup here is that all schools have thier own OMNIS (application/database) in short distributed system. And basic information (student) are being fed into our central system/db (sqlserver), then this information are available for viewing and downlaod if other school needs it. And this facility (i mean XMl generator you're trying to help me) will give them an option to download to their local OMNIS if one student move from other school to their school. using the web app front end

Honestly, pretty much blank at this stage....there's so much legacy processes done already and yes i do recognize that there are better ways but just don't want to tread to it or have the experience and skill to tackle  it. :)
0
 

Author Comment

by:jsuanque
ID: 33611238
Hello Gertone,
Maybe i'm not understanding when you state 'XML' source but to my limited knowledge.
the XSLT template is just used to transform the output generated from stored procedure into a format that OMNIS could process.
I'll try to provide you the code tomorrow of how the XSLT is being utilised.
0
 

Author Comment

by:jsuanque
ID: 33613843
Hello Gertone,
This the  code which utilizes the xslt.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CENTRIS_SMIConnectionString").ConnectionString)
            'Dim cmd As New Data.SqlClient.SqlCommand("SMI_P100_StudentMovementHistory_Download", conn)
            Dim cmd As New Data.SqlClient.SqlCommand("SMI_P100_XML_Download", conn)
            cmd.CommandType = Data.CommandType.StoredProcedure
            conn.Open()
            'Add parameters (NationalUPN) selected from form for WHERE clause
            cmd.Parameters.Add("@NationalUPN", Data.SqlDbType.VarChar)
            cmd.Parameters("@NationalUPN").Value = DropDownList2.SelectedValue.ToString

            'Add parameters (AcademicYear) selected from form for WHERE clause
            cmd.Parameters.Add("@AcademicYear", Data.SqlDbType.Int)
            cmd.Parameters("@AcademicYear").Value = DropDownList3.SelectedValue.ToString

            'Add parameters (SchoolDestination LEA Code) selected from form for WHERE clause
            cmd.Parameters.Add("@SchoolDestLEA", Data.SqlDbType.VarChar)
            cmd.Parameters("@SchoolDestLEA").Value = LEACode_TB.Text

            'Add parameters (SchoolDestination DfEE Code) selected from form for WHERE clause
            cmd.Parameters.Add("@SchoolDestDfEE", Data.SqlDbType.VarChar)
            cmd.Parameters("@SchoolDestDfEE").Value = DfEECode_TB.Text   

            Dim da As New Data.SqlClient.SqlDataAdapter(cmd)
            Dim ds As New Data.DataSet
            da.Fill(ds)
            Dim nowString As String = Now.ToString("dd-MM-yyyy hh.mm.ss")
            Dim fileName As String = "C:\Documents and Settings\jackson.suanque\My Documents\Visual Studio 2008\Projects\SMI_Student_Download_BAK\Download\TEMP\000LLL_CTF_SMI_" & nowString & ".xml"
            'Dim fileName As String = "C:\Documents and Settings\jackson.suanque.NTSCHOOLS\Desktop\MySMIStudentTrackerExport_" & nowString & ".xml"
            ds.WriteXml(fileName)
            Dim doc As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(ds)
            Dim transform As New System.Xml.Xsl.XslCompiledTransform()
            transform.Load("C:\Documents and Settings\jackson.suanque\My Documents\Visual Studio 2008\Projects\SMI_Student_Download_BAK\SMI_XML_Download_Multi_1.xslt")
            'transform.Load("C:\Documents and Settings\jackson.suanque\My Documents\Visual Studio 2008\Projects\SMI_Student_Download_BAK\SMI_XML_Download_Multi.xslt")
            'transform.Load(Server.MapPath("SMI_Tracker_Download1.xsl"))
            Dim writer As System.Xml.XmlTextWriter = New System.Xml.XmlTextWriter(fileName, System.Text.Encoding.UTF8)
            'Writes the XML declaration!!!
            writer.WriteStartDocument()
            writer.Formatting = System.Xml.Formatting.Indented
            writer.Indentation = 2
            transform.Transform(doc, Nothing, writer)
        End Using
    End Sub

Open in new window

0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 25 total points
ID: 33652786
Not sure how your new pupils XML looks like:

It could probably use an for-each like in code (you might need to look at exact XML passed to XSLT) on your side, and tweak it, in case the XML generated for multiplle pupils is of different structure.

Basically I removed the full path while getting values and used a for-each. This will work for single node also, and be faster executing compared to full path to each element.

Thanks
  <xsl:template name="callPupil">
    <xsl:for-each select="/NewDataSet/Table">
    <Pupil>
      <UPN>
        <xsl:value-of select="NationalUPN" />
      </UPN>
      <Surname>
        <xsl:value-of select="LegalSurname" />
      </Surname>
      <Forename>
        <xsl:value-of select="Firstname" />
      </Forename>
      <DOB>
        <xsl:value-of select="DOB" />
      </DOB>
      <Gender>
        <xsl:value-of select="Sex" />
      </Gender>
      <xsl:call-template name="callBasicDetails" />
      <xsl:call-template name="callAddress" />
      <xsl:call-template name="callPGContacts" />
      <xsl:call-template name="callSchoolHistory" />
      <xsl:call-template name="callSuppInfo" />
    </Pupil>
</xsl:for-each>
  </xsl:template>

<xsl:template name="callBasicDetails">
    <BasicDetails>
      <FormerUPN>
        <xsl:value-of select="PreviousNationalUPN" />
      </FormerUPN>
      <PreferredSurname>
        <xsl:value-of select="Surname" />
      </PreferredSurname>
      <FormerSurname>
        <xsl:value-of select="FormerSurname" />
      </FormerSurname>
      <PreferredForename>
        <xsl:value-of select="Firstname" />
      </PreferredForename>
      <MiddleNames>
        <xsl:value-of select="OtherNames" />
      </MiddleNames>
      <NCyearActual>
        <xsl:value-of select="NCYearActual" />
      </NCyearActual>
      <Ethnicity>
        <xsl:value-of select="Ethnicity" />
      </Ethnicity>
      <EthnicitySource>
        <xsl:value-of select="EthnicitySource" />
      </EthnicitySource>
      <xsl:call-template name="callLanguages" />
      <FSMeligible>
        <xsl:value-of select="FSMeligible" />
      </FSMeligible>
      <MedicalFlag>
        <xsl:value-of select="MedicalFlagQualifier" />
      </MedicalFlag>
      <EnrolStatus>
        <xsl:value-of select="EnrolStatus" />
      </EnrolStatus>
      <Statemented>
        <xsl:value-of select="SENStatemented" />
      </Statemented>
    </BasicDetails>
  </xsl:template>

  <xsl:template name="callLanguages">
    <Languages>
      <Type>
        <LanguageQualifier>
          <xsl:value-of select="LanguageQualifier" />
        </LanguageQualifier>
        <Language>
          <xsl:value-of select="Language" />
        </Language>
      </Type>
    </Languages>
  </xsl:template>


  <xsl:template name="callAddress">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="AddressLine1" />
        </Line1>
        <Line2>
          <xsl:value-of select="AddressLine2" />
        </Line2>
        <Line3>
          <xsl:value-of select="AddressLine3" />
        </Line3>
        <Line4>
          <xsl:value-of select="Town" />
        </Line4>
        <Line5>
          <xsl:value-of select="County" />
        </Line5>
        <Postcode>
          <xsl:value-of select="PostCode" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>
    
  <xsl:template name="callPGContacts">
    <Contacts>
      <xsl:call-template name="callPGContact1" />
      <xsl:call-template name="callPGContact2" />
      <xsl:call-template name="callPGContact3" />
    </Contacts>
  </xsl:template>

  <xsl:template name="callPGContact1">
    <Contact>
      <Title>
        <xsl:value-of select="studContTitle1" />
      </Title>
      <Surname>
        <xsl:value-of select="studContSurname1" />
      </Surname>
      <Forename>
        <xsl:value-of select="studContFirstname1" />
      </Forename>
      <Gender>
        <xsl:value-of select="studContSex1" />
      </Gender>
      <Relationship>
        <xsl:value-of select="studContRelationship1" />
      </Relationship>
      <Responsibility>
        <xsl:value-of select="studContResponsibility1" />
      </Responsibility>
      <xsl:call-template name="callPGAddress1" />
      <xsl:call-template name="callPGPhones1" />
      <xsl:call-template name="callPGSuppInfo1" />
    </Contact>
  </xsl:template>

  <xsl:template name="callPGAddress1">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="studContAddress11" />
        </Line1>
        <Line2>
          <xsl:value-of select="studContAddress21" />
        </Line2>
        <Line3>
          <xsl:value-of select="studContAddress31" />
        </Line3>
        <Line4>
          <xsl:value-of select="studContTown1" />
        </Line4>
        <Line5>
          <xsl:value-of select="studContCountry1" />
        </Line5>
        <Postcode>
          <xsl:value-of select="studContPostCode1" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>

  <xsl:template name="callPGPhones1">
    <Phones>
      <xsl:call-template name="callPGPhoneType1" />
      <xsl:call-template name="callPGPhoneType2" />
      <xsl:call-template name="callPGPhoneType3" />
    </Phones>
  </xsl:template>

  <xsl:template name="callPGPhoneType1">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType11" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="studContMobile1" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType2">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType12" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="stuContWorkPhone1" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType3">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType13" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="studContFax1" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGSuppInfo1">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="studContSuppID1" />
      </SuppID>
      <RelLnkParentNo>
        <xsl:value-of select="studContParentNum1" />
      </RelLnkParentNo>
    </SuppInfo>
  </xsl:template>

  <xsl:template name="callPGContact2">
    <Contact>
      <Title>
        <xsl:value-of select="studContTitle2" />
      </Title>
      <Surname>
        <xsl:value-of select="studContSurname2" />
      </Surname>
      <Forename>
        <xsl:value-of select="studContFirstname2" />
      </Forename>
      <Gender>
        <xsl:value-of select="studContSex2" />
      </Gender>
      <Relationship>
        <xsl:value-of select="studContRelationship2" />
      </Relationship>
      <Responsibility>
        <xsl:value-of select="studContResponsibility2" />
      </Responsibility>
      <xsl:call-template name="callPGAddress2" />
      <xsl:call-template name="callPGPhones2" />
      <xsl:call-template name="callPGSuppInfo2" />
    </Contact>
  </xsl:template>

  <xsl:template name="callPGAddress2">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="studContAddress12" />
        </Line1>
        <Line2>
          <xsl:value-of select="studContAddress22" />
        </Line2>
        <Line3>
          <xsl:value-of select="studContAddress32" />
        </Line3>
        <Line4>
          <xsl:value-of select="studContTown2" />
        </Line4>
        <Line5>
          <xsl:value-of select="studContCountry2" />
        </Line5>
        <Postcode>
          <xsl:value-of select="studContPostCode2" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>

  <xsl:template name="callPGPhones2">
    <Phones>
      <xsl:call-template name="callPGPhoneType21" />
      <xsl:call-template name="callPGPhoneType22" />
      <xsl:call-template name="callPGPhoneType23" />
    </Phones>
  </xsl:template>

  <xsl:template name="callPGPhoneType21">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType12" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="studContMobile2" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType22">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType22" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="stuContWorkPhone2" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType23">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType23" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="studContFax2" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGSuppInfo2">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="studContSuppID2" />
      </SuppID>
      <RelLnkParentNo>
        <xsl:value-of select="studContParentNum2" />
      </RelLnkParentNo>
    </SuppInfo>
  </xsl:template>

  <xsl:template name="callPGContact3">
    <Contact>
      <Title>
        <xsl:value-of select="studContTitle3" />
      </Title>
      <Surname>
        <xsl:value-of select="studContSurname3" />
      </Surname>
      <Forename>
        <xsl:value-of select="studContFirstname3" />
      </Forename>
      <Gender>
        <xsl:value-of select="studContSex3" />
      </Gender>
      <Relationship>
        <xsl:value-of select="studContRelationship3" />
      </Relationship>
      <Responsibility>
        <xsl:value-of select="studContResponsibility3" />
      </Responsibility>
      <xsl:call-template name="callPGAddress3" />
      <xsl:call-template name="callPGPhones3" />
      <xsl:call-template name="callPGSuppInfo3" />
    </Contact>
  </xsl:template>

  <xsl:template name="callPGAddress3">
    <Address>
      <AddressLines>
        <Line1>
          <xsl:value-of select="studContAddress13" />
        </Line1>
        <Line2>
          <xsl:value-of select="studContAddress23" />
        </Line2>
        <Line3>
          <xsl:value-of select="studContAddress33" />
        </Line3>
        <Line4>
          <xsl:value-of select="studContTown3" />
        </Line4>
        <Line5>
          <xsl:value-of select="studContCountry3" />
        </Line5>
        <Postcode>
          <xsl:value-of select="studContPostCode3" />
        </Postcode>
      </AddressLines>
    </Address>
  </xsl:template>

  <xsl:template name="callPGPhones3">
    <Phones>
      <xsl:call-template name="callPGPhoneType31" />
      <xsl:call-template name="callPGPhoneType32" />
      <xsl:call-template name="callPGPhoneType33" />
    </Phones>
  </xsl:template>

  <xsl:template name="callPGPhoneType31">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType13" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="studContMobile13" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType32">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType23" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="stuContWorkPhone3" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGPhoneType33">
    <Phone>
      <PhoneType>
        <xsl:value-of select="stuContType33" />
      </PhoneType>
      <PhoneNo>
        <xsl:value-of select="studContFax3" />
      </PhoneNo>
    </Phone>
  </xsl:template>

  <xsl:template name="callPGSuppInfo3">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="studContSuppID3" />
      </SuppID>
      <RelLnkParentNo>
        <xsl:value-of select="studContParentNum3" />
      </RelLnkParentNo>
    </SuppInfo>
  </xsl:template>

  <xsl:template name="callSchoolHistory">
    <SchoolHistory>
      <xsl:for-each select="/NewDataSet/Table">
        <School>
          <LEA>
            <xsl:value-of select="MovPrevSchoolLEA" />
          </LEA>
          <Estab>
            <xsl:value-of select="MovPrevSchoolEstab" />
          </Estab>
          <SchoolName>
            <xsl:value-of select="MovPrevSchoolName" />
          </SchoolName>
          <EntryDate>
            <xsl:value-of select="MovEntryDate" />
          </EntryDate>
          <LeavingDate>
            <xsl:value-of select="MovLeaveDate" />
          </LeavingDate>
          <LeavingReason>
            <xsl:value-of select="MovReasonForLeavingCode" />
          </LeavingReason>
        </School>
      </xsl:for-each>
    </SchoolHistory>
  </xsl:template>
  
  <xsl:template name="callSuppInfo">
    <SuppInfo>
      <SuppID>
        <xsl:value-of select="SupplierID" />
      </SuppID>
      <StudentDetails>
        <SSABSANumber>
          <xsl:value-of select="SSABSANumber" />
        </SSABSANumber>
      </StudentDetails>
    </SuppInfo>
  </xsl:template>

Open in new window

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 33652788
btw.. I have not used Muenchian grouping, unless you really need some sort of grouping, from XML, it is not needed.

thanks.
0
 

Author Comment

by:jsuanque
ID: 33872329
Hello Valli_an,
Was off for a month long holiday.
Anyway, the for-each you suggested doesn't work on my requirement since the source data has multiple rows for a particular student thus the output reuired which is one for each student is not staisfied.
 The source looks like this (trimmed it for emphasis purposes only)....


<CTFPupilData>
   <Pupil>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
      <LEACode>001</LEACode>
      <DfEECode>5001</DfEECode>
      <EntryDate>01/01/2005</EntryDate>
      <LeaveDate>30/06/2005</LeaveDate>
      <ReasonForLeaving>01</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
      <LEACode>002</LEACode>
      <DfEECode>1011</DfEECode>
      <EntryDate>01/07/2005</EntryDate>
      <LeaveDate>31/12/2005</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
      <LEACode>002</LEACode>
      <DfEECode>1011</DfEECode>
      <EntryDate>01/01/2006</EntryDate>
      <LeaveDate>31/12/2009</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>X001222123456</NationalUPN>
      <Surname>Doe</Surname>
      <Firstname>Sally</Firstname>
      ...
      <SchoolHistory>
      <LEACode>003</LEACode>
      <DfEECode>4001</DfEECode>
      <EntryDate>01/01/2007</EntryDate>
      <LeaveDate>31/12/2008</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>X001222123456</NationalUPN>
      <Surname>Doe</Surname>
      <Firstname>Sally</Firstname>
      ...
      <SchoolHistory>
      <LEACode>003</LEACode>
      <DfEECode>2019</DfEECode>
      <EntryDate>01/01/2008</EntryDate>
      <LeaveDate>30/06/2010</LeaveDate>
      <ReasonForLeaving>01</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>B001234567890</NationalUPN>
      <Surname>Jones</Surname>
      <Firstname>Eric</Firstname>
      ...
      <SchoolHistory>
      <LEACode>002</LEACode>
      <DfEECode>5001</DfEECode>
      <EntryDate>30/01/2007</EntryDate>
      <LeaveDate>30/06/2010</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
</CTFPupilData>
0
 

Author Comment

by:jsuanque
ID: 33872339
And the output i wanted should look like this...


<CTFPupilData>
   <Pupil>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
         <LEACode>001</LEACode>
         <DfEECode>5001</DfEECode>
         <EntryDate>01/01/2005</EntryDate>
         <LeaveDate>30/06/2005</LeaveDate>
         <ReasonForLeaving>01</ReasonForLeaving>
      </SchoolHistory>
      <SchoolHistory>
         <LEACode>002</LEACode>
         <DfEECode>1011</DfEECode>
         <EntryDate>01/07/2005</EntryDate>
         <LeaveDate>31/12/2005</LeaveDate>
         <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
      <SchoolHistory>
         <LEACode>002</LEACode>
         <DfEECode>1011</DfEECode>
         <EntryDate>01/01/2006</EntryDate>
         <LeaveDate>31/12/2009</LeaveDate>
         <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>X001222123456</NationalUPN>
      <Surname>Doe</Surname>
      <Firstname>Sally</Firstname>
      ...
      <SchoolHistory>
         <LEACode>003</LEACode>
         <DfEECode>4001</DfEECode>
         <EntryDate>01/01/2007</EntryDate>
         <LeaveDate>31/12/2008</LeaveDate>
         <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
      <SchoolHistory>
         <LEACode>003</LEACode>
         <DfEECode>2019</DfEECode>
         <EntryDate>01/01/2008</EntryDate>
         <LeaveDate>30/06/2010</LeaveDate>
         <ReasonForLeaving>01</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <NationalUPN>B001234567890</NationalUPN>
      <Surname>Jones</Surname>
      <Firstname>Eric</Firstname>
      ...
      <SchoolHistory>
         <LEACode>002</LEACode>
         <DfEECode>5001</DfEECode>
         <EntryDate>30/01/2007</EntryDate>
         <LeaveDate>30/06/2010</LeaveDate>
         <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
</CTFPupilData>
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33873008
Aha, I now finally see the XML source that I have wanted to see for months

Of course you need muenchian, that was the purpose of the whole question, no?

I have taken out the source as you just posted and have not integrated with your earlier stylesheet
But here is all the XSLT you need for the grouping part
<?xml version="1.0" encoding="iso-8859-1" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="xml" version="1.0" indent="yes" encoding="iso-8859-1" />

    <xsl:key name="pupil" match="Pupil" use="NationalUPN"/>
    
    <xsl:template match="CTFPupilData">
        <xsl:apply-templates select="Pupil[generate-id() = generate-id(key('pupil', NationalUPN)[1])]"/>
    </xsl:template>
    
    <xsl:template match="Pupil">
        <Pupil>
            <xsl:copy-of select="NationalUPN | Surname | Firstname"/>
            <xsl:for-each select="key('pupil', NationalUPN)">
                <xsl:copy-of select="SchoolHistory"/>
            </xsl:for-each>
        </Pupil>
    </xsl:template>

</xsl:stylesheet>

Open in new window

0
 

Author Comment

by:jsuanque
ID: 33880829
Hello gertone,
Thanks, this works.
Anyway, if i wanted to extend the source xml and output to include header information. Will it be possible....
Source XML....
<CTFPupilData>
   <Pupil>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
      <LEACode>001</LEACode>
      <DfEECode>5001</DfEECode>
      <EntryDate>01/01/2005</EntryDate>
      <LeaveDate>30/06/2005</LeaveDate>
      <ReasonForLeaving>01</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
      <LEACode>002</LEACode>
      <DfEECode>1011</DfEECode>
      <EntryDate>01/07/2005</EntryDate>
      <LeaveDate>31/12/2005</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
      <NationalUPN>A001001001001</NationalUPN>
      <Surname>Smith</Surname>
      <Firstname>John</Firstname>
      ...
      <SchoolHistory>
      <LEACode>002</LEACode>
      <DfEECode>1011</DfEECode>
      <EntryDate>01/01/2006</EntryDate>
      <LeaveDate>31/12/2009</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
      <NationalUPN>X001222123456</NationalUPN>
      <Surname>Doe</Surname>
      <Firstname>Sally</Firstname>
      ...
      <SchoolHistory>
      <LEACode>003</LEACode>
      <DfEECode>4001</DfEECode>
      <EntryDate>01/01/2007</EntryDate>
      <LeaveDate>31/12/2008</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
      <NationalUPN>X001222123456</NationalUPN>
      <Surname>Doe</Surname>
      <Firstname>Sally</Firstname>
      ...
      <SchoolHistory>
      <LEACode>003</LEACode>
      <DfEECode>2019</DfEECode>
      <EntryDate>01/01/2008</EntryDate>
      <LeaveDate>30/06/2010</LeaveDate>
      <ReasonForLeaving>01</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
   <Pupil>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
      <NationalUPN>B001234567890</NationalUPN>
      <Surname>Jones</Surname>
      <Firstname>Eric</Firstname>
      ...
      <SchoolHistory>
      <LEACode>002</LEACode>
      <DfEECode>5001</DfEECode>
      <EntryDate>30/01/2007</EntryDate>
      <LeaveDate>30/06/2010</LeaveDate>
      <ReasonForLeaving>02</ReasonForLeaving>
      </SchoolHistory>
   </Pupil>
</CTFPupilData>
0
 

Author Comment

by:jsuanque
ID: 33880846
And my desired output....


<CTFile>
   <Header>
      <DocumentName>Import File</DocumentName>
      <Version>1.0</Version>
      <SourceLEA>000</SourceLEA>
      <SourceDfEE>FFFF</SourceDfEE>
      <DestLEA>100</DestLEA>
      <DestDfEE>2210</DestDfEE>
   </Header>
   <CTFPupilData>
      <Pupil>
         <NationalUPN>A001001001001</NationalUPN>
         <Surname>Smith</Surname>
         <Firstname>John</Firstname>
         ...
         <SchoolHistory>
            <LEACode>001</LEACode>
            <DfEECode>5001</DfEECode>
            <EntryDate>01/01/2005</EntryDate>
            <LeaveDate>30/06/2005</LeaveDate>
            <ReasonForLeaving>01</ReasonForLeaving>
         </SchoolHistory>
         <SchoolHistory>
            <LEACode>002</LEACode>
            <DfEECode>1011</DfEECode>
         <EntryDate>01/07/2005</EntryDate>
            <LeaveDate>31/12/2005</LeaveDate>
            <ReasonForLeaving>02</ReasonForLeaving>
         </SchoolHistory>
         <SchoolHistory>
            <LEACode>002</LEACode>
            <DfEECode>1011</DfEECode>
            <EntryDate>01/01/2006</EntryDate>
            <LeaveDate>31/12/2009</LeaveDate>
            <ReasonForLeaving>02</ReasonForLeaving>
         </SchoolHistory>
      </Pupil>
      <Pupil>
         <NationalUPN>X001222123456</NationalUPN>
         <Surname>Doe</Surname>
         <Firstname>Sally</Firstname>
         ...
         <SchoolHistory>
            <LEACode>003</LEACode>
            <DfEECode>4001</DfEECode>
            <EntryDate>01/01/2007</EntryDate>
            <LeaveDate>31/12/2008</LeaveDate>
            <ReasonForLeaving>02</ReasonForLeaving>
         </SchoolHistory>
         <SchoolHistory>
            <LEACode>003</LEACode>
            <DfEECode>2019</DfEECode>
            <EntryDate>01/01/2008</EntryDate>
            <LeaveDate>30/06/2010</LeaveDate>
            <ReasonForLeaving>01</ReasonForLeaving>
         </SchoolHistory>
      </Pupil>
      <Pupil>
         <NationalUPN>B001234567890</NationalUPN>
         <Surname>Jones</Surname>
         <Firstname>Eric</Firstname>
         ...
         <SchoolHistory>
            <LEACode>002</LEACode>
            <DfEECode>5001</DfEECode>
            <EntryDate>30/01/2007</EntryDate>
            <LeaveDate>30/06/2010</LeaveDate>
            <ReasonForLeaving>02</ReasonForLeaving>
         </SchoolHistory>
      </Pupil>
   </CTFPupilData>
</CTFile>
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 475 total points
ID: 33882636
should not be too difficult
<?xml version="1.0" encoding="iso-8859-1" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" version="1.0" indent="yes" encoding="iso-8859-1" />
    
    <xsl:key name="pupil" match="Pupil" use="NationalUPN"/>
    
    <xsl:template match="CTFPupilData">
        <CTFile>
            <Header>
                <xsl:for-each select="Pupil[1]">
                    <xsl:copy-of select="DocumentName | Version | SourceLEA | SourceDfEE | DestLEA | DestDfEE"/>
                </xsl:for-each>
            </Header>
            <CTFPupilData>
                <xsl:apply-templates select="Pupil[generate-id() = generate-id(key('pupil', NationalUPN)[1])]"/>
            </CTFPupilData>
        </CTFile>
    </xsl:template>
    
    <xsl:template match="Pupil">
        <Pupil>
            <xsl:copy-of select="NationalUPN | Surname | Firstname"/>
            <xsl:for-each select="key('pupil', NationalUPN)">
                <xsl:copy-of select="SchoolHistory"/>
            </xsl:for-each>
        </Pupil>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window

0
 

Author Comment

by:jsuanque
ID: 33935245
Hello Gertone,
You make it sound so simple :-)
Unfortunately, i'm having a new error..

System.InvalidOperationException was unhandled by user code
  Message="Token Content in state Prolog would result in an invalid XML document."
  Source="System.Xml"...


Error.jpg
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 33993684
well, can you post the full XSLT you use?
The one I sent was tested, so you either changed it or you are calling it the wrong way
0
 

Author Closing Comment

by:jsuanque
ID: 34030946
Hello Gerone,
Thanks the solution works!!!
 I'm only getting this error message if the XML source is from SQL Server 2000 but not from SQL Server 2005 and 2008. I don't know what's causing it nor would be interested in wasting more time to it. At this stage, i'm happy on how it is since we'd be using the newer version of SQL Server (2008R2)

Hello Valli_an,
thanks for your contribution.


Thanks heaps.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34031563
welcome
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CSS: Element name is not scoping properly 4 77
site launch date and last modified date 3 99
React or Angular? 6 81
Download a website to hdd 2 82
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

828 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