March 13, 2003 at 6:54 am
Ok, is anyone able to help with an example of how to Bulk Insert XML and also Bulk Export (Copy?) XML?
I would prefer it to be either in VB, VB.NET, C#, or a Stored Procedure. The XML Format just has to be something that I can read in using ADO / ADO.NET...so I guess that means it must have the Schema too.
Any ideas?
March 17, 2003 at 8:00 am
This was removed by the editor as SPAM
March 17, 2003 at 1:58 pm
tymberwyld
Books online has some great examples on how to do this inside SQL Server. Having said that I would opt not to do the XML from inside SQL Server unless you absolutely need to. Sure it works and can be easy to do but it has limitations and I just feel that SQL Server is not the place to be doing this at this time.
SELECT FOR XML EXPLICIT does work well once you have gotten used to it. I'm not sure it's the best performance wise though.
Can you give a better idea of what it is you are trying to do?
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 17, 2003 at 2:15 pm
All I really want to be able to do is Import an XML file into a (blank) Table as fast as possible. I would never want to Bulk Insert unless the Table was Blank, so keep that in mind. I don't actually care about using the Server to "Export" data, I'd rather use the Client for that.
I already know several methods of retrieving data using XML or saving an ADO / ADO.NET DataSet to XML. However, is there a specific format that SQL can use to automatically Import an XML File into a Table, or am I stuck writing my own Synchronize programs?
I already have a Synchronize program to do this, but I am trying to optimize it as best I can. It will actually Sync any DB (as long as each table has 2 specific Columns). The reason I have done this vs using Replication, is that I don't have to worry about Licensing since all my Remote sites use MSDE 2000.
I've gone through almost every Stored Procedure in the Master / System DB, but none seem to be able to do what I want. I wish there were a Stored Procedure that I could either pass an XML File Path to or pass in an XML Stream / String of Data and it would update a Table.
March 17, 2003 at 2:36 pm
There is something in SQLXML that might help you : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp
Tim C.
//Will write code for food
Tim C //Will code for food
March 17, 2003 at 2:49 pm
Thanks for the Reply tcartwright, I'll look into that more when I have a chance!
March 17, 2003 at 2:56 pm
tymberwyld,
Replication should still be an option. According to the following (pulled from MSDN)
A SQL Server client access license (CAL) or processor license is required on the SQL Server database which MSDE replicate with.
It's really no different than your normal SQL Server licencing. IE: You either need to have a processor or per seat license. Snapshot Replication would be the best way to carry out what you are talking about I think so long as your server is correctly licensed.
If you are trying to create your own replication engine I would simply use BCP/DTS and flat files. The problem with using XML is that you bloat the file size that needs to be transfered from one machine to the other.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 17, 2003 at 3:12 pm
Ok, I think I understand. I must have read the Licensing wrong. It seemed like each MSDE machine needed a License if Replication was used. However, how is Replication at handling Deleted Items? For example, the way I have developed my Sync program I am able to do "Full" and "Incremental" synchronizations. It mostly relies on 2 Columns, "Deleted" and "ModifiedDate" to determine which Records need Synced and also which need Deleted during the Sync.
Also, using XML the file sizes drop down considerably when they are zipped. I can take a whole 60MB DB and turn it into 3-5MB of data. This is great for making a small backup of the DB especially since it runs in 2 minutes. I can actually make it even smaller once I remove the XSD Schema (I don't really need that).
The only issue I have with the BCP data is I can't Validate it during Sync and it would take me developing a Parsing alogorithm whereas now I can just use ADO.NET to open a File and start working with it.
You see, the Sync isn't just for Replicating the Central DB to a Remote DB, it also takes into account what was updated on the Remote DB (that may not neccessarily be on the Central), so I can't ALWAYS be doing a Bulk Import. Does Replication accomplish this? If so, how hard is it to setup? I wasn't very successful with setting it up in the past and it seemed to REALLY bloat the size of the Database! Has anyone had a lot of problems with Replication conflicts? The Sync I have takes no overhead, only the time to Sync between DBs and can be setup in 5 minutes.
Thanks for taking the time to explain all this!
March 17, 2003 at 3:26 pm
Yes Merge Replication can do what you are talking about. Yes it will bloat your database size as it adds a rowguid field to each table and views/sps/triggers to the database. If you already have the XML process working then Merge Replication may not be the way to go for you. Merge replication works great with some caveats. Managed Identity columns are a pain in the butt! I can usually set up a new subscriber in 5 - 10 minutes on my system (plus the syncronizing time of about 25 minutes for a 8 gig database). Replication allows you to have some control over conflicts when two users do something different on the same record (IE: one person deletes the record and another changes the value in a field).
Having said all that I would probably use it in the scenario you have described. Sure it takes some knowledge to set up and administer but over all it's not too bad. I probably have a more complicated replication scheme than most but it's been working well for the past 2 years.
Another thing you can do with Replication is use Dynamic subscriptions to filter the data depending on the HostName. This allows you to make it so that a salesman in Ohio only sees clients from Ohio while the saleman from Texas sees the Texas clients.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 17, 2003 at 3:33 pm
Hmm, that sounds good. The application I have developed should be able to be used "Stand-Alone" as well.
If I setup a DB that is Replicated, but then Script it to a new "stand-alone" Client that doesn't need to be "Synced" with a Central DB, how does that work? Do I need to keep a "Stand-alone" copy of my Database "just in case"?
March 17, 2003 at 6:52 pm
Well since I'm lazy I just restore a backup of my publisher and then run a script to drop the replication objects (Triggers/Views/SPs). I don't worry about the RowGuid fields though.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply