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

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

Marcel Schoof
Starting Member

Netherlands
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
next

than that would also be fine.

thanks!

Marcel

sunitabeck
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

Netherlands
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

Rootopen.txt:
<root><enter>

Rootclose.txt
<enter></root>

start commandprompt and run filename

comment:
''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  
 New 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.12 seconds. Powered By: Snitz Forums 2000