Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating schema and population from XML Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 7:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 12:53 AM
Points: 65, Visits: 197
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.
Post #1562662
Posted Thursday, April 17, 2014 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:24 PM
Points: 7,139, Visits: 15,191
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?
Post #1562748
Posted Wednesday, May 21, 2014 8:39 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
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.
Post #1573399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse