Storing and Retirving Data in XML format from sql server

Retrieving and storing data in XML format in Sql Server 2000.

Suppose you have 20 X 20 controls on your page. And want to send all the data to sql server then create xml for it.

Example :

Let there are A,B,C,D,E,F ……….. controls .

Then create xml for it as

Through String builder as

StringBuilder xmlData = new StringBuilder();

xmlData.Append("<Column>");

xmlData.Append("<ColumnVal ");

xmlData.Append(" A=");

xmlData.Append("'0'");

xmlData.Append(" B=");

xmlData.Append("’1’");

xmlData.Append(" C=");

xmlData.Append(" 2=");

xmlData.Append(" D=");

xmlData.Append(" 3=");

xmlData.Append(" E=");

……………………………………………………………………………………

……………………………………………………………………………………..

……………………………………………………………………………………

xmlData.Append("</Column>");

xmlData.Append("</ColumnVal ");

And at last it will created as :

<Column ColumnVal=1 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

<Column ColumnVal=2 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

<Column ColumnVal=3 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

<Column ColumnVal=4 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

………………………….

………………………….

………………………….

<Column ColumnVal=20 A=’val_A’ B=’val_B’ C=’val_C’ D=’val_D’ E=’val_E’ F=’val_F’………………….></ Column>

send this string into DataBase in as xml datatype.

And write query for inserting into datatable.

insert into YourTableName(A,B,C,D,E,F,G,H,…………………………… ColumnVal)

SELECT A,B,C,D,E,F,G,H, ColumnVal FROM OPENXML (@idoc, '/XML/ Column',1)

Retriving data from database.

It will return data in xml format.

CONVERT(xml,(select * from YourTableName where ‘yourcondition’ for xml raw,Elements,ROOT(' Column '))) as ColumnValue

Clustered Index vs Non Clustered Index

Clustered Index | Non Clustered Index

Indexing is used to get fast retrival of data. The indexing which is done on physical storage is known as Clustered Indexing. And an indexing which is performed logically is know as Non Clustered Indexing.

Clustered Indexing can be only one per table as we phyisically can arrange in one way only, while a table can have 249 Non Clustered Index.

Indexing should be used on table which not very frequently having update operations on it. There are many ways which can be used to get more performance while retrieving data.

Covering Index is --> when you create an index on fields which you are also using in select query, is know as covering index.

For ex suppose
Employee Table
---------------
EmpID int
EmpName
EmpDept
EmpDesing

----------------
If you have a query which only select EmpID and EmpName, then we can create a non clustered index with these two fields only, and it gains our performance while retrieving data.

Get all the triggers for a Database

To get all the triggers list and their scripts run the script below. View the result in file view mode.

SELECT [text]
FROM sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'TR'

Getting all stored procedures scripts from a Database

By using Cursor
declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)

declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc

FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc

Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.

From sysobjects
This is better option to get all stroed procedures from a database.

SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'