Export XML - SQL Server Forums

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) '

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources