I'm looking for a script or compiled (vb.net only) with source code that will process and XML file and write the records to a SQL 2008 database.
I have already created the tables (1) and have the connection info ready. Your code will open the xml file (sample attached) and based on the parent node in XML, will write the values to the columns in the database. There is one table. The XML report will have other values but I only care about what is in between <items></items> The rest of the data can be dropped.
I may run this script daily and the report may have same data so your script/exe will need do a lookup and find if the record already exists in the database. You can do this by a lookup on certain fields Example for Items Ordered it would be ASIN, Tag, and Date.
This is pretty straight forward and simple for someone that knows how to parse XML files and write to a database.
/****** Object: Table [dbo].[tbl_AmazonEarnings] Script Date: 11/08/2012 17:22:37 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[tbl_AmazonEarnings](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ASIN] [nchar](10) NOT NULL,
[Binding] [nvarchar](50) NULL,
[Category] [int] NULL,
[OrderDate] [date] NULL,
[Edate] [int] NULL,
[Earnings] [decimal](19, 2) NULL,
[LinkType] [nvarchar](5) NULL,
[Price] [decimal](19, 2) NULL,
[Qty] [int] NULL,
[Rate] [decimal](19, 4) NULL,
[Revenue] [decimal](19, 4) NULL,
[Seller] [nvarchar](50) NULL,
[SellerTag] [nvarchar](50) NULL,
[Title] [nvarchar](max) NULL
) ON [PRIMARY]
For clarification, I want a ASPX (vb.net) file to upload this XML to a SQL database connected to same server. I should be able modify the ASPX VB file and put in my own DSN connection string and XML upload button will upload. Thanks.