Lauren_New_Jersey
New Member
I'm attempting to create a small console app in C# to perform inserts on a table of Products (ITEMS) in SQL Server 2008 according to the contents of an XML file in the FASTEST way possible. I already have an .XSD file that contains the proper mappings to the SQL table (which may not be necessary with the approach outlined below). Here's a high-level of my approach:[*]Read the XML, using it to create a table.[*]Perform a MERGE against the ITEMS table using the table created from the XML file.
2a. If the item exists, update it.
2b. If the item does not exist, insert it. [*]Create a log of only the records inserted in XML. Consider the following ITEMS table and XML file: ITEMS\[code\] Item_Id Name Price 1 Coke 5.00 2 Pepsi 3.00 3 Sprite 2.00 \[/code\]ITEMS.XML \[code\] <?xml version="1.0" encoding="ISO-8859-1"?> <Item> <Id>5</Id> <Name>Mountain Dew</Name> <Price>4.50</Price> </Item> <Item> <Id>3</Id> <Name>Sprite Zero</Name> <Price>1.75</Price> </Item>\[/code\]After the import, the ITEMS table should look like: ITEMS\[code\] Item_Id Name Price 1 Coke 5.00 2 Pepsi 3.00 3 Sprite Zero 1.75 5 Mountain Dew 4.50\[/code\]Once that's done, I also need to generate an XML formatted log file that contains the "new" record that was inserted into the table (ITEMS_LOG.XML): ITEMS_LOG.XML\[code\] <?xml version="1.0" encoding="ISO-8859-1"?> <Item> <Id>5</Id> <Name>Mountain Dew</Name> <Price>4.50</Price> </Item>\[/code\]I have tried implementing this using SQLXMLBulkLoad, but unfortunately it does not provide the logging that I need, nor does it permit me to access any of the messages returned from SQL Server (i.e. what's been inserted/updated). Although I have an intermediate level of SQL expertise, I am fairly new to working with XML, especially in this context. Any help/guidance would be greatly appreciated!
2a. If the item exists, update it.
2b. If the item does not exist, insert it. [*]Create a log of only the records inserted in XML. Consider the following ITEMS table and XML file: ITEMS\[code\] Item_Id Name Price 1 Coke 5.00 2 Pepsi 3.00 3 Sprite 2.00 \[/code\]ITEMS.XML \[code\] <?xml version="1.0" encoding="ISO-8859-1"?> <Item> <Id>5</Id> <Name>Mountain Dew</Name> <Price>4.50</Price> </Item> <Item> <Id>3</Id> <Name>Sprite Zero</Name> <Price>1.75</Price> </Item>\[/code\]After the import, the ITEMS table should look like: ITEMS\[code\] Item_Id Name Price 1 Coke 5.00 2 Pepsi 3.00 3 Sprite Zero 1.75 5 Mountain Dew 4.50\[/code\]Once that's done, I also need to generate an XML formatted log file that contains the "new" record that was inserted into the table (ITEMS_LOG.XML): ITEMS_LOG.XML\[code\] <?xml version="1.0" encoding="ISO-8859-1"?> <Item> <Id>5</Id> <Name>Mountain Dew</Name> <Price>4.50</Price> </Item>\[/code\]I have tried implementing this using SQLXMLBulkLoad, but unfortunately it does not provide the logging that I need, nor does it permit me to access any of the messages returned from SQL Server (i.e. what's been inserted/updated). Although I have an intermediate level of SQL expertise, I am fairly new to working with XML, especially in this context. Any help/guidance would be greatly appreciated!