XML columns/variables

CREATE TABLE [dbo].[SampleXmlTable](
[GroupID] [int] NULL,
[Books] [xml] NULL
) ON [PRIMARY]

--select a node

SELECT
GroupID,
Books.value('(catalog/book/author)[1]','nvarchar(200)') as Author,
Books.value('(catalog/book/price)[2]','nvarchar(200)') as Price
FROM
[dbo].[SampleXmlTable]

GroupID Author Price
1 Gambardella, Matthew 5.95
1 Kress, Peter 36.95

--expand

declare @SampleXml Xml

SELECT
    DataTable.books.value('author[1]','nvarchar(100)') AS author,
DataTable.books.value('title[1]','nvarchar(100)') AS title,
DataTable.books.value('price[1]','float') AS price
FROM
@SampleXml.nodes('//catalog/book') AS DataTable(books)

Send Mails Using SQL

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test_Profile', -- should create
@recipients = indikask@gmail.com,
@copy_recipients = indikask2@gmail.com ,
@body = 'test mail from blogg',
@subject = 'test'
--@file_attachments =

MERGE Sample

CREATE TABLE [dbo].[source](
[id] [int] NULL,
[name] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[source] ([id], [name]) VALUES (5, N'indika')
INSERT [dbo].[source] ([id], [name]) VALUES (6, N'kannangara')
INSERT [dbo].[source] ([id], [name]) VALUES (2, N'saminda')

CREATE TABLE [dbo].[target](
[id] [int] NULL,
[name] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[target] ([id], [name]) VALUES (2, N'saminda')
INSERT [dbo].[target] ([id], [name]) VALUES (3, N'sunny')
INSERT [dbo].[target] ([id], [name]) VALUES (6, N'kanna')


SELECT * FROM [dbo].[source]

id name
5 indika
6 kannangara
2 saminda

SELECT * FROM [dbo].[target]

id name
2 saminda
3 sunny
6 kanna

MERGE INTO dbo.target T
USING (
SELECT [id]
      ,[name]
  FROM [test].[dbo].[source]) S
ON (S.[id]=T.[id] )
WHEN MATCHED
AND NOT S.[name] = T.[name]
THEN  UPDATE SET
[name] = S.[name]
WHEN NOT MATCHED THEN
  INSERT (
[id]
  ,[name])
  VALUES (
S.[id]
  ,S.[name])
WHEN NOT MATCHED BY SOURCE
THEN DELETE;


SELECT * FROM [dbo].[target]

id name
2 saminda
6 kannangara
5 indika