-- Create a new database called [Virtual-Class-01] in SQL Server.

-- Right-click and choose [New Query]

-- Copy and paste the code below and hit [Execute]


USE [Virtual-Class-01]
GO
/****** Object:  Table [dbo].[XMLSitemap]    Script Date: 7/8/2022 5:17:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XMLSitemap](
	[XMLID][int] IDENTITY(1,1) NOT NULL,
	[XMLTitle][nvarchar](50) NOT NULL,
	[XMLTime][datetime] NULL,
 CONSTRAINT [PK_XML] PRIMARY KEY CLUSTERED 
(
	[XMLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[XMLSitemap] ON 
GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (1, N'One', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (2, N'Two', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (3, N'Three', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (4, N'Four', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (5, N'Five', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (6, N'Six', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (7, N'Seven', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
INSERT [dbo].[XMLSitemap] ([XMLID], [XMLTitle], [XMLTime]) VALUES (8, N'Eight', CAST(N'2022-07-08T17:16:57.743' AS DateTime))

GO
SET IDENTITY_INSERT [dbo].[XMLSitemap] OFF
GO
ALTER TABLE [dbo].[XMLSitemap] ADD  CONSTRAINT [DF_XMLSitemap_XMLTime]  DEFAULT (getdate()) FOR [XMLTime]
GO

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Load.aspx.cs" Inherits="Load" %>
<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %><xmp><?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"><%
{
    SqlConnection myConnection;
    SqlCommand myCommand;
    SqlDataReader myDataReader;
    myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Virtual-Learning"].ConnectionString);
    myConnection.Open();
    myCommand = new SqlCommand("select XMLID, XMLTitle, XMLTime from XMLSitemap", myConnection);
    myDataReader = myCommand.ExecuteReader();
    while (myDataReader.Read())
    {%>
    <url>
  <loc><%=(string)myDataReader["XMLTitle"]%></loc>
  <lastmod><%=(DateTime)myDataReader["XMLTime"]%></lastmod>
  <changefreq>always</changefreq>
</url><%
          }
          myDataReader.Close();
          myConnection.Close();
          
          }
%>
</urlset></xmp>

<?xml version="1.0"?>
<configuration>
  <connectionStrings configSource="database.config" />
  <system.web>
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.7.2" />
    <httpRuntime targetFramework="4.7.2" />
  </system.web>
</configuration>

<?xml version="1.0"?>
<connectionStrings>
  <add name ="Virtual-Learning" connectionString="Data Source=sqlcorecs-01\sql2019;Database=Virtual-Class-01;User ID=testuser;Password=testuser;MultipleActiveResultSets=True"/>
</connectionStrings>