XML Data Type - updated for 2005
Sorry about the rush today. I know I didn't manage my time well. No excuses (except maybe the cold medicine).
Anyways - Here is the sample code I went over, you will find a few additional samples I didn't have time to go over.
www.neudesic.com/uploads/steve_muise/08_XMLType.zip
Also here are a few links to some additional, well-written articles on the new XML type
http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/Default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp
http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/Default.aspx?pull=/library/en-us/dnsql90/html/sql2k5xml.asp
To store XML in Unicode in SQL Server use the UTF-16 Encoding.
I mentioned at the end, but it is worth repeating; in order to modify an existing Schema Collection that has been created in the database and bound to a column in a table you have to:
1. Remove the table binding (ALTER Table ...)
2. Remove the Schema (DROP Schema ...)
3. Recreate Schema with new Schema (CREATE Schema ...)
4. If necessary update XML in the table
5. Rebind Schema to table (ALTER Table ...) - be prepared to wait, it needs to re-validate all XML files in the table
For the Indexing capabilities, you can also do a FULLTEXT index over the XML column to optimize text searches
The above articles cover the XML Methods fairly well (that is the modify, query, value, etc) that I covered pretty quickly
The OPENROWSET is pretty straight forward, I know I glossed over - but take a look at the sample code and see if it doesn't just make sense. Email me if you have any questions
Again sorry about the poor time management, I will try and improve for next time.
Steve