I recently came across with a interesting case in our project . Let me explain the scenario in detail .To replicate the case of our project i have created a table named temp in my database . The below is the script for creating and for inserting data in the table.

create table temp (empid int ,fname varchar(max) , lname varchar(max) , location varchar(max) )

insert into temp values ( 10, 'chris', 'chob', 'denver')
insert into temp values ( 11, 'Vinay', 'Kumar', 'downtown')
insert into temp values ( 12, 'chris', 'james', 'Hyderabad')
insert into temp values ( 13, 'praveen', 'Kumar', 'mumbai')
insert into temp values ( 14, 'Lakshman', 'Rao', 'Delhi')
insert into temp values ( 15, 'venky', 'jacob', 'Chennai')

Up to this it was fine . But after some time the employee ‘praveen’ has moved from mumbai to kolkata .Often in data warehousing projects there exists slowly changing dimensions . In slowly changing dimensions II we have to maintain the history data as well . so even if employee praveen has changed to kolkata we can’t update that record ,so we need to insert a new one for that .

insert into temp values ( 13, 'praveen', 'Kumar', 'kolkata')
After the inserting the new record it has there was no primary key in the table .previously in the table temp we thought that empid would be primary key but as it is a SCD II there should be one other column which has a identity value.so we added a new column named id to  the  table .

alter table temp add id int


Table data before updating

The left side image would give give the structure of  data in the table .

As there is no primary key  we need to update the column id  with a unique value . Here we can set the column id as identity while creating it but to explain the usage of update i haven’t done that .

Then i wrote the below script

declare @i int  =1
update temp set @i= id = @i+1
After running the script if we see the data in the table then .

Update_identity

we can set any value to @i ,so here i had set it to 1 .

This is  another way how to use update statement  for  updating a column with identity value .

Hope you all got one interesting point  in SQL.

Recently  one of my teammate has asked me a question  . Till at that point of time  i thought this question was well known to everyone  but  surprisingly many of my colleagues were unable to answer  it right away . so i thought of writing  a post on this .

Now the question  is : ” How to get the  current user name who logged into sqlserver ” ? ..

Well the answer is really simple .  Execute the below command which would get the desired results .

select   SYSTEM_USER as username

Current user details

In the adjacent o/p we can see the user  name through i was logged in  . I have logged in using windows authentication , you can also login through sql authentication .

ii) Suppose if we want to change the user context from one to another i.e if we want change from one user to another user login  the we should use  EXECUTE command .

In the snapshot we can see that the current user  connected to Sql Server  is ‘Rajesh-pc\Rajesh ‘  as i logged in using windows authentication .Now we can move from current to user test then

EXECUTE  as user  = 'test'.

Here test is also one of the useraccount .

Hi Everyone ,

Today I want to share some tips on how to create XML files  using FOR XML clause  in  SQL SERVER.

Sql server  2008 Provides different methods to generate  XML files   by using  FOR  XML clause .The different methods available  are  listed  below

1)      FOR XML RAW

2)      FOR XML AUTO

3)      FOR XML PATH

Now we will see each of them in detail. Before that we will first see each of them in brief.

RAW [ ('ElementName') ]

It is useful for ad hoc FOR XML querying when the structure of the resultant Xml is not known from beginning. While using this you have to be very careful because the result of this can change dramatically when the underlying data table structure is changed.

It takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag. You can optionally specify a name for the row element. The resulting XML output uses the specified Element Name as the row element generated for each row.

AUTO

This is also useful for ad hoc FOR XML querying. This is mainly useful when you need to map the XML result back to the original columns in the source table. Its result can also change dramatically when the underlying data table structure is changed.

It Returns query results in a simple, nested XML tree. Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.

PATH [ ('ElementName') ]

This is designed for explicitly defining XML result structure. This is more appropriate option than above two while you are working in production environment because you always know the resultant XML structure.

It generates a <row> element wrapper for each row in the result set. You can optionally specify an element name for the <row> element wrapper. If an empty string is provided, such as FOR XML PATH (”) , a wrapper element is not generated. Using PATH may provide a simpler alternative to queries written using the EXPLICIT directive.

Let us jump  to  the implementation  of them . In the below examples  i  have used AdventureWorks Database . You can find the AdventureWorks Database from the  Microsoft Codeplex at http://msftdbprodsamples.codeplex.com/

First we will see how to work with XML RAW.

– 1)For Xml RAW
SELECT * FROM   HUMANRESOURCES.EMPLOYEE  FOR XML RAW(‘EMPLOYEES’)

O/P:

If u observe every  row in the table  is generated  as  a seperate tag in the XML File  and all the column values in the table are represented as  attributes to the XML Row tag.The element name specified in the RAW(‘ElementName’) would be the tag name for every row in XML file generated . We can further modify the structure of XML generated .

–i) Adding Root Element
SELECT  EmployeeID,ContactID,ManagerID,Title  FROM   HumanResources.Employee FOR XML RAW (‘EMPLOYEE’)  ,ROOT(‘EMPLOYEES_DETAILS’)

O/p:

By using Root directive we can add root element to the resultant XML.

–ii) Creating  Elements Instead Of Attributes

SELECT  EmployeeID,ContactID,ManagerID,Title  FROM   HumanResources.Employee FOR XML RAW (‘EMPLOYEE’)  ,ROOT(‘EMPLOYEES_DETAILS’)  , ELEMENTS

Elements Directive would generate XML Elements rather than XML Attributes

For Xml with Element Directive

Now we will go to the For XMLAuto

–  2)For Xml Auto

SELECT * FROM  HUMANRESOURCES.EMPLOYEE  as Emp FOR XML AUTO

One thing we should note here is , For XML Auto we cannot specify Root Directive  and this would generate o/p similiar to XML RAW(when no directive is used ).

O/p:

Now we will go further  into the XML Path

–  3)For Xml Path

Xml Path is  mostly used for xml generation as it would allow us to control the structure of XML file generated.

SELECT * FROM HUMANRESOURCES.EMPLOYEE  FOR XML PATH(‘EMPLOYEE_DETAILS’)  ,ROOT(‘EMPLOYEES’)  ,TYPE

XML File Generated by FOR XML PATH with ROOT Directive

Now let’s understand how our FOR XML query is interpreted by SQL server and how these XML nodes and sub nodes and attributes are created in resultant XML. This is managed by certain rules which are explained in brief.

1)     Column names in SELECT statement are case sensitive. In our case Employeeid and EMPLOYEEID will be two different scenarios.

2)    Columns with name beginning with (@) sign are created as Attribute of every row element created in your result.

3)     Columns which are not starting with (@) sign and also not have forward slash (/) are mapped as sub elements of row elements.

4)    Columns which has one or more (/) but don’t have (@) sign are mapped as hierarchical data below row element.

5)      Columns with same prefix are grouped together as sub elements of it at the same level of hierarchy.

Let us see an example on the above rules .

–Illustration of above rules

SELECT emp.EmployeeID as  “Employee/@EmpID” ,
emp_dept.DepartmentID as  “Employee/Emp_Dept”,
emp_dept.StartDate,
emp_dept.EndDate
FROM HumanResources.EmployeeDepartmentHistory  emp_dept  inner join  HumanResources.Employee  emp
ON  emp_dept.EmployeeID = emp.EmployeeID
FOR XML  PATH (‘EMP_DETAILS’)  , ROOT(‘EMPLOYEES’)  ,ELEMENTS  XSINIL

In the above query if you have noticed that we used ELEMENTS XSINIL. As it looks from name it will create an XML element for those values which have null values in database. If we don’t use this and let’s say in our example EndDate column is blank in  the database for certain employees and then if we create  xml file without ELEMENTS XSINIL then the result would be

XML File Generated by FOR XML PATH without ELEMENTS XSINIL Directive

If you observe there is no tag named EndDate for some employees in the XML  generated . To maintain proper XML Structure we have use ELEMENTS XSINIL in those cases .

The o/p of above query would be  :

XML File generated by FOR XML PATH using ELEMENTS XSINIL

Hope You all understood the concepts of FOR XML in sqlserver 2008 . I will come back with much more interesting ones in coming future .