stored procedure for insert into Table from XML Parameter in SQL Server.

--–@XmlData='<Content>

--–<Name>Suneel</Name>

--–<Email>sk@gmail.com</Email>

--–<Mobile>99889898989</Mobile>

--–<Item Course="HSC" Year="2004″ Mark="68 %"></Item>

--–<Item Course="SSC" Year="2006″ Mark="70 %"></Item>

--–<Item Course="B.Sc" Year="2009″ Mark="72 %"></Item>

--–<Item Course="MCA" Year="2012″ Mark="75 %"></Item>

--–<RegNo>70123</RegNo>

--–</Content>'

CREATE PROCEDURE TestProcToInsertUsingXml

(

@XmlData ntext=null

)

AS

BEGIN

SET NOCOUNT ON;

declare @TempXML xml

set @TempXML=CONVERT(xml,@XmlData)

declare @hDoc int

EXEC sp_xml_preparedocument  @hDoc output,@TempXML

Declare @Email varchar(50),@RegNo varchar(10)

select @RegNo=Q.RegNo

from OPENXML (@hDoc,N'/Content')

WITH(

RegNo varchar(10) 'RegNo'

)Q

IF NOT EXISTS(SELECT  RegNo FROM tblStudent where RegNo = @RegNo)

BEGIN

--— insert Into Registration Table

insert into tblStudent(StudentName,EmailId,MobileNo,RegId)

select Name,Email,Mobile,RegNo

from OPENXML (@hDoc,N'/Content')

WITH(

Name   varchar(10) 'Name',

Email  varchar(10) 'Email',

Mobile varchar(10) 'Mobile',

RegNo  varchar(10) 'RegNo'

)Std

set @RegNo=SCOPE_IDENTITY();

--— Insert Into Qualification Table

insert into tblStudentQfy(RegNo,Course,PassingYear,MarkObtained)

select @RegNo,Course,PYear,Mark

from OPENXML (@hDoc,N'/Content/Item')

WITH(

Course varchar(10) '@Course',

PYear  varchar(10) '@Year',

Mark   varchar(10) '@Mark'

)Qfy

END

else

BEGIN

Update  tblStudent

SET Name =s.Name,

Email=s.Email,

Mobile=s.Mobile

from OPENXML (@hDoc,N'/Content')

WITH(

Name    varchar(50) 'Name',

Email   varchar(10) 'Email',

Mobile  varchar(10) 'Mobile',

RegNo   varchar(10) 'RegNo'

) s

where @RegNo=RegNo

END

END

Stored Procedure to return XML data from select query in sql server.

--— =============================================

--— Author:ABC

--— Create date: Date

--— Description: Decs

--— =============================================

CREATE procedure   sp_GetEmployeeList

(

@EmpId int=null

)

as

begin

select  EmpId,Name,City,Telephone

from Employee

where   EmpId=@EmpId

order by Name

for XML AUTO, ROOT('Employees')

end

Send alert email from database using CURSOR.

--— =============================================

--— Author: S K

--— Create date: Date

--— Description: Desc

--— =============================================

CREATE PROC Sp_AlertMail

AS

declare @EmpId varchar(50)

Declare @Name varchar(50)

Declare @Email varchar(50)

Declare @Moblie varchar(50)

Declare Mail_Alert Cursor For

SELECT EmpId,Name,Email,Moblie FROM Employee

WHERE  DATEDIFF(DAY,CONVERT(DATETIME,CONVERT(DATE,GETDATE())),CreatedDate) IN(1,2)

OPEN Mail_Alert

FETCH NEXT FROM  Mail_Alert Into @EmpId,@Name,@Email,@Service,@Moblie

WHILE(@@fetch_status=0)

BEGIN

begin

Declare @Body varchar(max)

SET @Body = 'your text….'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Alert_From_Database',

@recipients=@EMAIL,

@copy_recipients ='abc@gmail.com' ,

@subject = 'Alert Mail',

@body = @Body,

@body_format = 'HTML';

End

FETCH  Mail_Alert Into @EmpId,@Name,@Email,@Service,@Moblie

END

CLOSE Mail_Alert

DEALLOCATE Mail_Alert

Filter Record from table if data stored in comma (,) seprated.

CREATE TABLE TBL_USER

(

 id int,

 LanguageKnow varchar(300)

)

insERT INTO TBL_USER VALUES

(1,'HI,EN'),

(2,'HI,EN,FR')

I WANT TO FILTER THOSE RECORDS THAT  CONTAINS 'HI,FR'


Query to filter-

DECLARE @ListValue varchar(50)='FR'

SELECT * FROM  TBL_USER T WHERE

1 = case when isnull(@ListValue,0)='' then 1 when isnull(@ListValue,0)!='0'

and (exists(select A.Item from SplitString(@ListValue,',') A

inner join (select Item from SplitString(T.LanguageKnow,',')) B on A.Item=B.Item))then 1 else 0 end



You need to create below  spilt string function- 

---Function ———–

CREATE FUNCTION [dbo].[SplitString]

(

@SplitStr nvarchar(max),

@SplitChar nvarchar(5)

)

RETURNS @RtnValue table

(

Item nvarchar(max)

)

AS

BEGIN

Declare @Count int

Set @Count = 1

While (Charindex(@SplitChar,@SplitStr)>0)

Begin

Insert Into @RtnValue (Item)

Select

Item = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))

Set @Count = @Count + 1

End

Insert Into @RtnValue (Item)

Select Item = ltrim(rtrim(@SplitStr))

Return

END

Insert / Fech Data into database using conncection object.

strConnection = objBase.getConnectionString();
using (sqlCon = new SqlConnection(strConnection))
{
sqlCommand = new SqlCommand();
sqlAdapter = new SqlDataAdapter();
sqlCommand.CommandText = “SP_NAME”;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlCon;

sqlCommand.Parameters.Add(“@USerID”, SqlDbType.VarChar).Value = USERID
sqlCommand.Parameters.Add(“@QueryType”, SqlDbType.VarChar).Value = QueryType;

#region– For Select DATA
sqlAdapter.SelectCommand = sqlCommand;
if (!sqlCon.State.Equals(ConnectionState.Open))
sqlCon.Open();
sqlAdapter.Fill(myDS);
#endregion

#region —– For Insert
// Add output Parameter

SqlParameter param = new SqlParameter(“Output”, SqlDbType.VarChar, 20);
param.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(param);

if (!sqlCon.State.Equals(ConnectionState.Open))
sqlCon.Open();

iTripId = sqlCommand.ExecuteNonQuery();
sMsg = param.Value.ToString();
#endregion
}

WHAT IS JSON? Comparison between XML and JSON.

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language.

JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language.

JSON is built on two structures:

• A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.
• An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence. 

Comparison between XML and JSON

1. JSON is smaller than XML, and faster and easier to parse.
2. XML is human-readable data interchange specified in a document format, while JSON is human-readable data interchange data format.
3. JSON is not extensible because it does not need to be. JSON is not a document markup language, so it is not necessary to define new tags or attributes to represent data in it.
4. JSON has the same interoperability potential as XML.
5. JSON is much simpler than XML. JSON has a much smaller grammar and maps more directly onto the data structures used in modern programming languages.