SQL Server 2005 "XML datatype" to SQL Table

[gagan8877]

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

[boen_robot]

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...

[gagan8877]

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

[aspnetguy]

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.