General XML Data Question

[jasonp45]

I want to begin using standard XML tools to transform data. Basically I get data in several formats (Access, text, dbf, whatever) and have to verify and standardize it and put it into a Microsoft SQL database. Do I need to create XSLTs? XSDs? I don't know.

I'm looking for recommendations regarding software, what technologies I need to read up on, etc. Decent mapping software and the ability to do low-level data checking/ modification (like manually analyzing a date-string to check or standardize values) are important. I also want my transformations to be standards-compliant so that they will work with different platforms or data-consumers (different SQLs, Access, etc.).

Thanks for any advice.

 

[JontyMC]

This is a pretty vague question.

XML is a platform independent data structure. So putting all your putting into XML form would seem like a good start. The actual method of doing this would depend on the nature of the data you receive.

Once you have it in XML form. XSD's (schema's) can be used to check its validity. The actual XSD is a file that says what elements and attributes should go where in the XML and what they contain.

XSLT is a language used to transform XML. So you can create XSLT's to map the XML to anything you want.

Jon

 

[harebrain]

Converting the input file formats to XML so you can then import the files to SQL Server is an unnecessary (and error-prone) processing step. You would do better to look into DTS, which is SQL Server's import/transformation utility.

Use the right tool for the job.

 

[jasonp45]

Jonty: yes it is a vague question, hence the word "general" in my question's subject. Thanks for the info regarding XSDs and XSLT.

Harebrain: I use DTS all the time. It is not the right tool for the job I am trying to do. Its mapping capabilities are weak, and as I mentioned in my question I want my transformations to be cross-platform.

To anyone else reading this: I am looking for non-proprietary text-based transformations so that I am not tied to a particular platform (i.e., Microsoft SQL). I want any source file in whatever format to be described by some standards-compliant document, and one which allows me to then import the file into SQL Server (or any other XML-compatible database). Thanks.

 

[harebrain]

You might "use DTS all the time," but if you think its mapping capabilities are weak, you have yet to scratch the surface of what it can do.

Try this: import to SQL Server using DTS, then output using SELECT ... FOR XML. Best of both worlds--and easy.