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