gagan8877 Posted September 10, 2006 Share Posted September 10, 2006 Hi AllI am new to XML. The source application stores data in SQL Server 2005 in XML datatype column. We have to extract and load the XML data into relational tables for faster querying and reporting for a data warehouse. We do have an XSD file from the application developer. Now our tasks are:1. Create tables (data model) in SQL Server 2005 to load extracted XML data.2. Map XML data to various columns in these newly created tables.3. Extract XML data from the XML column.4. Load data to newly created tables for further transformation.Now the questions are:1. Is there a tool that can convert XSD to SQL DDL? Doing it manually will be very time consuming. (FYI: We are exploring Altova XMLSpy - which is a potential candidate)2. How can I map data from XML to relational table columns? Is there a tool that can help me there? Does XSLT file help in achieving this goal? What is the best approach?3. What is the best way to extracrt XML data and load it to relational SQL tables? Suggestions will be appreciated.ThanksGary Link to comment Share on other sites More sharing options...
boen_robot Posted September 10, 2006 Share Posted September 10, 2006 I can't help you with the first question, mostly because I have never even head of this "SQL DDL" thing.For the second one, I can tell you for sure that XSLT is NOT the solution. XSLT can convert XML to another XML based document or plain text, but not any other formats.The only sure way I know to map XML to SQL data fields would be to use a server side script, but writing it would be such a big pain... Link to comment Share on other sites More sharing options...
gagan8877 Posted September 10, 2006 Author Share Posted September 10, 2006 Thanks for the quick reply Boen. SQL DDL means Data Definition Language Script or the CREATE TABLE command that can create a SQL Table from an XSD file. ThanksGary Link to comment Share on other sites More sharing options...
aspnetguy Posted September 11, 2006 Share Posted September 11, 2006 can you post the XSD code? What language are you planning to use for this or are you hoping to just use SQL at the command line?ASP.Net can do some cool stuff with XSD and Databases. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now