reading xml in sql server

So some times we want to read xml file in sql server. I am passing list of details to sql server using xml file format. It reduce several db calls when we passing list of data.

Suppose we have List of Students. In List of Student we have to loop it and fire db calls to save data. To overcome this I create xml of List of Student and Pass it to sql server with one db call and then in my stored procedure I will handle those data as follow.

DECLARE @Xml XML
DECLARE @HDoc INT

SET @Xml = '<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Student>
<Id>1</Id>
<FirstName>jeevan</FirstName>
<LastName>JLast name</LastName>
</Student>
<Student>
<Id>2</Id>
<FirstName>john</FirstName>
<LastName>johnLast name</LastName>
</Student>
</ArrayOfStudent>'
EXECUTE SP_XML_PREPAREDOCUMENT @HDoc OUTPUT,@Xml

SELECT [Id],
[FirstName],
[LastName]
INTO #Temp
FROM OPENXML(@HDoc, '/ArrayOfStudent/Student', 1)
WITH (
[Id] INT 'Id',
[FirstName] VARCHAR(400) 'FirstName',
[LastName] VARCHAR(400) 'LastName'
)

EXECUTE SP_XML_REMOVEDOCUMENT @HDoc

SELECT  *  FROM  #Temp

PIVOT in SQL SERVER

One of the more complex statements that we can execute inside sql server is a PIVOT statement.

What it does? 

PIVOT statement allows us to convert what would normally be row data into column data.

Sounds Crazy…

In simply let’s imagine we have a table as follow.

In above table years are in rows. Let’s suppose we want to get data by years. Now it’s time to use PIVOT. Using PIVOT we can convert years into columns.

PIVOT syntax

SELECT <NonPivot>
, <FirstPivotedColumn>
, …
FROM <Table containing data>
PIVOT (FUNCTION(<data column>)
FOR <List of pivoted columns>)
AS <alias>

Let’s do

SELECT * FROM @Table
PIVOT (SUM(Sales)
FOR [Year] IN([2010],[2012])
)AS pvt

The we get result

Paging in sql server

It is best idea to do paging in server side. From Sql Server 2012 we have useful paging mechanism

We can use FETCH and OFFSET to paging in sql server.

FETCH -> indicates number of rows to retrieve

OFFSET-> indicates the number of rows to skip

Syntax for Paging

SELECT <columns>
FROM <tables>
ORDER BY<columns>
OFFSET x
FETCH NEXT y ROWS ONLY

x means number of record to skip and y means number of record to retrieve

Example

SELECT * 
FROM product 
ORDER BY productId 
OFFSET 20 ROWS 
FETCH NEXT 20 ROWS ONLY

In this it skips first 20 records and fetch next 20 rows

But in this some restrictions are there

• ORDER BY is required
• TOP is not allowed

CTE in sql server

CTE stands for Common Table Expression. It is introduced in sql server 2005.

Actualy it is not a terrible thing as we heard. We can consider it as inline view or temporary table.  CTE allows us to do exact same things like what we doing using views.

Benefits:

• Breakdown complex queries
• Avoid sub queires
• Simply certain syntax

Syntax for CTE

WITH <cte name>[(columns)]

AS(

<SELECT statement>

)

Example

WITH SalesData(TotalSold,Year,Branch)
AS(
SELECT SUM(Total) AS 'TotalSold',
       YEAR(OrderDate) AS '[Year]',
       b.BranchName
 FROM Order o INNER JOIN 
 Branch b 
 ON o.branchId = b.branchId
 GROUP BY YEAR(OrderDate),b.BranchName
)

Then we can do any operations to SalesData CTE as we doing to a View.

Like : SELECT * FROM SalesData

Note: CTE column specifying is optional . if not specified it takes select query result columns

We can use it for short hand for sub-query. More over there are many other benefits in CTE. I noted a here very basic usage.

Delete duplicate rows in a table in Sql server

When I am developing I came across to delete duplicate rows in a table. This is solution I have applied. I mentioned it in here coz it may help you.

DECLARE @Table AS TABLE([Id] INT IDENTITY(1 ,1) ,NAME NVARCHAR(50))</code>

INSERT INTO @Table( NAME )

SELECT 'jeevan'

UNION ALL

SELECT 'jeevan'

UNION ALL

SELECT 'Nimal'

UNION ALL

SELECT 'Kuma'

UNION ALL

SELECT 'Kuma'



;WITH cte AS (

SELECT NAME

,ROW_NUMBER() OVER(PARTITION BY C.NAME ORDER BY [Id]) AS [RowId]

FROM   @Table AS C

)


DELETE FROM

FROM   cte

WHERE  [RowId]>1