April 27, 2011 at 1:38 pm
Hi Guys,
Anyone has experience with FIXML data? One of the vendors sent us a fixml file that we need to stage into SQL 2008 to take a look... How can we stage that? Should I use SSIS?
Thanks!
April 27, 2011 at 2:48 pm
Do you have an XSD to go with the XML?
Do you have tables in SQL Server created to import the data into?
The Bulk XML Loader tool included in SQLXML will get the job done fast.http://msdn.microsoft.com/en-us/library/ms171806.aspx
If you want to interact with the data before it is loaded into the database you can use an XML Source in an SSIS Data Flow.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 10:43 pm
I do not have XSD or table created.. this is an ad-hoc file and we do not have schema for it... what should be the fastest way to load it into a table? Should I created XSD using SSIS for that file? Do I have change the .fixml into .xml? Thanks!
April 27, 2011 at 11:05 pm
Ghanta (4/27/2011)
I do not have XSD or table created.. this is an ad-hoc file and we do not have schema for it... what should be the fastest way to load it into a table? Should I created XSD using SSIS for that file? Do I have change the .fixml into .xml? Thanks!
Wow, no XSD and no table definitions. Is the structure of the XML complex?
You have three things you need to deal with:
1) determining the structure of the XML
2) modeling that structure as a set of database tables
3) importing the data
If you create an XSD with SQL Server annotations then SQLXML Bulk Loader can create the tables in the database in the same step while importing the data. That would take care of all three items above in one step. Did you look into using SQLXML from the link I sent?
How big is the file?
Another option is to pull the file into a SQL Server variable using OPENROWSET w/ BULK and then shredding it using XQuery in T-SQL. You would still need to create destination tables but this would skip the XSD since the schema would be modeled in you XQuery code.
This seems like a lot of work for processing an ad hoc file however...will you be receiving this file format again in the future or is this all one-time code?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 28, 2011 at 5:33 am
Thanks a lot OPC. So is there a way I can query the data and create the destination table for it? And then use your 2nd approach? Data is around 5 gb file and I think it has around 30 fields...
April 28, 2011 at 7:32 am
Ghanta (4/28/2011)
Thanks a lot OPC. So is there a way I can query the data and create the destination table for it? And then use your 2nd approach? Data is around 5 gb file and I think it has around 30 fields...
In order to query XML data you have to understand it's structure. Step 1 from above...there is no avoiding that unless you simply want to load the entire file into a single XML field...which in that case you would not be happy because your file is 5GB (GB, the file is 5 Gigabytes right?) and XML fields can only be 2GB...but I digress.
You need to determine the structure of your XML first. Given the size of the file I would recommend breaking the file up into many much smaller files, like files with a few hundred MBs each tops, so they're more manageable, but that may require you to manually create some end tags and start tags to ensure each file still contains a valid XML document which may not be feasible.
Either way, given the amount of data I think you should look into using the SQL XML Bulk Loader. I think loading 5GB of data using OPENROWSET, even in small chunks, is going to be very taxing on the system and take a lot of time due to how the XML is processed. One caveat, SQL XML Bulk Loader does require deterministic XML and a proper XSD because it reads ahead in the file...which is why it is so much faster and does not need to load the entire file into memory. If your XML is not deterministic then back to OPENROWSET.
Can you provide a sample of the XML? Is it just a flat data structure modeled as XML or is it truly hierarchical with multiple levels, i.e. complex requiring multiple related DB tables to contain it?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 28, 2011 at 7:49 am
OPC thanks for a nice write-up.. this write-up inspires me to learn more about handling XML which I will do. I think I need to flatten the xml file first and then load it. I have found a program that will flatten it and then I will use SSIS (XML source) to create XSD and use merge join to load into table... You are rite I might have to break it into smaller chunks... Thanks a lot for your help.
April 28, 2011 at 8:01 am
You're welcome. I am happy you have a path towards a solution 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2018 at 5:40 am
Using code definitely works for converting simple XML. However, this manual approach can be quite cumbersome when dealing with complex XML. I am talking about industry data standards such as HL7, FpML, ACORD, FIXML, NDC etc. etc. The specs are typically a few thousand pages long. If you have large volumes of XMLs and a streaming requirement things don't get easier. The same is true for frequently changing XML files or where you don't have an XSD. We have written up a blog post that looks into all these items and automatically converts complex FIXML to MS SQL Server https://sonra.io/2018/06/05/converting-fixml-to-sql-server/
June 7, 2018 at 7:25 am
kristijan.berta - Thursday, June 7, 2018 5:40 AMUsing code definitely works for converting simple XML. However, this manual approach can be quite cumbersome when dealing with complex XML. I am talking about industry data standards such as HL7, FpML, ACORD, FIXML, NDC etc. etc. The specs are typically a few thousand pages long. If you have large volumes of XMLs and a streaming requirement things don't get easier. The same is true for frequently changing XML files or where you don't have an XSD. We have written up a blog post that looks into all these items and automatically converts complex FIXML to MS SQL Server https://sonra.io/2018/06/05/converting-fixml-to-sql-server/
Somehow I doubt that the OP is still waiting for an answer on this one😀
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply