SQLTeam.com | Weblogs | Forums

Export a table to an XML format


#1

can i export a table to an XML format
thanks


#2

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:

http://www.codeproject.com/Articles/635956/How-to-export-data-from-database-tables-to-an-XML

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

SELECT TOP 10 *
FROM	YourTable
FOR XML RAW

#3

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


#4

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

BCP "SELECT * FROM YourTable WHERE YourCol1 = 'XXX' FOR XML RAW" 
    QUERYOUT c:\temp\YourFilename.xml -S YourServerName  -T -c -r -t

#5

thanks Kristen

maybe it is ok


#6

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


#7

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.


#8

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

I'm going to try