(I had this post as a draft since January, it’s time to get over the blogging lazyness)
I remember when sql server 2k came out that the main ad campaigns were about it’s xml support. I remember that, at the time, I didn’t know what the heck xml was, and why all the buzz about it.
I corrected part of my ignorance right after, note, part of my ignorance…
Anyways, apart from retrieving resulsets in xml format, with who knows what schema, I hadn’t used xml in sql server much, and thought that *that* xml support was all a programmer gal could wish for.
Oh, I was wrong!
My last project involved a great deal of importing xml into sql server 2000 and it was indeed interesting.
I noticed a few things I would like to blog about so I could keep a reminder for future projects.
- sql server 2000 won’t import xml using a DTS package unless you have the “proper mapping” of your xml schema and your database schema. I was never able to tickle the proper mapping… My first idea was infering an xsd file out of the xml (I created that with a visual studio console app, loading the xml into a dataset and infering schema, but that didn’t seem to work as the proper mapping) See this article Importing XML into sql server 2000.
- what about Excel? you theoretically can import xml into Excel, associate an xsd file to your spreadsheet and have it all imported into sql server 2000. Voila! it should be just a couple of clicks. Don’t run that fast. Excel won’t import complex xml, this is an xml with a complex schema, where there are several child nodes with more child nodes (complex nested types indicating a one to many relationships). I even tried to separate the xml for importing one complex type or table at a time and realized I had to save all of my .xls files as csv (comma separated text) to have them imported properly.
Ok, now that we ended up writing a a dot net utility to use ADO.NET, now that we load the XML and synchronize the dataset with the table in sql server 2k, lets check if I can retrieve the xml out of the database to be able to work with smaller chunks of data in xml format.
- This part of the project was also fun indeed. The T-SQL clause for XML does not seem to work when some of the columns in your table are defined as text and will truncate the resulset over a maximun length.
- SQL server 2000 is unable to retrive the xml with the schema you would like. Apart from the data truncation, you should make deep xslt with the data afterwards to have it properly complying with the schema you want.
Hopefully sql server 2005 will answer some of our prayers. There is a xml data type, so worse case scenario, you can load the whole document into a column in a temporary table(I know this is not a good practice to have a huge document in a table column), apply XQuery to it and be able to retrieve the chunk of information you do want to import into your tables. DTS packages for importing xml should be better, old sql server 2000 relied on scripting language for everything with the known drawback that scripting languages are typeless.
Said all this, I might blog about sql server 2k5 in a next post (I really like the CTE Common table expressions), …if I get over this blogging lazyness.
That’s all folks!