Export Table as XML from SQL Express using BCP

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

General SQL Server Forums

New to SQL Server Programming

Export Table as XML from SQL Express using BCP

Author

Topic

gomad2000
Starting Member

9 Posts

Posted - 2011-11-16 : 11:58:27

Is it possible to export a table for an SQL Express database in XML format using the BCP command? If so what does the command look like?

My servername is DAVIDM-TECRA\SF
I use Windows Authentication
The database name is SF_DEMO_AUS
The table is daily

Thanks in anticipation

===========================
If you don't ask ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 12:07:06

its possible

bcp "select * from SF_DEMO_AUS..daily for xml path(),Root('dailydata')" queryout <your xml file path here> -S DAVIDM-TECRA\SF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gomad2000
Starting Member

9 Posts

Posted - 2011-11-16 : 12:57:20

Thanks but this does not seem to produce the required result. I have to include -T to allow windows authentication but the code above gives me an error -Incorrect syntax near ')'
If I use the code
bcp "select * from SF_DEMO_AUS..daily for xml auto” queryout c:zones\testxml.xml -S DAVIDM-TECRA\SF -T
ie replacing the path(),Root('dailydata') with auto it prompts me for a number of values - file storage type, prefix length of field, field terminator, if i want to save file format, host filename
it does give ma an output but not in XML format
I hope that I have explained myself here well enough for you

===========================
If you don't ask ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 12:59:12

i missed ''

bcp "select * from SF_DEMO_AUS..daily for xml path(''),Root('dailydata')" queryout <your xml file path here> -S DAVIDM-TECRA\SF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gomad2000
Starting Member

9 Posts

Posted - 2011-11-16 : 13:58:55

Thanks that is working now but output is still not valid xml format as it will not open with an xml editor. There seems to be some strange code at teh start of the file when I view it in notepad. â Any ideas?

===========================
If you don't ask ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:30:43

quote:
Originally posted by gomad2000

Thanks that is working now but output is still not valid xml format as it will not open with an xml editor. There seems to be some strange code at teh start of the file when I view it in notepad. â Any ideas?

===========================
If you don't ask ....

whats the data table is containing? any fields storing other language characters?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gomad2000
Starting Member

9 Posts

Posted - 2011-11-17 : 05:11:50

The table contains text and numeric values. If I use the -c switch it seems to be correct.

bcp "select * from SF_DEMO_AUS..daily for xml path(‘row’),Root('dailydata')" queryout c:zones\testxml.xml -S DAVIDM-TECRA\SF –T -c

Thanks for your help.

===========================
If you don't ask ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:03:28

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources