Hi Experts,

Can anybody tell me the code for converting the table
records to a XML document.

I want to write a procedure which will return a string
( varchar(8000) )
containing the xml document.

The Sample XML document i need in the form of string is:

<QATollFreeLocations>
    <LocationName>USA</LocationName>
    <LocationNumber>123456453478</LocationNumber>
</QATollFreeLocations>


Please guide me with an example

Regards,

KG

__________________
KG

As you don't show the table structure it's hard to say but normally I would use the FOR XML method.
Search BOL for "FOR XML". If you are using SQL 2005 then I suggest you concentrate on "FOR XML PATH" which is the newer easier way to return custom structures.

--

Joe (Microsoft MVP - XML)

I am already using "FOR XML"

SELECT LocationName, LocationNumber
FROM QATollFreeLocations
WHERE QAId = 775904
FOR XML Auto, Elements

while using Elements keyword the result i am getting is incomplete.

but how will i store the result into a string variable of type varcha

Regards,

KG

I'm not sure what you mean by incomplete, please show what you mean.
If you are using SQL 2000 then you cannot do anything other than return the results of the query.
If you are using SQL 2005 then you can assign the results as with other variables:
--

Joe (Microsoft MVP - XML)

I think this could be the solution, wch we can use using sql
server 2000.

//template.tpl file

<root>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</root>

//Inbuilt procedure in sql server 2000
exec sp_makewebtask
@outputfile = 'c:\temp\myxmlfile.xml',
@query = 'select * from sysobjects for xml auto',
@templatefile = 'c:\temp\template.tpl'

but here problem i am facing is

"SQL Web Assistant: Could not open the template file"

Can any body help me in resolving this..

Regards,


KG