Link to home
Start Free TrialLog in
Avatar of soozh
soozhFlag for Sweden

asked on

TSQL to export data in XML format

Hello,

Using MS SQL Server 2008.

I have been asked to create an XML document by exporting data from a database.

I have been given an XSD defining the data to be exported.

I know where the data is held in the database.

What would be the best approach to generating the file?  It will contain many "rows" of data.

Thanks.
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

if you looking for how to do it in SQL server ..there are three option...

Through SSIS
Uisng BCP
Uisng SQL CMD

You can get plenty of example on Google.... other wise further let me know your thoughts....


Thanks,
Saurabh
Avatar of soozh

ASKER

THanks for the response.  I think i will have to use SQL CMD.

Are there recommend methods/techniques to use?

/richard
Get some idea from here
http://msdn.microsoft.com/en-IN/library/ms162773.aspx

Consider below example for xml export

EXECUTE master..xp_cmdshell 'SqlCmd   -S SAURABHBHADURIA -d master  -E  -i d:\query.sql  -h -1 -y 0   -o d:\out.xml '



in d:\query.sql File I have save my query ..which is below..

 set nocount on
:XML ON
  SELECT top(50) name,type FROM master.sys.objects s for xml Raw ,elements , ROOT ('EMSExport')

Thanks,
Saurabh
Also check your xml output format....
You can get the desired format   by changing the Query...using for xml clause
Avatar of soozh

ASKER

i have seen that its possible to use an XSD to validate the output, and XSD to define where the data is to be collected from.

Is it possible to combine these two somehow in one query?
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial