Repost: XML import file and best practices questions. RRS feed

  • Question

  • Note: I posted this question in the "SQL Server XML" forum.  However, after a few days, many reads, and no replies, I think I might have put it in the wrong forum.


    We are creating a Window client application with VB.NET 2.0 using Visual Studio 2005.  I have a SQL Express 2005 instance running a single database with 2 tables that are related as master/detail (aka Parent/Child) using a one-to-many foreign key constraint.  Each table has an AutoNumber primary key and the child table has the FK constraint.  In the application designer, I used drag and drop to create the XSD schema, a typed dataset, and the required table adapters.  In a separate (off site) data entry application, we create an XML file based on the identical schema and dataset as the first application.  We use the dataset.writeXML method to create the XML file from the data entry application.  The goal is to allow the client application to update the SQL Express database with the newly created records from the off site data entry application.  Basically, the client will receive the updates on CD or email attachment every few months.


    I’m thinking that the client application should load the import XML file into a dataset, validate the data, and finally update the backend using the TableAdapter.Update(myDS).  However, when I tried this, I ran into numerous problems and a million questions:


    1)      Should the XML file that is used to import the new records contain inline schema information, a separate XSD file, or simply contain the data using nested elements?  Since the backend database assigns the primary keys, I’m not sure what advantage I would gain by having temporary primary keys in my XML file.  I’ve searched the forums and see many references to using seed -1, step -1 on the dataset and a +1, +1 on the database backend to avoid collisions.  In addition there are several references to using GUIDs as the solution.  What is considered “best practice” on updating a database using a dataset populated with a XML file from a separate application?


    2)      When loading the dataset from XML, should you use a different dataset, the same empty dataset, or the same fully populated dataset?  I want to be able to leave room to validate the data contained in the XML file to make sure that it is not creating duplicates as in the case were someone might try and load the same import file twice.


    3)      Do I need to do anything special to allow the database to handle the AutoNumber columns when updating using the TableAdapter.Update method?  I see some examples where the code loops through the dataset and marks each row as “newly added” to force the database to add the records as new.  But if the dataset is using negative numbers for primary keys and the database is using positive numbers then wouldn’t it treat all the records as ‘newly added’? 


    Sorry for the long post but I want to get this right and try not to kludge anything J



    Friday, October 19, 2007 4:26 PM

All replies

  •  Doug DeBug wrote:

    Note: I posted this question in the "SQL Server XML" forum.  However, after a few days, many reads, and no replies, I think I might have put it in the wrong forum.

    Note, you have the ability to delete your own posts.  Also, most moderators will move your posts to the correct forum, if they see fit.
    Friday, October 19, 2007 5:43 PM
  • Hi Phil,


    I did try and delete the other post prior to posting here but can't seem to figure out where or how.  I can edit the post but did not see a "delete" button.  I've seen where I can delete a new post in the first few minutes of creation but I can't seem to delete an older post.  I searched the forum and found where you can use the "report abuse" button to alert the moderator to delete (or move) the post.






    Sunday, October 21, 2007 2:39 AM
  • Ok, so no takers yet Smile


    I also wanted to add another to the list of questions regarding how the XML from the Dataset handles the Primary key and the foreign key in the child rows.  If the XML file uses nested elements to maintain the relationship between the parent rows and the child rows, then why does the MyDataSet.WriteXML produce BOTH the nested elements and the FK value in the child elements?  It seems redundant to have both nested elements along with the Primary key of the parent row.  What am I over looking here?  Is it because the dataset to XML conversion includes every field in each row of the dataset regardless of how it is related?  If that's true, I can't seem to figure out why I would chose to check off the "Nested Releation" check box when setting up the Foreign Key in the visual designer.  It's only an issue because several seperate applications are trying to pass the XML data from one application to another in a workflow.  I'm sure it can be done in many ways but was looking for a best practice type of answer for this and the other questions.  I'm open to reading a book on the subject if someone knows of a good 'n.




    Thursday, November 1, 2007 9:08 PM