uspuwkTimetable

This user proc is called where there is some sort of data transformation required during a timetable import.

With the recent changes to the timetable import stored procs to handle Timetabling Solutions (Timetabler) v10 which uses JSON file format, this has introduced some issues in cases the user proc is doing its own loading of content from the Timetable file (such as period and day mapping), to achieve the outcome the proc was designed for (eg setting amended period numbers).

If the school has moved to using the v10 .tfx files, the existing XML based open/extract will fail with errors (eg invalid characters).

The user proc thus needs to be amended in the areas that work directly with opening the timetable .tfx file and extracting data, to switch to JSON based operations.

Below are some before and after code blocks from one example user proc, showing the changes involved to convert existing XML logic to equivalent JSON - via copying the equivalent JSON based code block from the standard stored proc dbo.spuwkTimetable_TimetablerV10 which handles the standard timetable file extraction, then commenting or adapting the existing xml based code in user proc as required.

Initial open/extract of file content to variable (names of variables are changed for ease of later use)

 DECLARE 
-- @xmlText VARCHAR(MAX),
-- @XML XML,
@SQL NVARCHAR(4000) = '',
@jsonText NVARCHAR(MAX)

IF @jsonText IS NULL
BEGIN
SET @SQL = 'SET @jsonText = (SELECT BulkColumn
FROM OPENROWSET(
BULK ''' + @TimetablePath + ''',
SINGLE_BLOB) AS x)'

--# scgignore(MI004,EI018,ST008) 
EXEC sp_executesql @SQL,N'@jsonText VARCHAR(MAX) OUTPUT',@jsonText = @jsonText OUTPUT
--# scgallow(MI004,EI018,ST008) 
END;

IF ISJSON(@jsonText) = 0
BEGIN
RAISERROR('JSON Parse Error', 16, 1)
RETURN(-1)
END

--SELECT @xml = CONVERT(XML,@xmlText)

Changing the existing table variable insertion to use JSON extraction - original xml-based blocks are shown commented out to illustrate the differences

/*
    ;WITH XMLNAMESPACES(DEFAULT 'http://www.timetabling.com.au/TDV9')
    INSERT INTO @Days
    (
      DayID,
      Code,
      Name
    )
    SELECT 
      RollClass.RollClassXML.value('DayID[1]', 'varchar(MAX)'),
      RollClass.RollClassXML.value('Code[1]', 'varchar(MAX)'),
      RollClass.RollClassXML.value('Name[1]', 'varchar(MAX)')
    FROM @XML.nodes('TimetableDevelopmentData/Days/Day') AS RollClass(RollClassXML)
*/

    INSERT INTO @Days
    (
      DayID,
      Code,
      Name
    )
    SELECT
      DayID, 
      Code,
      Name
    FROM OPENJSON(@jsonText,'$."Days"')
    WITH (
      DayID VARCHAR(50) '$.DayID', 
      Code  VARCHAR(200) '$.Code',
      Name  VARCHAR(200) '$.Name'
    )

/*
;WITH XMLNAMESPACES(DEFAULT 'http://www.timetabling.com.au/TDV9')
    INSERT INTO @Periods
    SELECT 
      Periods.PeriodsXML.value('PeriodID[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('DayID[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Code[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Name[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Doubles[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Triples[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Quadruples[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('SiteMove[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Load[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Index[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('Number[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('StartTime[1]', 'varchar(MAX)'),
      Periods.PeriodsXML.value('FinishTime[1]', 'varchar(MAX)')
    FROM @XML.nodes('TimetableDevelopmentData/Periods/Period') AS Periods(PeriodsXML)
*/
    INSERT INTO @Periods
    SELECT
      PeriodID, 
      DayID,
      Code,
      Name,
      Doubles,
      Triples,
      Quadruples,
      SiteMove,
      [Load],
      [Index],
      Number,
      StartTime,
      FinishTime
    FROM OPENJSON(@jsonText,'$."Periods"')
    WITH (
      PeriodID    VARCHAR(50) '$.PeriodID', 
      DayID       VARCHAR(200) '$.DayID',
      Code        VARCHAR(200) '$.Code',
      Name        VARCHAR(200) '$.Name',
      Doubles     VARCHAR(200) '$.Doubles',
      Triples     VARCHAR(200) '$.Triples',
      Quadruples  VARCHAR(100) '$.Quadruples',
      SiteMove    VARCHAR(100) '$.MoveBetweenSites',
      [Load]      VARCHAR(100) '$.Load',
      [Index]     VARCHAR(100) '$.PeriodIndex',
      Number      VARCHAR(100) '$.PeriodNo',
      StartTime   VARCHAR(100) '$.StartTime',
      FinishTime  VARCHAR(100) '$.FinishTime'
    )