(This post is soliciting personal experiences about storing XML; please share what you know. 
)I am working on a service application that communicates with an external service using XML. I am planning to use SQL Server 2008 to store the XML that is received and sent to the external service. I'm exploring my options for storing the XML in the database. The three options that I have identified are:[*]Store the XML in an XML data type column [*]Create tables to store the various parent and child relationships represented in the XML. [*]A hybrid of the two above approaches where the original XML is stored in an XML data type column but several fields from the XML broken out into their own columns to simplify querying and indexing. I'm looking for any advice, based on your personal experience, with storing and retrieving XML data in SQL Server. Some additional background: I've used an 'xsd.exe' equivalent called XsdObjectgenerator to create .net classes based on the XML schemas. When the service receives the XML file, it is deserialized into an instance of .net class. This instance is used to perform the operations of the service. My original plan was to then use option #1 above to store the XML. If I needed to update or report on the data, I would simply deserialize the db record back into one of my .net classes. Although this approach works and makes working with the xml very simple, I have concerns that as the volume of data increases, the performance of querying XML data type records will decrease. This is why I've explored options 2. & 3. above. In addition to storing the XML, the XML will be queried for use in both reports and a separate web application. The db records will be queried, sorted, filtered, grouped, summaried and possibly updated by the end users.
)I am working on a service application that communicates with an external service using XML. I am planning to use SQL Server 2008 to store the XML that is received and sent to the external service. I'm exploring my options for storing the XML in the database. The three options that I have identified are:[*]Store the XML in an XML data type column [*]Create tables to store the various parent and child relationships represented in the XML. [*]A hybrid of the two above approaches where the original XML is stored in an XML data type column but several fields from the XML broken out into their own columns to simplify querying and indexing. I'm looking for any advice, based on your personal experience, with storing and retrieving XML data in SQL Server. Some additional background: I've used an 'xsd.exe' equivalent called XsdObjectgenerator to create .net classes based on the XML schemas. When the service receives the XML file, it is deserialized into an instance of .net class. This instance is used to perform the operations of the service. My original plan was to then use option #1 above to store the XML. If I needed to update or report on the data, I would simply deserialize the db record back into one of my .net classes. Although this approach works and makes working with the xml very simple, I have concerns that as the volume of data increases, the performance of querying XML data type records will decrease. This is why I've explored options 2. & 3. above. In addition to storing the XML, the XML will be queried for use in both reports and a separate web application. The db records will be queried, sorted, filtered, grouped, summaried and possibly updated by the end users.