FAST import/export of XML data into a Microsoft SQL Server database

<< The important thing is that this needs to be very fast. >>

Sure. BCP Utility provides the fastest method to load files in and out of SQL Server.

<< This needs to work for SQL Server 2005 and 2008.>>

BCP is available in both versions with support for XML Format Files.

You have the option to override Collation Restrictions.
You have the option to handle Binary characters too..

Refer BOL on the topic Format Files in BCP Utility.
Revert if you need any more clarifications.

ok I've had a look at this and can't quite get it to do what I want.

The first thing I want to try is exporting data from an existing table, and having the export file in XML format.

I can generate an XML format file using the -x bcp option, but although I use this file to export data, the export file isn't in XML format.

The only examples I can find for exporting data in XML format is if the data is already held in XML format on the database already.

Are you sure BCP can export normal, non-XML SQL Server data in XML format?

I've discovered that using FOR XML and queryout I can export data from a table in XML format, but I'm not seeing any of the binary data. Can you confirm bcp will work with XML in binary data format?

From the beginning itself, BCP and several other processes in SQL Server has support to Native fields aka Binary fields.

Anyhow found out one more option for importing XML files called SQLBULKLOAD which has several advantages as well as faster compared to BCP.

Kindly check out the following links for complete details about SQLBULKLOAD

http://www.sqlservercentral.com/articles/Miscellaneous/replacingbcpwithsqlbulkload/1055/
http://www.sqlservercentral.com/articles/XML/63687/

Just as a note for anyone else reading this thread, rrjegan17 doesn't actually know the answer to my question but is giving me suggestions for things to go and look at.

His first suggestion - bcp - turned out to be a dead-end. He has now suggested two others, SQLBULKLOAD (which is the same as the SQL Server Bulk Load component I referred to in my question) and XMLStarlet.

I have had a look at the SQL Server Bulk Load component. It appears to only to import, not import and export, and I haven't been able to find a way for it to process binary data as yet. XMLStarlet appears to just allow the user to generate a flat text file out of an XML file, from which you can use standard SQL tools for import/export processing. Again, I can't see a way to process binary data.

So I haven't yet had a satisfactory answer to my question.

purplesoup,
   
<< bcp - turned out to be a dead-end >>

BCP is not a dead end for importing XML files by creating XML format files. Because I have did this one earlier successfully. It supports native datatypes and you have to create a format file similar to the one below:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

The Datatype can be specified as Binary or Varbinary or Varbinary(max) based upon your requirement.
This will work for sure. If you would have tried that you would not have said that as a Dead end.

And while spending time to help you, I have found out two methods and since I have not tested that I gave you the link for your reference.

BCP is not definitely a dead end if you have tried. I tried both importing and exporting and it works fine.

I'm sorry for the misunderstanding. I made the comment

"I've discovered that using FOR XML and queryout I can export data from a table in XML format, but I'm not seeing any of the binary data. Can you confirm bcp will work with XML in binary data format?"

and got a reply that said "From the beginning itself, BCP and several other processes in SQL Server has support to Native fields aka Binary fields" and suggested the SQLBULKLOAD component, so I assumed only native mode would support binary data and I should look elsewhere for import and export of binary data with BCP. I'm sorry I misunderstood you.

Regarding your most recent comment. I understand how to create a format file similar to the one you have listed, however I have not been able to import and export binary data with XML using bcp, as I indicated earlier. I am delighted that you are able to do this, could you provide the details on how it works please?

can you please clarify the big picture of the import/export scenario?
is there a special reason is "has" to be XML? -> XML data has big overhead, so not really recommended for large/fast import/export scenario.

Well that is a very good question and one that I am asking myself at present. The reason it is XML is because that was what I was given as the feature requirements from the Business Analyst, and I am working on an approach document on how to implement the feature.

There is a requirement to be able to ship a "scriptable database" which contains text files for creating the schema, and also text files for importing the data. I do know XML is a text format that can handle binary data, and I'm not sure if there are other formats that can do this, so that may have been one reason to work with XML.

At present we ship a backup of the database which is restored on the target server. This has the problem that collation sequences are fixed in the database, instead of being able to use the default for the server.

I hope that makes it a little clearer.

Incidentally just putting the speed part of it to one side, I'm surprised there aren't more tools for being able to import and export data from SQL Server into XML files.

>>I do know XML is a text format that can handle binary data<<
Than you are not going to be able to do it without some other component that will encode the binary data to base64.

Check this sites out for your reference..

http://www.mssqltips.com/tip.asp?tip=1060
http://msdn.microsoft.com/en-us/library/ms189327.aspx

Revert if you face any issues.

ok I have read through these article and I seriously doubt your comment regarding bcp that "I tried both importing and exporting and it works fine" if you are claiming bcp works with XML data.

As I think this link makes clear:

http://msdn.microsoft.com/en-us/library/ms178129.aspx

the XML option in BCP is the ability to specify the format file in XML. The data itself has to be non-XML, therefore it is not suitable for my purpose.

Is anyone able to suggest a way of importing and exporting XML data?

Have you tried using BCP?

that's funny.

Maybe this will help:

http://www.15seconds.com/issue/001102.htm

And probably this:

https://www.experts-exchange.com/questions/24091027/Take-xml-String-in-SQL2000-and-convert-it-inot-an-append-insert-statement.html

Zberteoc's article from 15 seconds explains the various ways FOR XML is used to extract XML data from a database.

This does meet my requirement for extracting/exporting XML data from a SQL Server database, I'm not sure if it meets the requirement of working with large amounts of data, there doesn't seem to be any way of extracting the data in batches or having some sort of progress message to identify how many rows have been processed.

I don't think it meets the requirement that some of the data being processed is in binary format. There is nothing in the article to explain how this would be addressed.

It would be useful when posting links to an article if the person making the suggestion is able to demonstrate some evidence that they have read the article and the question, and so comment on where they think it will meet the needs and if there are any shortfalls. Just saying "maybe this will help" doesn't make it clear in what way it will help.

Regarding the second link, the experts-exchange article, this covers processing an XML document and being able to transpose it into a temporary table from where the data can then be processed to update the database. This assumes the data being imported is of a different structure to the target database, whereas in my question I said the target database has "the same schema" therefore this article appears to not be relevant to the problem I am trying to solve. Again, there was no mention of fast processing, processing large amounts of data or working with binary data.

Thank you for the suggestions, unfortunately I do not think they meet the criteria specified in the question.

purplesoup,

Very often on EE you will see postings that are not intended to cover 100% of the problem but to help the asker getting closer to the solution. I would suggest you to not spend time on explaining how a post covers or not your problem but rather try to use any information is given to you. There are also situations when the correct answer is simply "is not possible" but I am not suggestion that would be the case here, though.

The point is, try to use the pieces that are given to you to build the puzzle because ultimately that is your task.

I feel sorry for people who come along after me and think I got an answer to my question and look at the links suggested, only to find out it doesn't solve the problem.

Although unfortunately I wasted some time reading the links, I can at least help those who come after me to not make the same mistake.

If the links suggested won't solve the problem then you will not pick an answer as the wanted solution. It will not be the first time. Again think of your problems and not of others. And be patient the solution might come when less expected. Nobody says that EE is the answer to any problem, is more like an experience sharing environment that happens to be very efficient at the same time and helpful in the vast majority of the cases.

Now, more to the subject, I think you already have the answer in proportion of 80% or maybe more in all the posts you got so far. To import/export XML to/from SQL server is no big deal and you already saw several solutions in the links provided so far. The bigger challenge is about the special data types like binary, blob etc. Somebody already posted here that in order to be able to do that you need a conversion for binary to Base64 encoding which will convert the binary data to text so it could be wrapped into a XML file. Unfortunately there is no tool or way I know about directly provided in SQL server. You need to build something in a language of your choice.

>>Somebody already posted here that in order to be able to do that you need a conversion for binary to Base64 encoding which will convert the binary data to text so it could be wrapped into a XML file<<
That would be me.  Unfortunately it was ignored, so I assumed that the author was under the mistaken impression they could send binary data using Xml and had no use for Base64.  Hopefully they will come around to that concept now and realize that we at EE cannot provide them with a cookie cutter solution for their specific requirements.  For that there are sites such as www.rentacoder.com

The only suggestions made here are for T-SQL and BCP, and I think from investigations I have made neither of these will do what I want.

I don't know all the tools/APIs available in MS SQL Server but no one has suggested anything else I assume no one knows a way of doing this within the SQL Server environment.

I am not looking for someone to write code, I just want to know if something is possible and what tools to use.

If no one has any further suggestions I will delete the question.

I'm going to accept Zberteoc and other comments as this discussion may be useful for other people who wanted to do the same thing.

Sadly I want to import this 1 XML file... And I found this post...  I got lost in the banter of between everyone...  It was quite interesting...  I find the when I use EE I will either get one of two things....

1.  After 3 - 4 comments someone has given me the answer or between the first few I am able to use my "Brain" and figure it out.

Or

2.  After 10 - 15 comments I award points to people that have tried to help and pointed me in the right direction.  Helping me the most...  Weather it is solving my problem or edumacating me.  Then with what I have learned I turn around and ask the question again, but in a different manner or phrase and usually get an answer.

No one is an Expert in Everything...  So we come here to unite our knowledge to help each other grow.  I am thankful for the people that give me effort of even clicking on my question and reading it.  Perhaps they comment, perhaps they don't... I have several paid EE accounts for my IT consulting customers  and they come here and search and read and learn...  IT Experts and the not so much Experts come together...!!!

I know this question is old and dead...  But I just wanted to give my 2 cents on EE...  I am glad that we are all here to help each other...

Take care everyone and enjoy EE for what is is...  But don't expect it to be like some F - ing computer out of Star Trek...

Daeta says, "Computer End Transmission"

lol