how to convert sql server 2008 database to its xml form?

Question

Hi, I have a database in sql server 2008. I want to convert it in it's xml format. What I have to do? What is the procedure ? Please guide me. I don't want to write the code for this in any language. Is there any facility given in sql server 2008 ?

Answer 1

Take a look at this solution here: http://sqlxml.org/faqs.aspx?faq=29

It uses a stored procedure to export a resultset to XML. It's an old article but it's probably worth checking out. I haven't tested it out so your mileage may vary.

From the site:

You first generate a file with the text-editor like:

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

Save it as c:\temp\template.tpl

Next you open QA and type:


sp_makewebtask @outputfile = 'c:\temp\myxmlfile.xml', 
    @query = 'select * from sysobjects for xml auto',  
    @templatefile = 'c:\temp\template.tpl'

The Result is a XML-File!