[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

Need help on Muenchian grouping syntax

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
jsuanque
Asked:
jsuanque
  • 11
  • 7
  • 2
2 Solutions
 
Geert BormansCommented:
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
 
jsuanqueAuthor Commented:
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
 
Geert BormansCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jsuanqueAuthor Commented:
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
 
Geert BormansCommented:
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
 
jsuanqueAuthor Commented:
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
 
jsuanqueAuthor Commented:
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
 
jsuanqueAuthor Commented:
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
 
Valliappan ANCommented:
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
 
Valliappan ANCommented:
btw.. I have not used Muenchian grouping, unless you really need some sort of grouping, from XML, it is not needed.

thanks.
0
 
jsuanqueAuthor Commented:
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
 
jsuanqueAuthor Commented:
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
 
Geert BormansCommented:
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
 
jsuanqueAuthor Commented:
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
 
jsuanqueAuthor Commented:
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
 
Geert BormansCommented:
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
 
jsuanqueAuthor Commented:
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
 
Geert BormansCommented:
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
 
jsuanqueAuthor Commented:
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
 
Geert BormansCommented:
welcome
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now