Sample SQL Server 2005 code for generating XCRI XML from a simplified relational course database

From Xcri

Here is some sample SQL for Microsoft SQL Server 2005 that sets up a simple relational course database, populates it with two courses, each of which have two programmes, and creates a stored procedure to return a course feed in XCRI XML.

1 Create the tables
2 Populate the tables with dummy course and programme data
3 Create stored procedure
4 Execute stored procedure with default parameters
5 Results
6 Execute stored procedure with different parameters
7 Results 2
8 Notes

[edit] Create the tables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
	[CourseId] [nvarchar](6) NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[QualificationName] [nvarchar](50) NULL,
	[QualificationLevel] [tinyint] NULL,
	[Overview] [xml] NULL,
	[SubjectArea] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
(
	[CourseId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Programme](
	[ProgrammeId] [nvarchar](25) NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NULL,
	[Venue] [nvarchar](50) NULL,
	[CourseId] [nvarchar](6) NOT NULL,
	[ModeOfAttendance] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Programme] PRIMARY KEY CLUSTERED 
(
	[ProgrammeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier for a programme (an instance of a course).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Programme', @level2type=N'COLUMN',@level2name=N'ProgrammeId'
GO
ALTER TABLE [dbo].[Programme]  WITH CHECK ADD  CONSTRAINT [FK_Occurence_Course] FOREIGN KEY([CourseId])
REFERENCES [dbo].[Course] ([CourseId])
GO
ALTER TABLE [dbo].[Programme] CHECK CONSTRAINT [FK_Occurence_Course]

[edit] Populate the tables with dummy course and programme data

INSERT INTO [dbo].[Course]
           ([CourseId]
           ,[Title]
           ,[QualificationName]
           ,[QualificationLevel]
           ,[Overview]
           ,[SubjectArea])
     VALUES
           ('BSCAN'
           ,'Applied Networking Technologies'
           ,'BSc'
           ,9
           ,'<p>Industry analysts are currently predicting a shortfall of trained networking staff with a Business knowledge.  Building on from our HND this course is designed to equip students with the very latest skills and qualities that they will need to succeed in the vibrant IT orientated world.  You will develop specialist skills in security technologies and techniques and in particular the use of intrusion techniques and penetration testing as methods of determining the robustness of IT security.</p>'
           ,'Information Technology and Information')
GO

INSERT INTO [dbo].[Course]
           ([CourseId]
           ,[Title]
           ,[QualificationName]
           ,[QualificationLevel]
           ,[Overview]
           ,[SubjectArea])
     VALUES
           ('UWBKW'
           ,'Underwater Basketweaving'
           ,NULL
           ,NULL
           ,'<p>Learn this traditional craft, using quality local materials, in the comfort of a swimming pool.</p><p>Full snorkelling kit and reed thimbles are provided.</p>'
           ,'Unreal crafts')
GO

INSERT INTO [dbo].[Programme]
           ([ProgrammeId]
           ,[StartDate]
           ,[EndDate]
           ,[Venue]
           ,[CourseId]
           ,[ModeOfAttendance])
     VALUES
           ('2008/2009/BSCAN/F1/BK/08'
           ,'2008-09-01 00:00:00'
           ,'2009-05-20 00:00:00'
           ,NULL
           ,'BSCAN'
           ,'Full Time')
GO

INSERT INTO [dbo].[Programme]
           ([ProgrammeId]
           ,[StartDate]
           ,[EndDate]
           ,[Venue]
           ,[CourseId]
           ,[ModeOfAttendance])
     VALUES
           ('2008/2009/BSCAN/I1/BK/08'
           ,'2008-09-01 00:00:00'
           ,'2009-05-20 00:00:00'
           ,NULL
           ,'BSCAN'
           ,'Part Time Day')
GO

INSERT INTO [dbo].[Programme]
           ([ProgrammeId]
           ,[StartDate]
           ,[EndDate]
           ,[Venue]
           ,[CourseId]
           ,[ModeOfAttendance])
     VALUES
           ('2008/2009/UWBKW/X1/08'
           ,'2008-04-01 00:00:00'
           ,'2008-04-01 00:00:00'
           ,NULL
           ,'UWBKW'
           ,'Day')
GO

INSERT INTO [dbo].[Programme]
           ([ProgrammeId]
           ,[StartDate]
           ,[EndDate]
           ,[Venue]
           ,[CourseId]
           ,[ModeOfAttendance])
     VALUES
           ('2008/2009/UWBKW/Y1/08'
           ,'2008-04-30 00:00:00'
           ,'2008-05-06 00:00:00'
           ,NULL
           ,'UWBKW'
           ,'Part Time Day')
GO

[edit] Create stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Tavis Reddick, Adam Smith College
-- Create date: 2009-08-03
-- Modified date: 2009-08-11
-- Description: Returns XCRI course feeds from a greatly simplified course database.
-- Licence: This work is licensed under the Creative Commons Attribution 3.0 Unported License.
-- To view a copy of this license, visit http://creativecommons.org/licenses/by/3.0/ or send a
-- letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.
-- =============================================
CREATE PROCEDURE [dbo].[usp_CourseFeed]
	-- Add the parameters for the stored procedure here
	@schemaName NVARCHAR(25) = 'XCRI CAP 1.0', -- in future, add new feeds for 'XCRI CAP 1.1', or 'XCRI CAP 1.2', 'XCRI CAP 1.1 with Learndirect extensions' and so on.
	@providerIdentifier NVARCHAR(25) = '0000X00XX0', -- your organizational identifier
	@providerName NVARCHAR(50) = 'Acme University' -- your organizational name
AS
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET CONCAT_NULL_YIELDS_NULL OFF; -- So null values can be concatenated with strings.


IF @schemaName = 'XCRI CAP 1.0'
	BEGIN
	   -- Build the XCRI CAP 1.0 XML using correlated subqueries with FOR XML PATH.
		WITH XMLNAMESPACES ('http://xcri.org/profiles/catalog' AS xcri, 
			'http://www.w3.org/1999/xhtml' AS xhtml,
			'http://purl.org/dc/elements/1.1/' AS dc,
			DEFAULT 'http://xcri.org/profiles/catalog')
		SELECT GETDATE() AS "@generated",
		CAST ((SELECT @providerIdentifier AS identifier, @providerName AS [name],
		CAST ((SELECT CourseId AS identifier, (LTRIM(QualificationName + ' ') + Title) AS title,
			CONVERT(XML, '<div xmlns="http://www.w3.org/1999/xhtml">' + CAST(Overview AS NVARCHAR(4000)) + '</div>') AS [description], -- wrap the Overview XML fragment in a div with the appropriate HTML namespace.
			COALESCE(QualificationName, '') AS "qualification/title", COALESCE(QualificationLevel, 0) AS "qualification/level", -- qualification can't be empty, so use COALESCE to put empty strings or zeroes in if columns contain null values.
			CAST((SELECT ProgrammeId AS identifier, StartDate AS start, ModeOfAttendance AS studyMode
			FROM dbo.Programme AS programme WHERE course.CourseId = programme.CourseId
			FOR XML PATH('presentation'), ELEMENTS) AS XML)
			, SubjectArea AS "dc:subject"
		FROM dbo.Course AS course
		FOR XML PATH ('course')) AS XML)
		FOR XML PATH ('provider')) AS XML)
		FOR XML PATH ('catalog')
	END

IF @schemaName = 'XCRI Curriculum 1.0'
	BEGIN
	   -- Build the XCRI CAP 1.0 XML using correlated subqueries with FOR XML PATH.
		WITH XMLNAMESPACES ('http://elframework.org/curriculum/elements' AS xcri, 
			'http://www.w3.org/1999/xhtml' AS xhtml,
			'http://purl.org/dc/elements/1.1/' AS dc,
			'http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0' AS imsRLI,
			DEFAULT 'http://elframework.org/curriculum/elements')
		SELECT 'Date/time of data extract' AS  [calendarEvent/dc:title], GETDATE() AS [calendarEvent/dc:date],
		--CAST ((SELECT @providerIdentifier AS identifier, @providerName AS [name],
		CAST ((SELECT 'course' AS [@type], CourseId AS identifier, (LTRIM(QualificationName + ' ') + Title) AS [dc:title],
			SubjectArea AS "dc:subject",
			CONVERT(XML, '<div xmlns="http://www.w3.org/1999/xhtml">' + CAST(Overview AS NVARCHAR(4000)) + '</div>') AS [description], -- wrap the Overview XML fragment in a div with the appropriate HTML namespace.
			COALESCE(QualificationName, '') AS [recognitions/recognition/award/awardTitle], COALESCE(QualificationLevel, 0) AS [recognitions/recognition/award/awardLevel], -- qualification can't be empty, so use COALESCE to put empty strings or zeroes in if columns contain null values.
			CAST ((SELECT
				CAST((SELECT ProgrammeId AS identifier, ModeOfAttendance AS studyMode,
				StartDate AS start, EndDate AS [end], Venue AS location
				FROM dbo.Programme AS programme WHERE course.CourseId = programme.CourseId
				FOR XML PATH('offering'), ELEMENTS, ROOT('offerings')) AS XML)
			FOR XML PATH('offeringPattern')) AS XML)
		FROM dbo.Course AS course
		FOR XML PATH ('spec')) AS XML)
		--FOR XML PATH ('provider')) AS XML)
		FOR XML PATH ('curriculum')
	END;

[edit] Execute stored procedure with default parameters

EXEC dbo.usp_CourseFeed @schemaName = 'XCRI CAP 1.0', @providerIdentifier = '0000X00XX0', @providerName = 'Acme University'

[edit] Results

You should get from the stored procedure an XML output which is valid XCRI CAP 1.0, as shown below.

<catalog xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog" generated="2009-08-05T02:01:38.827">
  <provider xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
    <identifier>0000X00XX0</identifier>
    <name>Acme University</name>
    <course xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
      <identifier>BSCAN</identifier>
      <title>BSc Applied Networking Technologies</title>
      <description>
        <div xmlns="http://www.w3.org/1999/xhtml">
          <p>Industry analysts are currently predicting a shortfall of trained networking staff with a Business knowledge.  Building on from our HND this course is designed to equip students with the very latest skills and qualities that they will need to succeed in the vibrant IT orientated world.  You will develop specialist skills in security technologies and techniques and in particular the use of intrusion techniques and penetration testing as methods of determining the robustness of IT security.</p>
        </div>
      </description>
      <qualification>
        <title>BSc</title>
        <level>9</level>
      </qualification>
      <presentation xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
        <identifier>2008/2009/BSCAN/F1/BK/08</identifier>
        <start>2008-09-01T00:00:00</start>
        <studyMode>Full Time</studyMode>
      </presentation>
      <presentation xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
        <identifier>2008/2009/BSCAN/I1/BK/08</identifier>
        <start>2008-09-01T00:00:00</start>
        <studyMode>Part Time Day</studyMode>
      </presentation>
      <dc:subject>Information Technology and Information</dc:subject>
    </course>
    <course xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
      <identifier>UWBKW</identifier>
      <title>Underwater Basketweaving</title>
      <description>
        <div xmlns="http://www.w3.org/1999/xhtml">
          <p>Learn this traditional craft, using quality local materials, in the comfort of a swimming pool.</p>
          <p>Full snorkelling kit and reed thimbles are provided.</p>
        </div>
      </description>
      <qualification>
        <title />
        <level>0</level>
      </qualification>
      <presentation xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
        <identifier>2008/2009/UWBKW/X1/08</identifier>
        <start>2008-04-01T00:00:00</start>
        <studyMode>Day</studyMode>
      </presentation>
      <presentation xmlns="http://xcri.org/profiles/catalog" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://xcri.org/profiles/catalog">
        <identifier>2008/2009/UWBKW/Y1/08</identifier>
        <start>2008-04-30T00:00:00</start>
        <studyMode>Part Time Day</studyMode>
      </presentation>
      <dc:subject>Unreal crafts</dc:subject>
    </course>
  </provider>
</catalog>

[edit] Execute stored procedure with different parameters

Now, try generating an XCRI Curriculum 1.0 feed.

EXEC dbo.usp_CourseFeed @schemaName = 'XCRI Curriculum 1.0'

[edit] Results 2

You should get a valid XCRI Curriculum 1.0 result like:

<curriculum xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements">
  <calendarEvent>
    <dc:title>Date/time of data extract</dc:title>
    <dc:date>2009-08-11T00:42:10.067</dc:date>
  </calendarEvent>
  <spec xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements" type="course">
    <identifier>BSCAN</identifier>
    <dc:title>BSc Applied Networking Technologies</dc:title>
    <dc:subject>Information Technology and Information</dc:subject>
    <description>
      <div xmlns="http://www.w3.org/1999/xhtml">
        <p>Industry analysts are currently predicting a shortfall of trained networking staff with a Business knowledge.  Building on from our HND this course is designed to equip students with the very latest skills and qualities that they will need to succeed in the vibrant IT orientated world.  You will develop specialist skills in security technologies and techniques and in particular the use of intrusion techniques and penetration testing as methods of determining the robustness of IT security.</p>
      </div>
    </description>
    <recognitions>
      <recognition>
        <award>
          <awardTitle>BSc</awardTitle>
          <awardLevel>9</awardLevel>
        </award>
      </recognition>
    </recognitions>
    <offeringPattern xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements">
      <offerings xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements">
        <offering>
          <identifier>2008/2009/BSCAN/F1/BK/08</identifier>
          <studyMode>Full Time</studyMode>
          <start>2008-09-01T00:00:00</start>
          <end>2009-05-20T00:00:00</end>
        </offering>
        <offering>
          <identifier>2008/2009/BSCAN/I1/BK/08</identifier>
          <studyMode>Part Time Day</studyMode>
          <start>2008-09-01T00:00:00</start>
          <end>2009-05-20T00:00:00</end>
        </offering>
      </offerings>
    </offeringPattern>
  </spec>
  <spec xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements" type="course">
    <identifier>UWBKW</identifier>
    <dc:title>Underwater Basketweaving</dc:title>
    <dc:subject>Unreal crafts</dc:subject>
    <description>
      <div xmlns="http://www.w3.org/1999/xhtml">
        <p>Learn this traditional craft, using quality local materials, in the comfort of a swimming pool.</p>
        <p>Full snorkelling kit and reed thimbles are provided.</p>
      </div>
    </description>
    <recognitions>
      <recognition>
        <award>
          <awardTitle />
          <awardLevel>0</awardLevel>
        </award>
      </recognition>
    </recognitions>
    <offeringPattern xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements">
      <offerings xmlns="http://elframework.org/curriculum/elements" xmlns:imsRLI="http://www.imsglobal.org/services/rli/xsd/imsRLIManDataSchema_v1p0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xcri="http://elframework.org/curriculum/elements">
        <offering>
          <identifier>2008/2009/UWBKW/X1/08</identifier>
          <studyMode>Day</studyMode>
          <start>2008-04-01T00:00:00</start>
          <end>2008-04-01T00:00:00</end>
        </offering>
        <offering>
          <identifier>2008/2009/UWBKW/Y1/08</identifier>
          <studyMode>Part Time Day</studyMode>
          <start>2008-04-30T00:00:00</start>
          <end>2008-05-06T00:00:00</end>
        </offering>
      </offerings>
    </offeringPattern>
  </spec>
</curriculum>

[edit] Notes

This method of XML extraction:

doesn't work in SQL Server 2000, and hasn't been tested in SQL Server 2008 or other database engines. The variant of SQL used is Microsoft's T-SQL and the XML parts may not be very portable. However, it does work with the free SQL Server 2005 Express, so it should easy enough to try it out.
is simpler, more direct and easier to maintain than our previous method, which relied on an ASP.NET web page and various stored procedures and XSLT transformations.
does not yet have full functionality. I haven't worked out the validation yet; the obvious thing is to get SQL Server Integration Services to run the stored procedure, validate the XML and write the file to a webserver if it passes, but this hasn't proved as easy as saying it, and if anyone can help, that would be a valuable addition to this page. The code for the CAP validator on the XCRI tools page could be used here, but it might be more convenient to use a validating web service with parameterized schema, which Integration Services should be able to connect with.
can easily be extended to produce different flavours of XCRI, and any forthcoming European standard.

This sample uses a grossly simplified relational database, but the main methods of structuring elements and adding attributes are shown, I think. --Tavis 00:48, 11 August 2009 (BST)

Retrieved from "http://www.xcri.org/wiki/index.php/Sample_SQL_Server_2005_code_for_generating_XCRI_XML_from_a_simplified_relational_course_database"

Sample SQL Server 2005 code for generating XCRI XML from a simplified relational course database

From Xcri

Contents

[edit] Create the tables

[edit] Populate the tables with dummy course and programme data

[edit] Create stored procedure

[edit] Execute stored procedure with default parameters

[edit] Results

[edit] Execute stored procedure with different parameters

[edit] Results 2

[edit] Notes

Views

Personal tools

Navigation

Search

Toolbox