SQLXMLBULKLOAD question: Does it INSERT only, or can it UP..

[Using assembly Interop.SQLXMLBULKLOADLib, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=null]

Hello all,

A piece of software that was built for us by a 3rd party uses
SQLXMLBULKLOADLib to import records from an XML feed into our database. The
XML feed includes previously-imported records that may or may not have
changed, as well as new records. When we run the bulkload utility, it
inserts duplicate records for those previously-imported, so to avoid this,
we have resorted to emptying out each table prior to running the bulkload
utility. This is not desirable because the tables often contain records that
were added through another source, such as the web interface.

I wonder if there isn't a way to accomplish the bulkload without dumping the
contents first? Each record contains a unique field that can be used as a
primary key, however, the original developer did not specify the field as a
primary key in the table. If I set the field as a primary key in the table,
can the bulkload be configured so that when it processes the nodes in the
XML file, it updates existing records and only inserts when the record
doesn't already exist in the table?

Because I didn't originally write this code, I would prefer to keep my
revisions to a minimum, unless there is a much better way of importing the
XML feed into our database that I can write fairly quickly.

Also, I was wondering if there is a utility to quickly generate XSD schemas
to use in the bulkload from the actual tables in SQL Server. I'd like to
modify our schemas to include the relationships and keys that I've set up
using the Management Console, but would much rather avoid doing it manually.
Please advise.

Thank you!
-Sabrina

Hi Sabrina,
Thank you for using Microsoft MSDN Managed Newsgroup.

From your description, I understand that you used SQLXMLBULKLOADLib in a third party software to import data from an XML source into
your database. However you do not want to first delete the data from the table and then bulk insert the data into to the table. You would
like to have it update existing records and only insert new records which do not exist in the table. Also you would like to modify your
schemas to include the relationships and keys in an easy way but not manually.
If I have misunderstood, please let me know.

Unfortunately what you were asking for are product limitations in SQLXMLBULKLOADLib. SQLXMLBULKLOADLib does not provide update
data function. If you want this function, you need to code to implement it by yourself. If there are large of data, checking for update may
also lead to performance issue. Generally the existing known methods or tools like bcp, XML Source (in SSIS), Data Import/Export etc,
they all do not provide such function. If you need to check each row for update, there might be not any performance advantage for BULK
operation. In my opinion, dumping the contents first is a reasonable design, especially from performance perspective. I recommend that
you review your requirements to see if you really want update in a bulk load operation. If this is required, you may consider rewriting your
application to implement this function.

SSIS XML Source can automatically generate a matched XSD schema for an XML source, however currently it is simple and will not include
the relationships and keys. You need to manually include the relationships and keys in your schema file. I am not sure if there are third-
party tools supporting this feature. You may contact your application vendor or some third party Dev forums for consulting this.

Appreciate your understanding on this. Please feel free to let me know if you have any other questions or concerns.


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg RemoveThis @microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx...tificat

Note: MSDN Managed Newsgroup support offering is for non-urgent issues where an initial
response from the community or a Microsoft Support Engineer within 2 business day is
acceptable. Please note that each follow up response may take approximately 2 business days as
the support professional working with you may need further investigation to reach the most
efficient resolution. The offering is not appropriate for situations that require urgent, real
-time or phone-based interactions. Issues of this nature are best handled working with a
dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================