Solved

Need help on Muenchian grouping  syntax

Posted on 2010-09-05
20
515 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
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)

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now