Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Export XML
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marcel Schoof
Starting Member

2 Posts

Posted - 10/23/2012 :  06:59:34  Show Profile  Reply with Quote
I have to export a complete database to XML. In the Import Export Wizard i choose 'SQLNAtive' and the database.
In Destination I choose SQLXMLOLEDB.4.0 but then I have to give the properties.....beginning with datasource and what to put there??

I anybody has a script (or SP) that will work something like

for each tbl in sys.tables
select * from tbl
for XML RAW to outpoutfile tablename.xml

than that would also be fine.



Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/23/2012 :  07:44:29  Show Profile  Reply with Quote
I have not tried the SQLXMLOLEDB.4.0 destination, so not able to comment on that.

The second option you proposed can be done using the undocumented sp_MSForeachTable stored procedure like this:
sp_MSForeachTable 'select * from ? for xml raw'

If you want to insert the data into a single table, you can modify it like this:
CREATE TABLE dbo.TableXMLs(tablename VARCHAR(255), XMLDATA XML);
sp_MSForeachTable 'INSERT INTO dbo.TableXMLs SELECT ''?'', (select * from ? for xml raw) '
Go to Top of Page

Marcel Schoof
Starting Member

2 Posts

Posted - 10/23/2012 :  11:04:36  Show Profile  Reply with Quote
With a lot of programming I've found a sollution:

use db
select * from sys.tables

Copy datagrid in Excel
on worksheet2!a1 (Blad1 is the dutch name) type:

="bcp @SELECT * FROM database.."&Blad1!A1&" FOR XML RAW@ queryout d:\"&Blad1!A1&"1.xml -Sservername -T -c -r"

on worksheet2!b1 type:
="copy/b rootopen.txt+"&Blad1!A1&"1.xml+rootclose.txt D1:\"&Blad1!A1&".xml"

copy the formulas for every tablename

Copy column A to sheet 3, paste special values
go to the first free row
Copy column b to sheet 3, paste special values

save sheet 3 as textfile
find @ and replace "
save as filename.cmd

in directory (somewhere on the SqlServer) from with you start the cmd add two files



start commandprompt and run filename

''d:\= drive and directory where the first xmlfile is stored
''d1:\= drive and directory where the altered xmlfile is stored
''S must be followed with your servername

Hope this is clear enough
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000