Getting XML into MS SQL Server database

**haggul** · 18-01-11

I want to move from CSV to XML feeds as I am sure they will be more reliable, and also get me over some of the issues I am having with bad formatting of files etc.

I want to get the XML data into an MS SQL Server database.

I have some SSIS jobs setup for the CSV files which do the downloading / unzipping etc as required. However I can't get the SSIS packages to do anything with XML files from Webgains or Aff Win. It complains of bad characters in Webgains case, and of DTD errors in Aff Wins case.

Does that mean the XML files aren't up to scratch, or is it likely SQL Server / SSIS is being too fussy? Can you get XSD files for these to help also?

Anyone have any experience with this sort of thing? I was thinking of abandoning SQL Servers built in XML import, and coding something in vb.net but thought I'd check here before reinventing the wheel.

Cheers.

**D-Mac** · 18-01-11

If the same data provider is providing both csv and xml, then the data is almost certain to be exactly the same in both files, so you might as well stick with the csv as the files are smaller and save yourself the hassle of coding something new :-)

**haggul** · 19-01-11

Issue is that SSIS does not respect the text delimiters - once it finds a comma in the data, even between the quotes it breaks down and drops those rows at best. I have scripted an import but it takes so long sucking the file in row by row.

I was hoping the XML would be a little more resilient as the tags are essentially the delimiters and so the data in between can be "dirty" without failing the whole thing.

**jonsp** · 19-01-11

Originally Posted by haggul

Issue is that SSIS does not respect the text delimiters - once it finds a comma in the data, even between the quotes it breaks down and drops those rows at best. I have scripted an import but it takes so long sucking the file in row by row.

In that case can you get a pipe (|) delimited csv? This should eliminate the problems with commas

Cheers,
Jon

**lowndsy** · 21-01-11

Maybe use some software like this?

Altova DatabaseSpy Database Tool

This one is paid, but they have a free trial and it does look good.
SqlYog Community Edition is a good free package for MySQl and there's probably a free package for SQL as well.

**devTastic** · 06-02-11

I believe in SSIS you can call out to .NET code, so you can write a .NEt code snippet that does the XML parsing and integrate it into your dataflow can you not?