Remove HTML Tags In Sql

CREATE FUNCTION [dbo].[fn_RemoveHtmlTag](@htmlTagWithHtml VARCHAR(MAX))RETURNS VARCHAR(MAX)AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@htmlTagWithHtml )SET @End = CHARINDEX('>',@htmlTagWithHtml ,CHARINDEX('<',@htmlTagWithHtml ))SET @Length = (@End - @Start) + 1WHILE @Start > 0AND @End > 0AND @Length > 0BEGIN
SET @htmlTagWithHtml = STUFF(@htmlTagWithHtml ,@Start,@Length,'')SET @Start = CHARINDEX('<',@htmlTagWithHtml )SET @End = CHARINDEX('>',@htmlTagWithHtml ,CHARINDEX('<',@htmlTagWithHtml ))SET @Length = (@End - @Start) + 1END
RETURN LTRIM(RTRIM(@htmlTagWithHtml ))ENDGO

Importing XML File to Sql Server Table

 Here is the example on how to import an xml file in the sql server installed machine to sql server table. Steps to do this 
1. Save the xml file to a location on your drive eg.C:\xmlfiles\Students.xml

2. Create Table Students

3. Import the xml using Openrowset bulk option  and select the values in xml using xpath and xquery
    and Do a Insert into using select
4. Results
STEP 1
The contents of the saved file will be
<Students>

  <Student>

    <Id>1</Id>

    <Name>Madhan</Name>

  </Student>

  <Student>

    <Id>2</Id>

    <Name>Karthi</Name>

  </Student>

  <Student>

    <Id>3</Id>

    <Name>Bata</Name>

  </Student>

</Students>

STEP 2

The syntax to create a table

CREATE TABLE TblStudents

(

 Id INT PRIMARY KEY,

 Name VARCHAR(50)

);
STEP 3

Query to select the values from xml and to extract

INSERT INTO TblStudents (Id , Name )

SELECT students.student.query('Id').value('.', 'INT') as Id,

       students.student.query('Name').value('.', 'VARCHAR(30)') as Name

FROM (

SELECT CAST(b AS XML)

FROM OPENROWSET(

     BULK 'C:\xmlfiles\Students.xml',

     SINGLE_BLOB) AS a(b)

     ) AS a(b)

CROSS APPLY b.nodes('Students/Student') AS students(student);

STEP 4

SELECT Id, Name FROM TblStudents;

/*

Results:

Id        Name

----------- -------------

1           Madhan

2           Karthi

3           Bata

*/

xml to sql

Example1 on how to read xml in sql server

declare @xmlfullstring xml

set @xmlfullstring= '<root>

<name>madhan</name>

<name>kumar</name>

</root>'

select xfs.name.value('name[1]','varchar(50)') as namexml,

xfs.name.value('name[2]','varchar(50)') as namexml

from @XmlFullString.nodes('root')xfs(name)

Example2 on how to read xml in sql server

DECLARE @Details xml

    SET @Details = '<root>

    <SelectedCategory CategoryId="101">

        <ItemID>120</ItemID>

        <ItemID>256</ItemID>

      </SelectedCategory>

      <SelectedCategory CategoryId="125">

        <ItemID>158</ItemID>

        <ItemID>120</ItemID>

    </SelectedCategory>

    <SelectedCategory CategoryId="15">

            <ItemID>5986</ItemID>

            <ItemID>20</ItemID>

        <ItemID>268</ItemID>

      </SelectedCategory>

    </root>'

    SELECT  T.[CategoryID],

            T2.Loc.query('.').value('.', 'INT') Val

    FROM

        ( SELECT SelCat.CatDet.value('(@CategoryId)[1]', 'int') as CategoryID,

                 SelCat.CatDet.query('.') as ItemID

          FROM @Details.nodes('/root/SelectedCategory') as SelCat(CatDet)

         ) T  CROSS APPLY ItemID.nodes('/SelectedCategory/ItemID') as T2(Loc)

Example3 on how to read xml in sql server

declare @studentxml xml ='

<Students>

<Student>

<Id>1</Id>

<Name>Madhan</Name>

</Student>

      <Student>

<Id>2</Id>

<Name>Karthi</Name>

</Student>

      <Student>

<Id>3</Id>

<Name>Bata</Name>

</Student>

</Students>'

SELECT X.Student.query('Id').value('.', 'INT') as studentid,

       X.Student.query('Name').value('.', 'VARCHAR(50)') as studentname

FROM @studentxml.nodes('/') as a(b)

CROSS APPLY b.nodes('Students/Student') AS X(Student);         

How to encrypt a procedure in sql server

Example Code for Stored Procedure Encryption - SQL SERVER

/*STEP1: create stored procedure*/

create procedure SSP_SelectNo

with encryption

as begin

select 1 as no

end

go

/*STEP2: Execute stored procedure*/

exec SSP_SelectNo

go

/*STEP3: Get Contents of stored procedure*/

sp_helptext 'SSP_SelectNo'

Code Explanation:

The keyword used to encrypt the stored procedure in sql server  is "with encryption". When you create a stored procedure with this keyword the stored procedure source code cannot be seen anymore.So please save a copy of your stored procedure before encrypting it. This will be useful when the sql server is installed at the client end and can avoid source code theft to some extent.

Stored Procedure Encryption in Sql Server Image

Sql To Xml ( Create xml using sql select )

Example code for you

select * from (

select 1 as id,'one' as name

union all

select 2 as id,'two' as name

union all

select 3 as id,'three' as name) as a

for xml path('units'),ROOT('root')

Result XML

<root>

  <units>

    <id>1</id>

    <name>one</name>

  </units>

  <units>

    <id>2</id>

    <name>two</name>

  </units>

  <units>

    <id>3</id>

    <name>three</name>

  </units>

</root>

Explanation : Convert Sql Select To XML

The Keyword used to convert an sql select into xml is "for xml path(),ROOT()" the parameter which goes inside the path() eg. path('units')is the tag which wraps the every single record. And the parameter which goes inside the ROOT() eg.,ROOT('root')is the one one which forms the root xml tag, i.e it wraps all the records as a whole.

What is Sql Server

Sql Server is a database software to keep the information organised. Imagine it to a Giant excel software which can handle a very large where that data can be 1. Inserting to it and 2. Retrieved from it.
Sql Server is also called as Relational Database Management Systems as the data are organised in it using relations called Public Key and Foreign Key. SQL Server can be installed only in Windows Operating System.

The Sql Server Runs as a service in Windows and it can be accessed by a GUI tool called Sql Server Management Studio or Command Prompt.

SQL SERVER - Insert from one table to another Table

In SQL Database, You can probably insert data from one table into another using two methods.
1. Insert Into - This is used when the table to which the data is going to be inserted is already present.
2. Select Into -  This is used when the table to which the data is going to be inserted is not present and is to be created on the go when inserting the data.  

Syntax for Insert Into

INSERT INTO NewEmployeeTable (EmpId)

SELECT EmpId

FROM OldEmployeeTable 

In the above syntax the table NewEmployeeTable is already there and we are inserting EmpId from the OldEmployeeTable into the exisiting NewEmployeeTable.

Syntax for Select Into

SELECT * INTO  NewTableName FROM ExistingTableName

In the above syntax it creates the new table and inserts the data from the existing old table.Thus the insert form one table to another table is done in Sql Server using Tsql. 
Check this post to read how to Insert into Table from Stored Procedure.

SQL SERVER – Insert Into from Stored procedure

In SQL Database, To insert into table from stored procedure you need to create a table with fields and data types which comes as a result of executing Stored Procedure.

1. Create Table

CREATE TABLE Employee (EmpId INT)

2. Run Execute Stored Procedure with Insert into above it

INSERT INTO Employee (EmpId)
EXEC Proc_InsertEmployeeId
GO
You have successfully learnt how to insert into table from the store procedure.


Check this post to read how to Insert from one table to another Table.

About Database

What is a Database?

Database is an organized way of storing information, making it easy to get the information at later time.

Eg, storing organized data in Excel file(.xlsx file extension) is called Excel database. storing data in microsoft sqlserver file(.mdf file extension ) is called Sql database.

What is database software ?

Database Software is a tool to store organized data. Examples of database software will be

Excel, Sql Server, Mysql, Sqllite, postgresql,Apache Cassandra , google big table etc.,

Types of Database

Hierarchical Databases -- data is organized like a Pyramid. Oldest method of storing data.Used in IBM mainframe Computers

Network Databases - same like hierarchical database but this has an hierarchical structure. 

Relational Databases - This is used in sql server where the data are linked by relationships (using keys)

Object Oriented Databases - This kind of database can be used to store variety of media sources such as images,text,videos etc.,

Free Database Softwares

MySQL-- MySQL is a widely used opensource Database.

SQLite-- SQLite is a  opensource Databas which is widely used in Mobiles phones and ipads .

PostgreSQL--  This is another Famous open source database.

All the above three databases are cross-platform which means it support multiple operating systems like linux,Windows,Mac OS X, Solaris , FreeBSD etc.,