2

Context:

I created a dump of a sql table in XML (Including data and schema) using this command from the command prompt.

BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" queryout  C:\data.xml -w -r "" -S localhost -T

Now that I am equiped with the XML File containing all the information that I need, I would like to load this into another database.

Since it is that easy to create this table dump into a xml file, I suppose there must be a simple way to load the XML File into a database (Creates the Table and then loads the data). Does anyone know how to do this?

Disclaimer: I am not a DBA and this is my first experience with BCP, the documentation was talking about creating a Format file but it didn't seem to work for me.

Thanks in advance for your responses

| improve this question | |
  • BCP is a dead-simple tool. It doesn't shred XML data. The only XML it understand is the format file (which maps your data file to your table). So think text file using a separator for the rows and for the columns. That's BCP. (And there's also a binary file format.) So if you need to have the data as XML you need to look elsewhere. Or export the data as text (/w or /c) or binary (/n). But in any case, BCP won't help you create the table. – Tibor Karaszi Apr 3 at 14:24
  • Thanks, based on this information, BCP might not be the way to go since we do not have access to the source database to generate the format file. It is better to code a software that will read the XSD from the header of the file to generate the table and then load the data. – LP. Apr 3 at 15:13
  • I'm very interested in using BCP too import but my source is also xml. I figure Ill just use a stream program to shred it first. What format does the data file have to be in to use BCP? – Dan May 29 at 18:12
3

I wrote a blog post showing some sample BCP commands, including how to create the format file, at https://www.sqlserverscience.com/tools/example-bcp-export-import-commands/

This command should create the format file that is essential for import via bcp:

bcp "dbo.Oranges" format nul -S "FRUIT\PEARS" -T -d "Fruit" -n -f "C:\some\path\Oranges.fmt" -x

dbo.Oranges is the table name. FRUIT\PEARS is the SQL Server instance name. Fruit is the database name.

If you're having trouble with the XML file, I would recommend using the native binary format generated by BCP It ensures the export and import will work without any data loss, and is fully supported across various versions of SQL Server.

| improve this answer | |
  • Thank you max, in the case we do not have access to the initial database to generate the format file, is there a way to do it only based on the XSD from the XML file containing the data? – LP. Apr 3 at 13:56
  • do you know the original table structure? – Max Vernon♦ Apr 3 at 14:14
  • you could import the data from the bcp file into a temporary table setup with a correctly defined XML column, then use SQL Server to shred the xml into the "real" table. – Max Vernon♦ Apr 3 at 14:27
  • As Tibor said in his comment above, it would be far easier if you could get the data in bcp's native format. – Max Vernon♦ Apr 3 at 14:27
  • Unfortunately, we receive the format in xml from an external party and we do not have access to the source database. I believe then the simplest option will be to develop a C# software to read those files and generate the database with its data. Thank you for the clarification. – LP. Apr 3 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.