April 17, 2014 at 7:46 am
I would like some help with XML processing in SQL Server and I hope someone can help me.
I have a complex XML file with many layers. I need to generate a SQL set of tables and populate them with the data in the XML. If that is possible, I would like to go a step further. I may only need a subset of tables and even attributes. Is there a way I can define which elements, attributes and parameters I should care to import?
Thanks for any insight.
I forgot to say that I'm using SQL Server 2012 and 2014.
April 17, 2014 at 10:10 am
Both requests are possible to some degree. The best tool to do so might be more of a factor of how repeatable you intend the process to be. As in - if the structure is going to be fairly consistent and the discovery of the structure is to be done once for many subsequent XML files then I would tend to pursue one path; if on the other hand the XML files are like thte wild west and sturcture changes each and every single time, you're talking a different solution.
In general your best bet would be to get the XML schema (i.e. the definition of the structures being sent in the XML) from the person sending you the XML files. A good schema can tell you a lot about the content you're getting, and would help with not "guessing" wrong about what the structure SHOULD be. There are also a lot of XML tools that can reverse-engineer a database schema to accomodate the incoming XML content, which would at least give you a reasonable DB structure to start from and adjust as needed. You can then use XSLT tools to transform and extract that content into the DB structure.
Assuming the process were repeatable, I would actually perform your first task manually (i.e. create the strcuture, then adjust as needed to fit the data, but also to shape the DB to include only what I need and to follow good SQL standards), then create the XSLT transformation process after step 1 is complete to my satisfaction.
If the files are not consistent and I have to automate everything end to end and still somehow guess what it is I want and throw out the rest, then the process gets to be a lot trickier. I've been asked for a few of those "superman" processes before, and most of them tend to fail dramatically because of the lack of structure.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2014 at 8:39 pm
You could use SSIS to guess/generate an XSD for you. Create a data flow task with an xml source. If you have the XSD file then even better. Dump the results into a staging table, created with all varchar columns is a safe way to go. You could do a cursory inspection of what you think you might need from here.
----------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply