SQLTeam.com | Weblogs | Forums

Export a table to an XML format


can i export a table to an XML format


I've always found it a bit of a nightmare - marrying up the XML structure and the Data Selection ...

You can do it in SSIS:


or maybe just a simple SELECT statement would work for you?

FROM	YourTable


Than you Kristen
select the apparently walking
How to exploit the result (I have a software which imports xml data type)

I could save it as csv and excel in reading


One route would be to use BCP to run a query and save the results to a file:

    QUERYOUT c:\temp\YourFilename.xml -S YourServerName  -T -c -r -t


thanks Kristen

maybe it is ok


can you help me to Reverse transaction: Import from XML to sql


If it is a recurring task them you might want to use the XML Bulk Loader - that will require that you set up an XSD schema mapping file.

if its a one-off job my preference would be to throw together some SQL using OPENROWSET to get teh XML from a file into a Table (with a Column of datatype XML) and then use OPENXML to parse from that XML column into whatever I want.

e.g. https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

But I find it takes much longer working with XML in this way than, say, a CSV file. probably a good thing for repeat tasks that have complex data (i.e. which would make a CSV file struggle!) but in those more complex instances we strive to get a direct database-to-database connection, instead of exporting-from-one and importing-into-another as that always seems to be fraught with "tinkering" both tog et it working and, then ongoing, to keep it working.

I don't use SSIS, but that may well have a much better, more easily configured, set of tools for buildnig an XML import routine.


I hope I get out, I'm not strong in English (French)

I'm going to try