SQL Query For Column Information

I’ve been working on a utility that takes in XML documents and dumps them into a database table. This is a generic solution that will be reused with many different types of XML documents, so I looked into ways to make the utility smarter, to make adding new XML document types easier.

One thought I had was with casting. All the values start out as strings (since they’re from XML), but upon inserting into the database, they need to be converted to the correct data types. Rather than putting this information in the code, what if the code was able to ask the database what data type a column is, and then cast it to that type automatically?

After some digging and experimentation, I came up with this query:

Which will give you something like this:

Still not sure I’ll use this. Might be a little too much magic. But I thought it was a pretty cool trick worth sharing.

Adapted from this blog post with help from this documentation.

0 comments » Related topics: