I have a client application that generate a database into my SQL Server. The generated database contains a BLOB data (image data type) of an XML file. My question is how best would I be able to use and read the data inside the XML file (for generating a web report). Would it be possible to a good idea to export the BLOB data into an XML file on the disk and import the XML back into SQL as table database. If yes, how would I go about doing this? Would I be using BCP to export the BLOB out into XML file? Also is it possible to import the XML back without predetermining / pre-creating the columns in the table? Thanks!!
Unless, there is a need to do indexing / filtering / other analysis on various columns, there is no need to split the xml into various columns.
SQL Server 2008 R2 has xml support and a single table with two column ID and XMLData should work for straight forward storage and retrieval, for example:
CREATE TABLE T1(Col1 int primary key, Col2 xml)
INSERT INTO T values(1,'<ProductDescription ProductID="1">iPhone</ProductDescription>')
Refer to the msdn article "Implementing XML in SQL Server" for syntax and other help.