generate a xml file from sql server 2000?

hi there.

i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?

generate xml and cache it and use the xml file rather going to sql server database?

has anybody have any help on this?

the steps invloved:

1) first generate a xml file from table something like this:

select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?
SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?
SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?

which one should i use and how do i access after i gnerate a xml file

thanks.

Do you have a PrimaryKey (or clustered index) on that table? Yes, access such a large table in SQL2000 database would be slow, but you can speed up query by using clustered index seek, which I believe should be much quicker than accessing XML file. You can take a look at this link to learn more about clustered index:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp

|||

i do have primary keys and culstered index

but is that true that, if i access xml file it will boost my access speed?

i can cache my xml file and use from cache?

|||

My recomendation is run some test and look for these two System stored procs in the Master database sp_xml_preparedocument and sp_xml_removedocument because you can use them to do a lot of things with XML in SQL Server 2000. The links below show you several options including the Dataset.ReadXml method. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

http://forums.asp.net/thread/1024186.aspx

|||

after i run the below code:

DECLARE @.hdoc int
DECLARE @.doc varchar(2000)
SET @.doc = '
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE root
[<!ELEMENT root (Customers)*>
<!ELEMENT Customers EMPTY>
<!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]>
<root>
<Customers CustomerID="ALFKI" ContactName="Maria Anders"/>
</root>'

EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc

what do i do after that?

|||The second page of the thread I gave include a sample using repeater and the ReadXml method of the dataset. Hope this helps.