| Author |
Topic |
|
HenryFulmer
Posting Yak Master
110 Posts |
 Posted - 2011-04-13 : 13:53:56
|
| I am writing a process to import master data from a legacy system to my SQL Server 2008 database. The output option of the legacy system that I have are XML or tab delimited flat file.Are there any arguments for one over the other? So far I've mostly been working with flat files but would be open to going a different routes if there's benefits to it.I'd appreciate your comments. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-13 : 14:16:13
|
| Unless there's a pressing need for XML, I'd recommend going with tab-delimited. It's a lot simpler, a lot smaller, and it's much easier to import into SQL Server (lots of options compared to XML).The only reason I would consider XML is if the data was deeply hierarchical and not relational in overall structure, i.e. object stores vs. relational tables, and it would be complicated to piece together multiple tab-delimited files. If the legacy system is relational, like Oracle, DB2, SQL Server then tab-delimited will be better. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-13 : 14:20:06
|
| I would prefer flat file, mainly because it is easier to import into database tables. With XML files you have to shred the XML data and process it. If it is a one-time conversion of data from one known system to another known system, I can't really see any advantages to using XML files.I am of course, assuming that eventually you want to store the data in relational tables and not as an XML column. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-13 : 14:21:00
|
 But, at least you have concurring opinions from an expert and a novice. SO the opinion has to be right! |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-04-13 : 15:43:08
|
| i vote for xml because I have worked with tab and comma delimited for many years but enjoy xml now. with flat files do you not need to create format files etc? not with xml. if it is a one time thing i guess it does not matter but you can use this time to see for future if xml is worth it?If you don't have the passion to help people, you have no passion |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-13 : 16:00:20
|
| But eventually, don't you need to shred that thingie yosiasz? Not a fun thing to do, at least for me. All those path expressions and FLWOR queries give me the chills!! |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-04-13 : 16:10:18
|
| yes you do need to shred it somehow depends on complexity of xml structure, but it is a lot of fun! I learned FLWOR from you, actually very cool sunita!If you don't have the passion to help people, you have no passion |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-04-13 : 17:34:12
|
quote:
Originally posted by yosiasz
i vote for xml because I have worked with tab and comma delimited for many years but enjoy xml now. with flat files do you not need to create format files etc? not with xml. if it is a one time thing i guess it does not matter but you can use this time to see for future if xml is worth it?If you don't have the passion to help people, you have no passion
Delimited files do not require format files.All you usually have to do is create a table with the correct number of columns and correct datatypes, and then create a bulk insert or bcp command with the correct delimiter and end of line identifiers.The only place where you might have a problem with tab delimited files is when the data itself contains tab characters. That would cause bcp or bulk insert to see the row as an error, because there would be too many columns. If you have the option to specify a more complex, multi-character delimiter that is unlikely to appear in the data, like ~|~ , that will usually solve the problem.CODO ERGO SUM |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-13 : 19:48:02
|
quote:
Originally posted by yosiasz
yes you do need to shred it somehow depends on complexity of xml structure, but it is a lot of fun! I learned FLWOR from you, actually very cool sunita!If you don't have the passion to help people, you have no passion
OMG, so kind of you to say that, yosiasz! You made me smile!!Depending on your skill levels, you might like the tutorials on xquery (and other topics on XML) here: http://www.academictutorials.com/xquery/ Their tutorials are not very in-depth, but what they do cover, they cover very well; very readable. It is not specific to xquery in SQL, but still useful. |
|
|
|
|
tab delimited file vs. XML file