.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Import Complex XML into flat table

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :ASP.Net

I have an XML feed supplied which I need to get into a SQL Server table. I have tried SSIS but because it has DTD in the file that does not work.

The format is as follows :

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE merchantProductFeed SYSTEM "http://www.xxxxx.com/DTD/datafeed.dtd">
   <merchant id="123" name="MerchantName">
    <prod id="123456" in_stock="no" pre_order="no" web_offer="no" hotPick="no" adult="no">
     <text lang="EN">
      <name>Product Name</name> 
      <desc>Product description can be quite long</desc> 
      <spec>Product Specs</spec> 
     <uri lang="EN">
     <price curr="GBP">

View Complete Post

More Related Resource Links

I need to import XML from Cognos to a flat table in SQL Server 2005


How do I query data in this form?

<?xml version="1.0" encoding="utf-8" ?> 
 <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
  xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd"

 <item name="NAME_LAST" type="xs:string" length="62" /> 
 <item name="NAME_FIRST" type="xs:string" length="62" /> 
 <item name="NAME_MIDDLE" type="xs:string" length="32" /> 
 <item name="GUID" type="xs:string" length="20" /> 
 <item name="NETID" type="xs:string" length="18" /> 
 <item name="WORK_EMAIL" type="xs:string" length="62" /> 
 <item name="HOME_ADDRESS_STREET_1" type="xs:string" length="50" /> 
 <item name="HOME_ADDRESS_STREET_2" type="xs:string" length="50" /> 
 <item name="HOME_ADDRESS_CITY" type="xs:string" length="32" /> 

Import XML File to SQL Table

Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

Super Complex Table/Matrix



I'm in a situation where I have to build a super complex table or rather matrix

This is how the table/matrix should look like: 




The complex thing is that everything is taken from a database.


I will try to explain the color sections one by one.


SERIE (Green area)

If we start with the top columns; SERIE 1, SERIE 2, SERIE 3

These values are coming from a table column value. If this column has the value 3 then I will need to print 3 table cells, just like in the image.




Then there is the outer left columns, that goes like this;

Behandlingsdag (meaning day of treatment)

Osplatin (100 mg/m2)

Leucovorin (x)

And so forth.


All these values are also coming from 3 different tables



One Column (Yellow area)

These cells are getting their values from a table. According to some true og false values.



1, 2, 3, 4 (Red area)

Then there is the table cells below the SERIES. These table cells goes like this

First there is a 1, then there are the tables cells 1 to 18 all below

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

Import DataTable into SQL Table

I'm trying to parse a text file into a sql table.  I have the code to parse the file and display it in a datatable but for the life of me I can't figure out how to import it into a sql table.  All my previous experience has been with csv files and with schema.ini files.  This is new to me.  This is what the code to read the text file and display it in a datagridview: Dim dtTest As DataTable Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load dtTest = New DataTable DataGridView1.DataSource = dtTest.DefaultView End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim filename As String = "C:\Price.txt" Dim lstValues As New List(Of String) Try Using sr As StreamReader = New StreamReader(filename) While Not sr.EndOfStream Dim splits As String() splits = sr.ReadLine.Split(","c) dtTest.Columns.Add(splits(1), GetType(String)) lstValues.Add(splits(2)) End While End Using Catch ex As Exception End Try dtTest.Rows.Add(lstValues.ToArray) End Sub This is what the data in the text file looks like: DD/MM/YYYY HH:MM:SS,LAU4AB,245.35 DD/MM/YYYY HH:MM:SS,ROC06,123.45 DD/MM/YYYY HH:MM:SS,GRE033,87.5 The second item "LAU4AB is the column header for the first

Import flat file from the report to filtered data



Is there a way to use a flat file which could be imported from the report in order to filtered the data displayed ?



Import Access table or Excel spreadsheet into Oracle table


I am trying to import from an Excel spreadsheet or an Access database into an Oracle table.  I used the SSIS Import and Export wizard to create the SSIS packages, but everytime that I attempt to run the package, SSIS stops once it gets to the Pre-Execute phase.  There are never any errors.  Am I doing something that cannot be done?

I'm using the following:  SSIS 2005, Microsoft Oracle OLEDB provider (MSDAORA.1), Excel and Access 2003

By the way, I am able to successfully export from the Oracle database to either Excel or Access using SSIS.


Import table data using webservice


Hi all,  I'm starting using web services in .net , I'd like to know how to achieve this:

I need to import table data into a database, currently what we do is uploading via ftp the text files, and then running a process in php in the webserver.

Could this be automated  by sending those text files (or xml, or whatever format is necessary) to a webservice as an argument, and that the webservice handles the insert and validating process of the data?

Also I would need it to be secure, ideally I should send it login validation.

Anybody can help me with this?

Thanks a lot!

Easiest way to Import Multiple Spreadsheets into the same table



I am trying to found out the most simple way to import multiple spreadsheets (myspreadsheet*.xls) into the same table. I found this process relatively straight forward with importing multiple text files (myTextFile*.txt) into the same table.

Any help would be greatly appreciated.

Thanks in advance,


If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

How to Create multiple flat file import package?

Hello Everyone,

I am an absolutely beginner to SSIS and need to perform the following task.

  • Need to import Master data from (semi colon) ; delaminated flat files into separate tables in SQL Server
  • Each file will create a separate table in SQL Server
  • While importing I need to perform validation on each file and only extract data that is valid e.g. CNIC column must be numeric and 13 digits long.
  • Any data that is not valid will be sent to a separate table with same TableName_bad suffix
  • After the data is extracted I will extract the details data of only those records that have been extracted in master table before

I know about basic data flow controls etc but I don’t know which control will be better for which task. Please tell me what will be the procedure to fulfill my requirements. I will be extremely thankful.



Syed Afraz Ali


SQL 2005 Import of Flat File always truncates data longer than 50 chars

My source data is a flat file, semicolon-delimited, and has three columns.  The first is 10 chars, the second can be up to 240, and the third can be up to 240.  For the import I select "Flat File Source" for the Data Source.  When I select my source file, I use the Flat File Manager, Advanced tab, to configure it as follows:

I leave the defaults for Column 0.
For Column 1 I select a DataType of Unicode String and set the OutputColumnWidth to 240.
For Column 2 I select a DataType of Unicode String and set the OutputColumnWidth to 240.

On the Destination page I choose "SQL Native Client", and point it at my database.  The destination table has 3 columns.  Column 0 is CHAR(1), Column 1 is NVARCHAR(240), and Column 2 is NVARCHAR(240).

However, when I get to the "Select Source Tables and Views" page of the wizard and hit the "Edit" key under "Mapping", it shows a size for all three columns of "50".  Sure enough, when I perform the import, it truncates everything to 50 characters.

How do I get more than 50 characters to import?

how do i import a text file into a table?


Hi all, need some help here as am not the SQL guru or anything close. I need to write a stored procedure which will import data from a log file into a table in a Database. Right now, i have a log file that looks just like this and i wan to be able to get the data into the appropriate fields in the a table (lets call it tbl_log). I have a service that is creating the log file and i want to be able to import the data into a table, so i need to be able to set the different directories dynamically since there will be different logs in different directories.



C:\TEMP\watch_directory\1\New Folder Created    11/9/2010 5:55:16 PM

C:\TEMP\watch_directory\1\New Folder Renamed to 1\FootBall    11/9/2010 5:55:22 PM

C:\TEMP\watch_directory\1\FootBall\New Folder Created    11/9/2010 5:55:32 PM

C:\TEMP\watch_directory\1\FootBall\New Folder Renamed to 1\FootBall\Dhino    11/9/2010 5:55:35 PM



Please help.

Import Wizard lists table out of order when connected to SQL Azure


When you use the import wizard to copy data from SQL Azure, the list of table on the Azure server is out of order.  This makes it very time consuming to find a particular set of tables. 

Is this something that I could get some feedback on, or perhaps submit as an issue, either here or another forum?



I cannot add a dynamic table inside a Wiki or import a template.


Hello, I Have created a Wiki inside a Sharepoint 2007 site and have created five pages inside this Wiki.   Now I am trying to use a template but there is nowhere that I can import a template. Also, when I attempt to create a dynamic table on one of my Wiki pages, the dialog box which comes up only asks me for the number of columns and rows.  The 'SharePoint How To' book that I am using as an instruction guide is telling me that a dialog box should come up with several parameters, including number of columns, rows, height, depth and formatting - I'm not getting this dialog box.

So, I'm wondering if someone can tell me why:

(a) I can't get a dynamic table into the site where users can add rows and columns.

(b) I can't import a template into the Wiki so we can use a solution that works.

(c) My functionality is restricted. Do I need to be using SharePoint Designer to get this level of functionality?

I'd really appreciate any references which would help me solve this problem.

Thanks for your help.

SharePoint Sally

Embedded quotes in CSV file - Flat file import

I have embedded double quotes in one of columns of my CSV file. E.g (see 2nd line) "Sateesh","Maduri",1,M "S""Folk", "M",1,M And as we dont have direct configuration properties for this type of flat files while importing into sql server, I have used the example given in http://www.ideaexcursion.com/2008/11/12/handling-embedded-text-qualifiers/ , but I am getting the error "value of type string cannot be converted to Microsoft.SqlServer.Dts.Pipeline.BlobColumn'. I have one column as Text column which have embedded double quotes in it. Please help me as soon as possible.
Sateesh Maduri

Import xml data to Table Using Stored procedure



I have xml structure and Table structure as same. I need to import the xml data to Table using stored procedure.

Can you provide sample SP for this?


Thanks in Advance

Import xls file into sql server database table in asp.net


I have one xls file which has 1.98 lacs rows. Whenever I try to import it in sql database table then I am getting following error to open OLE db connection.

External table is not in the expected format.

My code:

string con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("TowerWorkOrders20110316.xls") + ";Extended Properties=\"Excel 12.0\"";       

 //string con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("TowerWorkOrders20110317.xls") + ";Extended Properties=Excel 8.0";        

OleDbConnection connection = new 

OleDbDataReader reader;        
 OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);        
reader = command.ExecuteReader();.................

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend