Sample ASP.NET code to take XCRI CAP 1.2 XML from database and publish it as a web feed

Having got your XCRI CAP 1.2 XML generated from your database (see Sample SQL Server 2008 code for extracting XCRI CAP 1.2 XML from a relational database), you will now probably want to publish it on the web. A best practice could be to set up a scheduled task which extracts the XML, validates it, and (if passed) writes the XML as a static file to your webserver, then sends an email notification on the result to your administrators.

However, if you just want to generate a live feed, here is some code showing a way to do it using a Microsoft ASP.NET web form, using Visual Basic (VB.NET) code.

The ASP.NET page, save as default.aspx

This is a pretty simple page, which returns a content type of "application/xml", and holds the XML control that will be filled with the XCRI CAP 1.2 output.

<%@ Page Language="VB" ContentType="application/xml" AutoEventWireup="false" CodeFile="default.aspx.vb" Inherits="courses_xcri_cap_1p2_default" %> <asp:Xml ID="xmlCoursesCAP1p2" runat="server" />

The ASP.NET Visual Basic code behind file, save as default.aspx.vb

The main work is done in the code behind file, which sits in the same directory. The connection to your database should be set in your site/web application's web.config file, and you should replace "kelpiecollegeConnectionString" with the name of your connection string. You should also amend the values of the three parameters as necessary (they will work for the sample set of course data on this wiki).

Imports System.Configuration Imports System.Data Imports System.Data.SqlClient Imports System.Xml Imports System.Xml.XPath Partial Class courses_xcri_cap_1p2_default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim strSchemaName As String = "XCRI CAP 1.2" Dim strProviderIdentifier As String = "0000X00XX0" Dim strLanguage As String = "en" Try 'Set up SQL connection and command which returns XCRI XML, using named connection string from web.config file. Dim conn As SqlConnection conn = New SqlConnection(ConfigurationManager.ConnectionStrings("kelpiecollegeConnectionString").ConnectionString) Dim cmd As SqlCommand = New SqlCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dbo.usp_Course" cmd.Connection = conn 'Add parameters. Dim sprSchemaName As SqlParameter = New SqlParameter() Dim sprProviderIdentifier As SqlParameter = New SqlParameter() Dim sprLanguage As SqlParameter = New SqlParameter() sprSchemaName.ParameterName = "@schemaName" sprSchemaName.SqlDbType = SqlDbType.NVarChar sprSchemaName.Size = 25 sprSchemaName.Direction = ParameterDirection.Input sprSchemaName.Value = strSchemaName sprProviderIdentifier.ParameterName = "@providerIdentifier" sprProviderIdentifier.SqlDbType = SqlDbType.NVarChar sprProviderIdentifier.Size = 25 sprProviderIdentifier.Direction = ParameterDirection.Input sprProviderIdentifier.Value = strProviderIdentifier sprLanguage.ParameterName = "@language" sprLanguage.SqlDbType = SqlDbType.NVarChar sprLanguage.Size = 50 sprLanguage.Direction = ParameterDirection.Input sprLanguage.Value = strLanguage cmd.Parameters.Add(sprSchemaName) cmd.Parameters.Add(sprProviderIdentifier) cmd.Parameters.Add(sprLanguage) 'Open the connection. cmd.Connection.Open() 'Get the XML from the stored procedure with an XmlReader and put it into the XML control to be passed to the requester. Dim xmlr As System.Xml.XmlReader xmlr = cmd.ExecuteXmlReader() xmlr.Read() Dim xpnDoc As New XPathDocument(xmlr) xmlCoursesCAP1p2.XPathNavigator = xpnDoc.CreateNavigator() 'Dispose of command and connection objects. cmd.Dispose() conn.Dispose() Catch ex As Exception 'Do stuff to handle an exception. End Try End Sub End Class

Now all you need to do is publish these to your website and request the page. And remember that your webserver account will need EXECUTE permissions on the stored procedure (dbo.usp_Course).

You could set up caching on this page to avoid hitting the database every time.

A working demonstration of the web pages built from this code scan be found on the fictional Kelpie College website courses section.

For more information on the standard, see the XCRI Knowledge Base.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License.

Back to Code and Examples for XCRI CAP 1.2.

Last updated: .