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

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Export XML

Author  Topic 

Marcel Schoof
Starting Member

2 Posts

Posted - 2012-10-23 : 06:59:34
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 07:44:29
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 - 2012-10-23 : 11:04:36
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
   

- Advertisement -