Styles using ORDER By and XML

STYLES OF DISPLAYING THE OUTPUT

 In SQL Server, we can display the out in two different styles, this we can achieve through

1. ORDER BY CLAUSE
2. XML Format

1. ORDER BY Clause:

It is used to display the table data in sorted order. The order may be either ascending (or) descending order depending on the specified column. it always appears at the and of select statement

it always appears at the and of select statement

Syntax:

SELECT {*/Columns List} FROM TABLE NAME [WHERE CONDITION]

                        ORDER BY COLUMN NAME {ASC/DESC}

The default order is ascending order. It means if we does not specify any specific order then it automatically takes ascending order.

E.g.: W.A.Q to display employ details in ascending order based on their department numbers (DEPTNO).

SELECT * FROM EMP ORDER BY DEPTNO ASC

E.g.: W.A.Q to display employ details in descending order based on their employ numbers (EMPNO).

SELECT * FROM EMP ORDER BY EMPNO DESC

E.g.: W.A.Q to display employ details in descending order based on their salaries (SAL)

SELECT * FROM EMP ORDER BY SAL DESC

E.g.: W.A.Q to display employ names and their salaries in descending order based on their salaries (SAL).

SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC

E.g.: W.A.Q to display 10^th department employs in ascending order based on their employ names (ENAME)

SELECT * FROM EMP WHERE DEPTNO=10 ORDER BY ENAME

3.      XML Format

It is new feature in SQL Server 2005 version which will display the table data in

XML format which in between open tag ‘<’ and closed tag ‘/>’.

This format basically useful to the .Net developers

Syntax : SELECT {*/ Columns list} FROM TABLENAME FOR XML AUTO

Ex: SELECT * FROM EMP FOR XML AUTO

The above select statement will generate a file preceded by xml, if you click on that file then automatically emp table data will be converted into XML format