This is second article in a series for LexBlog to
Sitecore. You can refer first article for LexBlog basics from here.
This second article is about creating SQL server database
for LexBlog, mapping details and required script.
We will migrate content from LexBlog XML file to Sitecore
in two phases as
Phase-I
=> From LexBlog XML to SQL Server
Phase-II
=> From SQL Server to Sitecore
 |
| LexBlog To Sitecore |
From LexBlog XML to SQL Server
We can store this XML file to any database but in our
case we have to store in SQL server. We need to create database for holding
this information.
Here is a schema diagram for holding these Lexblog XML
file details as
Database Schema
 |
| Database Schema for LexBlog |
Tables Details
Author –
Store all authors of blog site
Category –
Store all categories of this blog site
Item_Author –
Store authors details mapping for particular articles
Item_Category –
Store categories details mapping for particular articles
Item_Tag –
Store tags mapping details for particular articles
MainItem – This
is main table which store blog article related details
Tag –
Store all tags for this blog site
Title_Description –
Store only title and description of this blog site.
Scripts
We need tables and stored producers for holding LexBlog
details. You can run below scripts for the same.
Scripts to create tables
/****** Object: Table [dbo].[Author] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Author](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[author_id]
[int] NULL,
[author_login]
[nvarchar](250)
NULL,
[author_email]
[nvarchar](100)
NULL,
[author_display_name]
[nvarchar](500)
NULL,
[author_first_name]
[nvarchar](100)
NULL,
[author_last_name]
[nvarchar](100)
NULL,
[blogId]
[int] NULL,
CONSTRAINT
[PK_Author_1] PRIMARY KEY CLUSTERED
(
[Id]
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
/****** Object: Table [dbo].[Category] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Category](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[term_id]
[int] NULL,
[category_nicename]
[nvarchar](200)
NULL,
[category_parent]
[nvarchar](200)
NULL,
[cat_name]
[nvarchar](200)
NULL,
[blogId]
[int] NULL,
CONSTRAINT
[PK_Category] PRIMARY KEY CLUSTERED
(
[Id]
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
/****** Object: Table [dbo].[Item_Author] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Item_Author](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[post_id]
[int] NULL,
[author_id]
[int] NULL,
[niceName]
[nvarchar](200)
NULL,
[blogId]
[int] NULL,
CONSTRAINT
[PK_Item_Author] PRIMARY KEY CLUSTERED
(
[Id]
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
/****** Object: Table [dbo].[Item_Category] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Item_Category](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[post_id]
[int] NULL,
[category_id]
[int] NULL,
[nicename]
[nvarchar](200)
NULL,
[blogId]
[int] NULL,
CONSTRAINT
[PK_Item_Category] PRIMARY KEY CLUSTERED
(
[Id]
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
/****** Object: Table [dbo].[Item_Tag] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Item_Tag](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[post_id]
[int] NULL,
[tag_id]
[int] NULL,
[nicename]
[nvarchar](200)
NULL,
[blogId]
[int] NULL,
CONSTRAINT
[PK_Item_Tag] PRIMARY KEY CLUSTERED
(
[Id]
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
/****** Object: Table [dbo].[MainItem] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[MainItem](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[title]
[nvarchar](200)
NULL,
[post_id]
[int] NULL,
[post_date]
[datetime] NULL,
[main_content]
[nvarchar](max) NULL,
[blogId]
[int] NULL,
[post_status]
[nvarchar](200)
NULL,
[post_link]
[nvarchar](400)
NULL,
[post_name]
[nvarchar](200)
NULL,
CONSTRAINT
[PK_MainItem] PRIMARY KEY CLUSTERED
(
[Id]
ASC
)WITH (PAD_INDEX =
OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON
[PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Tag] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Tag](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[term_id]
[int] NULL,
[tag_slug]
[nvarchar](200)
NULL,
[tag_name]
[nvarchar](200)
NULL,
[blogId]
[int] NULL,
CONSTRAINT
[PK_Tag] PRIMARY KEY
CLUSTERED
(
[Id]
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
/****** Object: Table [dbo].[Title_Description] Script Date: 11/16/2016 2:49:20 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE
[dbo].[Title_Description](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[title]
[nvarchar](200)
NULL,
[description]
[nvarchar](500)
NULL,
CONSTRAINT
[PK_Title_Description] PRIMARY KEY CLUSTERED
(
[Id]
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
|
Scripts for creating stored procedure
Run below script to create required stored procedures in
LexBlog database.
/****** Object: StoredProcedure
[dbo].[ItemAuthorInsert] Script
Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[ItemAuthorInsert]
-- Add the parameters for the stored procedure here
@Category
nvarchar(200),
@post_id
int,
@nicename
nvarchar(200),
@blogId
int
AS
BEGIN
declare
@author_Id int
Select
@author_Id = author_id from Author where
author_login = @Category
Insert into Item_Author(post_id, author_id,
niceName, blogId)
values(@post_id, @author_Id,
@nicename, @blogId)
END
GO
/****** Object: StoredProcedure
[dbo].[ItemCategoryInsert] Script
Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[ItemCategoryInsert]
-- Add the parameters for the stored procedure here
@Category
nvarchar(200),
@post_id
int,
@nicename
nvarchar(200),
@blogId
int
AS
BEGIN
declare
@category_Id int
Select
@category_Id = term_id from Category where
category_nicename = @Category
Insert into Item_Category(post_id, category_id,
nicename, blogId)
values(@post_id, @category_Id,
@nicename, @blogId)
END
GO
/****** Object: StoredProcedure [dbo].[ItemTagInsert] Script Date: 11/16/2016 2:50:22 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[ItemTagInsert]
-- Add the parameters for the stored procedure here
@Category
nvarchar(200),
@post_id
int,
@nicename
nvarchar(200),
@blogId
int
AS
BEGIN
declare
@tag_Id int
Select
@tag_Id = term_id from
Tag where tag_name =
@Category
Insert into Item_Tag(post_id, tag_Id, nicename, blogId) values(@post_id, @tag_Id,
@nicename, @blogId)
END
GO
/****** Object: StoredProcedure [dbo].[SP_AuthorsAdd] Script Date: 11/16/2016 2:50:22 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[SP_AuthorsAdd]
-- Add the parameters for the stored procedure here
@author_id
int,
@author_login
nvarchar(250),
@author_email
nvarchar(100),
@author_display_name
nvarchar(500),
@author_first_name
nvarchar(100),
@author_last_name
nvarchar(100),
@blogId int
AS
BEGIN
Insert INTO Author (author_id,author_login,author_email,author_display_name,author_first_name,author_last_name,
blogId) VALUES (@author_id,@author_login,@author_email,@author_display_name,@author_first_name,@author_last_name, @blogId)
END
GO
/****** Object: StoredProcedure [dbo].[SP_CategoryAdd] Script Date: 11/16/2016 2:50:22 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[SP_CategoryAdd]
-- Add the parameters for the stored procedure here
@term_id
int,
@category_nicename
nvarchar(200),
@category_parent
nvarchar(200),
@cat_name
nvarchar(200),
@blogId
int
AS
BEGIN
Insert INTO
Category (term_id,category_nicename,category_parent,cat_name, blogId) VALUES (@term_id,@category_nicename,@category_parent,@cat_name, @blogId)
END
GO
/****** Object: StoredProcedure [dbo].[SP_MainItemAdd] Script Date: 11/16/2016 2:50:22 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[SP_MainItemAdd]
-- Add the parameters for the stored procedure here
@title
nvarchar(200),
@post_id
int,
@post_date
datetime,
@main_content
nvarchar(max),
@blogId
int,
@post_name nvarchar(200),
@post_status
nvarchar(200),
@post_link
nvarchar(400)
AS
BEGIN
Insert INTO
MainItem (title,post_id,post_date,main_content,
blogId, post_name,
post_status, post_link) VALUES (@title,@post_id,@post_date,@main_content, @blogId,
@post_name, @post_status, @post_link)
END
GO
/****** Object: StoredProcedure [dbo].[SP_TagAdd] Script Date: 11/16/2016 2:50:22 PM
******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[SP_TagAdd]
-- Add the parameters for the stored procedure here
@term_id
int,
@tag_slug
nvarchar(200),
@tag_name
nvarchar(200),
@blogId
int
AS
BEGIN
Insert INTO Tag (term_id,tag_slug,tag_name,blogId) VALUES (@term_id,@tag_slug,@tag_name,@blogId)
END
GO
/****** Object: StoredProcedure
[dbo].[SP_TitleDescInsert] Script
Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[SP_TitleDescInsert]
-- Add the parameters for the stored procedure here
@title
nvarchar(200),
@description
nvarchar(500),
@new_identity
INT OUTPUT
AS
BEGIN
Insert into Title_Description(title, description) values (@title,
@description)
SELECT
@new_identity = SCOPE_IDENTITY()
SELECT
@new_identity AS blogId
return
END
GO
|
XML and SQL Mapping Details
In previous sections, we walk through with XML file, its different
sections along with database schema and tables.
Let see how to map XML files fields with database tables.
Basic details are mapped as
 |
| LexBlog Basic Details Mapping with SQL Server |
Authors are mapped as
 |
| LexBlog Authors Mapping with SQL Server |
Categories are mapped as
 |
| LexBlog categories Mapping with SQL Server |
Tags are mapped as
 |
| LexBlog Tags Mapping with SQL Server |
Blog items contains article description along with its
associated tags, categories and authors. So this is the main table of this
LexBlog migration process. So here is mapping of this XML section with SQL
tables as
 |
| LexBlog Items Mapping with SQL Server |
That’s it for the part II. In next part I, will walk
through with our strategy for moving this LexBlog Site into SQL Server and Sitecore.
I hope you like this Sitecore lesson. Stay tuned for more
Sitecore related articles.
Till that happy
sitecoring :)
Please leave your comments or share this article if it’s
useful for you.
