The following template generates sql. However, there may or may not be
commas at the end of each statement and this is throwing my database
adapter off.
ie. this may occur which throws an error - ,attribute,)
I need to eliminate these trailing commas in the output. So far I am
trying to use the replace() function but have had no luck. Is there an
easy way to alter the output after the transform.
Here is the template before I tried to remove the commas.
Regards
Garry
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output omit-xml-declaration="yes"/>
<xsl:template match="/" >
<!-- Top Level Pupil details -->
<xsl:for-each select="CTfile/CTFpupilData/Pupil">
insert into studentdetails(
<xsl:if test="UPN"> upn,</xsl:if>
<xsl:if test="Surname"> surname ,</xsl:if>
<xsl:if test="Forename"> forename , </xsl:if>
<xsl:if test="DOB"> dob,</xsl:if>
<xsl:if test="Gender"> gender,</xsl:if>
<xsl:for-each select="BasicDetails">
<xsl:if test="FormerUPN">formerupn, </xsl:if>
<xsl:if
test="PreferredSurname">preferredsurname,
</xsl:if>
<xsl:if
test="FormerSurname">formersurname,
</xsl:if>
<xsl:if test="PreferredForename">
preferredforename, </xsl:if>
<xsl:if test="MiddleNames"> middlenames,
</xsl:if>
<xsl:if test="NCyearActual"> ncyearactual,
</xsl:if>
<xsl:if test="Ethnicity"> ethnicity,
</xsl:if>
<xsl:if test="EthnicitySource">
ethnicitysource, </xsl:if>
<xsl:if test="FSMeligible">
fsmeligible, </xsl:if>
<xsl:if test="FSMreviewDate">
fsmreviewdate, </xsl:if>
<xsl:if test="MedicalFlag">
medicalflag, </xsl:if>
<xsl:if test="EnrolStatus">
enrolstatus </xsl:if>)
</xsl:for-each>
values( <xsl:if test="UPN">'<xsl:value-of
select="UPN"/>',</xsl:if>
<xsl:if test="Surname">'<xsl:value-of
select="Surname"/>',</xsl:if>
<xsl:if test="Forename">'<xsl:value-of
select="Forename"/>' , </xsl:if>
<xsl:if test="DOB">'<xsl:value-of
select="DOB"/>', </xsl:if>
<xsl:if test="Gender">'<xsl:value-of
select="Gender"/>', </xsl:if>
<xsl:for-each select="BasicDetails">
<xsl:if test="FormerUPN">'<xsl:value-of
select="FormerUPN"/>', </xsl:if>
<xsl:if
test="PreferredSurname">'<xsl:value-of
select="PreferredSurname"/>', </xsl:if>
<xsl:if
test="FormerSurname">'<xsl:value-of
select="FormerSurname"/>', </xsl:if>
<xsl:if
test="PreferredForename">'<xsl:value-of
select="PreferredForename"/>', </xsl:if>
<xsl:if test="MiddleNames">'<xsl:value-of
select="MiddleNames"/>', </xsl:if>
<xsl:if test="NCyearActual">'<xsl:value-of
select="NCyearActual"/>', </xsl:if>
<xsl:if test="Ethnicity">'<xsl:value-of
select="Ethnicity"/>', </xsl:if>
<xsl:if
test="EthnicitySource">'<xsl:value-of
select="EthnicitySource"/>', </xsl:if>
<xsl:if test="FSMeligible">'<xsl:value-of
select="FSMeligible"/>', </xsl:if>
<xsl:if
test="FSMreviewDate">'<xsl:value-of
select="FSMreviewDate"/>', </xsl:if>
<xsl:if test="MedicalFlag">'<xsl:value-of
select="MedicalFlag"/>', </xsl:if>
<xsl:if test="EnrolStatus">'<xsl:value-of
select="EnrolStatus"/>'</xsl:if>);
</xsl:for-each>
<!-- Languages -->
<xsl:for-each select="BasicDetails/Languages/Type">
insert into languages(
<xsl:if
test="LanguageQualifier">languagequalifier,</xsl:if>
<xsl:if test="Language"> language</xsl:if>)
values(
<xsl:if
test="LanguageQualifier">'<xsl:value-of
select="LanguageQualifier"/>'
</xsl:if>
<xsl:if test="Language">'<xsl:value-of
select="Language"/>'</xsl:if>);
</xsl:for-each>
<!-- Looked After -->
<xsl:for-each select="LookedAfter">
insert into lookedafters(
<xsl:if test="InCare"> incare,</xsl:if>
<xsl:if test="CareAuthority">
careauthority</xsl:if>)
values(
<xsl:if test="InCare">'<xsl:value-of
select="InCare"/>', </xsl:if>
<xsl:if
test="CareAuthority">'<xsl:value-of
select="CareAuthority"/>'</xsl:if>);
</xsl:for-each>
<!-- SEN Histories -->
<xsl:for-each select="SENhistory/SEN">
insert into senhistories(
<xsl:if test="StartDate">startdate,</xsl:if>
<xsl:if test="SENstatus"> senstatus</xsl:if>)
values(
<xsl:if test="StartDate">'<xsl:value-of
select="StartDate"/>', </xsl:if>
<xsl:if test="SENstatus">'<xsl:value-of
select="SENstatus"/>'</xsl:if>);
</xsl:for-each>
<!-- SEN types -->
<xsl:for-each
select="/CTfile/CTFpupilData/Pupil/SENhistory/SENneeds/SENneed">
insert into sentypes(
<xsl:if test="SENtypeRank">sentyperank,
</xsl:if>
<xsl:if test="SENtype"> sentype</xsl:if>)
values(
<xsl:if
test="SENtypeRank">'<xsl:value-of
select="SENtypeRank"/>', </xsl:if>
<xsl:if test="SENtype">'<xsl:value-of
select="SENtype"/>'</xsl:if>);
</xsl:for-each>
<!-- BS7666Address -->
insert into addresses(
<xsl:for-each select="Address/BS7666address">
<xsl:if test="SAON"> saon , </xsl:if>
<xsl:if test="PAON"> paon, </xsl:if>
<xsl:if test="Street"> street, </xsl:if>
<xsl:if test="Locality"> locality, </xsl:if>
<xsl:if test="Town"> town, </xsl:if>
<xsl:if test="AdministrativeArea">
administrativearea , </xsl:if>
<xsl:if test="PostTown"> posttown , </xsl:if>
<xsl:if test="Postcode"> postcode, </xsl:if>
<xsl:if test="UPRN"> uprn , </xsl:if>
<xsl:if test="Zip"> zip , </xsl:if>
<xsl:if test="Country"> country, </xsl:if>
<xsl:if test="Easting"> easting , </xsl:if>
<xsl:if test="Northing"> northing, </xsl:if>
</xsl:for-each>
<xsl:for-each select="Address/AddressLines">
<xsl:if test="Line1">
addressline1, </xsl:if>
<xsl:if test="Line2"> addressline2
, </xsl:if>
<xsl:if test="Line3"> addressline3
, </xsl:if>
<xsl:if test="Line4"> addressline4
, </xsl:if>
<xsl:if test="Line5"> addressline5
, </xsl:if>
<xsl:if test="Postcode"> postcode,
</xsl:if>
<xsl:if test="Zip"> zip, </xsl:if>
<xsl:if test="Country"> country
</xsl:if>)
</xsl:for-each>
<!-- AddressLines -->
values( <xsl:for-each select="Address/BS7666address">
<xsl:if test="SAON">'<xsl:value-of
select="SAON"/>', </xsl:if>
<xsl:if test="PAON">'<xsl:value-of
select="PAON"/>', </xsl:if>
<xsl:if test="Street">'<xsl:value-of
select="Street"/>', </xsl:if>
<xsl:if test="Locality">'<xsl:value-of
select="Locality"/>', </xsl:if>
<xsl:if test="Town">'<xsl:value-of
select="Town"/>', </xsl:if>
<xsl:if
test="AdministrativeArea">'<xsl:value-of
select="AdministrativeArea"/>', </xsl:if>
<xsl:if test="PostTown">'<xsl:value-of
select="PostTown" />', </xsl:if>
<xsl:if test="Postcode">'<xsl:value-of
select="Postcode"/>', </xsl:if>
<xsl:if test="UPRN">'<xsl:value-of
select="UPRN"/>', </xsl:if>
<xsl:if test="Zip">'<xsl:value-of
select="Zip"/>', </xsl:if>
<xsl:if test="Country">'<xsl:value-of
select="Country"/>', </xsl:if>
<xsl:if test="Easting">'<xsl:value-of
select="Easting"/>', </xsl:if>
<xsl:if test="Northing">'<xsl:value-of
select="Northing"/>', </xsl:if>
</xsl:for-each>
<xsl:for-each select="Address/AddressLines">
<xsl:if test="Line1">'<xsl:value-of
select="Line1"/>', </xsl:if>
<xsl:if test="Line2">'<xsl:value-of
select="Line2"/>', </xsl:if>
<xsl:if test="Line3">'<xsl:value-of
select="Line3"/>', </xsl:if>
<xsl:if test="Line4">'<xsl:value-of
select="Line4"/>', </xsl:if>
<xsl:if test="Line5">'<xsl:value-of
select="Line5"/>', </xsl:if>
<xsl:if test="Postcode">'<xsl:value-of
select="Postcode" />', </xsl:if>
<xsl:if test="Zip">'<xsl:value-of
select="Zip"/>', </xsl:if>
<xsl:if test="Country">'<xsl:value-of
select="Country"/>'</xsl:if>);
</xsl:for-each>
<!-- Contacts -->
<xsl:for-each select="Contacts/Contact">
insert into contacts(
<xsl:if test="Order"> order, </xsl:if>
<xsl:if test="Title"> title, </xsl:if>
<xsl:if test="Surname"> surname, </xsl:if>
<xsl:if test="Forename"> forename, </xsl:if>
<xsl:if test="MiddleNames"> middlenames ,
</xsl:if>
<xsl:if test="Gender"> gender , </xsl:if>
<xsl:if test="Relationship"> relationship
, </xsl:if>
<xsl:if test="Responsibility">
responsibility, </xsl:if>
<xsl:if
test="Address/AddressAsPupil">addressaspupil,</xsl:if>
<xsl:if
test="Address/AddressLines/Line1">addressline1,</xsl:if>
<xsl:if
test="Address/AddressLines/Line2">addressline2,</xsl:if>
<xsl:if
test="Address/AddressLines/Line3">addressline3,</xsl:if>
<xsl:if
test="Address/AddressLines/Line4">addressline4,</xsl:if>
<xsl:if
test="Address/AddressLines/Line5">addressline5,</xsl:if>
<xsl:if
test="Address/AddressLines/Postcode">postcode,</xsl:if>
<xsl:if
test="Address/AddressLines/Zip">zip,</xsl:if>
<xsl:if
test="Address/AddressLines/Country">country</xsl:if>)
values(
<xsl:if test="Order">'<xsl:value-of
select="Order"/>', </xsl:if>
<xsl:if test="Title">'<xsl:value-of
select="Title"/>', </xsl:if>
<xsl:if test="Surname">'<xsl:value-of
select="Surname"/>', </xsl:if>
<xsl:if test="Forename">'<xsl:value-of
select="Forename"/>', </xsl:if>
<xsl:if test="MiddleNames">'<xsl:value-of
select="MiddleNames"/>', </xsl:if>
<xsl:if test="Gender">'<xsl:value-of
select="Gender"/>', </xsl:if>
<xsl:if test="Relationship">'<xsl:value-of
select="Relationship"/>', </xsl:if>
<xsl:if
test="Responsibility">'<xsl:value-of
select="Responsibility"/>', </xsl:if>
<xsl:if
test="Address/AddressAsPupil">'<xsl:value-of
select="Address/AddressAsPupil"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Line1">'<xsl:value-of
select="Address/AddressLines/Line1"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Line2">'<xsl:value-of
select="Address/AddressLines/Line2"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Line3">'<xsl:value-of
select="Address/AddressLines/Line3"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Line4">'<xsl:value-of
select="Address/AddressLines/Line4"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Line5">'<xsl:value-of
select="Address/AddressLines/Line5"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Postcode">'<xsl:value-of
select="Address/AddressLines/Postcode"
/>',</xsl:if>
<xsl:if test="Address/AddressLines/Zip">'
<xsl:value-of
select="Address/AddressLines/Zip"/>',</xsl:if>
<xsl:if
test="Address/AddressLines/Country">
'<xsl:value-of
select="Address/AddressLines/Country"/>'</xsl:if>);
<!-- Contact Phones-->
<xsl:for-each select="Phones/Phone">
insert into contactphones(
<xsl:if test="PhoneType">
phonetype, </xsl:if>
<xsl:if test="PhoneNo">
phoneno</xsl:if>)
values(
<xsl:if
test="PhoneType">'<xsl:value-of
select="PhoneType"/>', </xsl:if>
<xsl:if
test="PhoneNo">'<xsl:value-of
select="PhoneNo"/>'</xsl:if>);
</xsl:for-each>
</xsl:for-each>
<!-- Attendance -->
<xsl:for-each select="Attendance/YearData">
insert into attendances(
<xsl:if test="Year"> year, </xsl:if>
<xsl:if test="LEA"> lea, </xsl:if>
<xsl:if test="Estab"> estab, </xsl:if>
<xsl:if test="SchoolName"> schoolname,
</xsl:if>
<xsl:if test="SessionsPossible">
sessionspossible, </xsl:if>
<xsl:if test="SessionsAuthorised">
sessionsauthorised, </xsl:if>
<xsl:if test="SessionsAttended">
sessionsattended, </xsl:if>
<xsl:if test="SessionsUnauthorised">
sessionsunauthorised</xsl:if>)
values(
<xsl:if test="Year">'<xsl:value-of
select="Year"/>', </xsl:if>
<xsl:if test="LEA">'<xsl:value-of
select="LEA"/>', </xsl:if>
<xsl:if test="Estab">'<xsl:value-of
select="Estab"/>', </xsl:if>
<xsl:if test="SchoolName">'<xsl:value-of
select="SchoolName"/>', </xsl:if>
<xsl:if
test="SessionsPossible">'<xsl:value-of
select="SessionsPossible"/>', </xsl:if>
<xsl:if
test="SessionsAuthorised">'<xsl:value-of
select="SessionsAuthorised"/>', </xsl:if>
<xsl:if
test="SessionsAttended">'<xsl:value-of
select="SessionsAttended"/>', </xsl:if>
<xsl:if
test="SessionsUnauthorised">'<xsl:value-of
select="SessionsUnauthorised"/>'</xsl:if>);
</xsl:for-each>
<!-- Assessment Key Stage --><!--Stage Assessment
-->
<xsl:for-each
select="StageAssessments/KeyStage/StageAssessment">
insert into assessments(
<xsl:if test="../Stage"> stage, </xsl:if>
<xsl:if test="Locale"> locale , </xsl:if>
<xsl:if test="Year"> year , </xsl:if>
<xsl:if test="KSsubject"> subject , </xsl:if>
<xsl:if test="Method"> method , </xsl:if>
<xsl:if test="Component"> component ,
</xsl:if>
<xsl:if test="ResultStatus"> resultstatus
, </xsl:if>
<xsl:if test="ResultQualifier">
resultqualifier , </xsl:if>
<xsl:if test="Result"> result , </xsl:if>
<xsl:if test="ResultDate"> resultdate
</xsl:if>)
values(
<xsl:if test="../Stage">'<xsl:value-of
select="../Stage"/>', </xsl:if>
<xsl:if test="Locale">'<xsl:value-of
select="Locale"/>', </xsl:if>
<xsl:if test="Year">'<xsl:value-of
select="Year"/>', </xsl:if>
<xsl:if test="KSsubject">'<xsl:value-of
select="KSsubject"/>', </xsl:if>
<xsl:if test="Method">'<xsl:value-of
select="Method"/>', </xsl:if>
<xsl:if test="Component">' <xsl:value-of
select="Component"/>',</xsl:if>
<xsl:if test="ResultStatus">
'<xsl:value-of
select="ResultStatus"/>',</xsl:if>
<xsl:if
test="ResultQualifier">'<xsl:value-of
select="ResultQualifier"/>',</xsl:if>
<xsl:if test="Result">'<xsl:value-of
select="Result"/>',</xsl:if>
<xsl:if test="ResultDate">'<xsl:value-of
select="ResultDate"/>'</xsl:if>);
</xsl:for-each>
<!--School History -->
<xsl:for-each select="SchoolHistory/School">
insert into schoolhistories(
<xsl:if test="LEA"> lea , </xsl:if>
<xsl:if test="Estab"> estab , </xsl:if>
<xsl:if test="SchoolName"> schoolname ,
</xsl:if>
<xsl:if test="EntryDate"> entrydate ,
</xsl:if>
<xsl:if test="LeavingDate"> leavingdate ,
</xsl:if>
<xsl:if test="LeavingReason">
leavingreason , </xsl:if>
<xsl:if test="LastSchool"> lastschool
</xsl:if>)
values(
<xsl:if test="LEA">'<xsl:value-of
select="LEA"/>', </xsl:if>
<xsl:if test="Estab">'<xsl:value-of
select="Estab"/>', </xsl:if>
<xsl:if test="SchoolName">'<xsl:value-of
select="SchoolName"/>', </xsl:if>
<xsl:if test="EntryDate">'<xsl:value-of
select="EntryDate"/>', </xsl:if>
<xsl:if test="LeavingDate">'<xsl:value-of
select="LeavingDate"/>', </xsl:if>
<xsl:if
test="LeavingReason">'<xsl:value-of
select="LeavingReason"/>', </xsl:if>
<xsl:if test="LastSchool">'<xsl:value-of
select="LastSchool"/>'</xsl:if>);
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--