I have a dataset with three tables. I would like to use XML bulk loading to load the data from the datatables in the dataset into three database tables.
1. Is there a way to validate the datatables(in a dataset) against the schema of each database tables and selectively remove the rows that are invalid Say 13th row in datatable 1 does not have a required value, I would like to remove row 13 and proceed loading the other rows.
2. Load data using XML bulk load directly from dataset to database without creating a XML file. I guess OpenXML will work but is there any limitation on the amount of data transfered
After browsing over the documentation of SQLXML 3.0 ,I found some relevent information reg question 1.
a.There is a property called CheckConstraints. Can this property be used to check for required fields It is recommended that this property is set to False. How much does this reduce the performance.Is it drastically or significantly or little.
b.CheckConstraints stops processing the rows if it fails in a particular row.Is there a way to bypass the failed row and bulk insert the other rows.
Thank you.
Validate and Bulk Load Datatables in Dataset to SQL Server 2000 tables.
Hot Topic
- future enhancement
- Grant CREATE VIEW, CREATE PROCEDURE ...
- Can't uninstall or reinstall nonfunctioning sql server
- SQL Reporting Services Subscription Does Not Work
- Analysis Services Migration Wizard
- Using SMO to backup / restore a SqlExpress database
- Database mail not sending
- Package Runs but as soon as it is scheduled in SQL Server it hangs
- data driven subscription does not work
- Export to Acrobat
SQL Server
- How to dynamically change width?
- SQL Reporting Services Subscription Does Not Work
- FormatCurrency in 2k5 Report Services
- Management Studio for SQL Server 2005 Standard
- Migration from Cognos ...
- job failing
- Cognos and SQL Server 2005 Reporting Services
- High Performance in Mirror
- Row Size Limitation of Report Model?
- Select Distinct
Validate and Bulk Load Datatables in Dataset to SQL Server 2000 tables.
WoodyJ
On your 1st question: there are multiple solutions to this problem, each with it's own pros and cons. One solution is loading the XML into a temp table, then cleaning out any rows you don't want. Using DTS is another solution. Lastly, SqlXml can help by loading XML to the database (it has a xml bulkload component specialized in this) - by tweaking the annotated XSD schema you can get some limited transformations (see limit values).
On your 2a question: as a high-level rule of thumb, bulk-inserting data into database without having to check constraints for each row should speed things up. That beeing said, the perf depends on a lot of thingsthings (size of data, db rountrips, structure of XML, no of tables, etc), so I would try doing some trial measurements on representative hardware with representative xml data. CheckConstratins is related with checking sql constraits while bulkloading data to tables.
For 2b: if anything fails, the current batch/transaction rolls back. The biggest problem here is finding out what other batch rows were not inserted, since the batch size can be more than 1 row.
If your project allows, check out the new Sql Integration Services in Yukon. Among other things, it has built in support for 'error' rows - you can either change them so they can be properly inserted in the sql-server database, or filter them out entirely.
HTH,
Daniel D.C.
ericsdavis
Also there is no way to bypass a failed row.
Removing values from a dataset needs to be done using the dataset mechanism, I don't think SQLXML is usable for that purpose.
Now if you have your data in a dataset, why aren't you using the DataSet mechanism such as Diffgrams to update the relational tables
Best regards
Michael