 next
|
  Subject: Get rid of spaces in returned output
Author: Rodolfo Vegas
Date: 24 Oct 2008 06:29 AM
|
Hi, I'm stucked with a annoying issue and hope you can help me.
I'm accesing a table in a relational database (on SQL Server) from wich I have (basically) to process (and output) the following information for each row (the table contains information about training courses):
1 - A unique key for the course.
2 - Different days of the week (the initial letter used for each day, in spanish: L,M,X,J,V,S) which the course take place (Sundays are excluded).
On the input table there is a column called DAYS, that keeps the information of ALL the dates that the course take place (sessions) in the following format:
dd/mm/yyyy.dd/mm/yyyy.dd/mm/yyyy. ... dd/mm/yyyy
An example of input and expected output:
Input: 09/10/2008.14/10/2008.16/10/2008.21/10/2008
Output: MJ
Note that in the result strings, the days must be in order (in this case, altough the first (and third) date corresponds to a Thursday -J-, the first letter is the M, that corresponds to the second and fourth dates).
Up to now I have this code:
declare variable $year as xs:string external;
<Courses>
{
for $row in collection("TRAINING.dbo.COURSES")/COURSES
where $row/YEAR = $year
and not(empty($row/DAYS))
return
<Course>
<CourseKey>
<Code>
{$row/CODE}
</Code>
<Year>
{$row/YEAR}
</Year>
</CourseKey>
<WeekDays>
{distinct-values
(
for $date in tokenize(normalize-space($row/DAYS), "[.]")
let $DDMMYYYY := replace($date, "/", "")
let $DD := substring($DDMMYYYY, 1, 2)
let $MM := substring($DDMMYYYY, 3, 2)
let $YYYY := substring($DDMMYYYY, 5, 4)
let $weekDayNumber := xs:integer((xs:date(concat($YYYY,'-',$MM,'-',$DD)) - xs:date('1901-01-06')) div xs:dayTimeDuration('P1D')) mod 7
where string-length($date) = 10
order by $weekDayNumber
return
if ($weekDayNumber = 1) then "L"
else if ($weekDayNumber = 2) then "M"
else if ($weekDayNumber = 3) then "X"
else if ($weekDayNumber = 4) then "J"
else if ($weekDayNumber = 5) then "V"
else if ($weekDayNumber = 6) then "S"
else ()
)
}
</WeekDays>
</Course>
}
</Courses>
And this is the result for the example:
<Courses>
<Course>
<CourseKey>
<Code>
BCA103
</Code>
<Year>
2008
</Year>
</CourseKey>
<WeekDays>
M J
</Weekdays>
</Course>
</Courses>
How can I get rid of the spaces (one in this case) for the WeekDays element? (The first question really should be: Why does it generate this spaces?)
If the result for this element were a string I could do a replace(.," ",""), but as it is a sequence, this is not possible. (As far as I know, there is no way to convert a sequence into a string, is there?)
Is there any solution to do this, if possible, in the 'return' portion of the code, without having to reprocess the resulting XML document?
|
|
top