SQL Server Performance Forum – Threads Archive

export to xml

Hi Guys,
R there any softwares available to export the whole of data to xml format from sql server database ? The database size is around 7gb and there are more than 50 tables. Please let me knw if u have any info regarding this… thank you regards
abhi

---

you could autogenerate SELECT .. FOR XML statements for each of your tables. 7GB and 50 tables isn’t too bad. something like. SELECT ‘SELECT * FROM ‘ + TABLE_NAME + ‘ FOR XML’
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND TABLE_NAME <> ‘dtproperties’
ORDER BY TABLE_NAME Then save the generated script and run it from osql. Tom Pullen
DBA, Oxfam GB

---

Hi,
Im a beginner in MSSQL Server, sorry if im asking lame doubts.
Should i run the piece of query u posted in Query Analyser ?
what is osql ? is it a part of MSSQL ?
From ur reply i understand that we donot need any software to
export to xml, but writing queries would do. am is right ? thanks

---

Run the script I provided in Query Analyser, and it will generate another script. You can then save and run the generated script. osql is a command-line utility which allows you to run input scripts from batch files. There are options with the FOR XML clause of the SELECT statement, such as FOR XML RAW, FOR XML AUTO and FOR XML EXPLICIT which you may need to investigate. Look in SQL Server Books Online for details. Tom Pullen
DBA, Oxfam GB

---

]]>