Storing Tables In Xml file.

[Miss Kelly]

Hi Friends,

I have Two Tables

Table A
Col1
Col2

TableB
Col1


How can I design my XMl file for this tables?

Please Help Me.

[kelfro]

The simplest way to design XML is by using the dataset in vb.net.

1-create the query as follows,

"SELECT Col1, Col2 FROM [Table A]
UNION
SELECT Col1,NULL FROM [Table B]"

2-Get the result in dataset1 in asp.net

then

dataset1.WriteXml(path of file to save);

by using this two methods you can easily create the xml file.

[Katty]

There are lots of ways to create the xml design.

One way is already mentioned in the thread. That is loading the data into DataSet and use WriteXml method to get the xml as a output.

Second is to get the xml directly from the SQL Server itself using FOR XML clause. If you are using SQL Server 2005 and above you can utilize the FOR XML Path to get required xml Structure.

Following is the sample using For XML Clause

SELECT A.Col1, A.Col2, B.Col3 FROM TblA A INNER JOIN TblB B ON A.Col1 = B.Col1 FOR XML PATH

You can also look into FOR XML AUTO, FOR XML RAW, FOR XML EXPLICIT. Which ever is applicable to your requirement you can use that.

Finally, If you are just looking to get only the xml then I will recomend to go with the FOR XML Clause than going with the DataSet approach which is a Costly operation than FOR XML Clause.