Using CONTENT keyword while creating a table with an XML column from XML Schema Collection

Question

While creating a table that has an XML type column, I am referring to a complex XML Schema Collection. When I specify the XML Schema, I have the option of mentioning either CONTENT or DOCUMENT keyword. The latter will ensure that the XML data is stored as a document in a single column.

According to a video tutorial the CONTENT will store the XML data in fragments.

Besides the above statement I don't find reference anywhere else regarding the usage of the CONTENT keyword and it's implication on schema & data.

I would like to know how the fragments are created and managed and whether and how they can be queried individually. Further, how the fragments are correlated. Next, when I amend the XML Schema Collection, what is the impact.

Answer 1

actually i think SQLServer 2005 XML is quite good documented.
CONTENT is the default and allows any valid XML. DOCUMENT is more specific and means that the XML-Data you can to store is only allowed to have a single Top-Level node.

Create:

CREATE TABLE XmlCatalog ( 
   ID INT PRIMARY KEY, 
   Document XML(CONTENT myCollection))

Insert:

INSERT INTO XmlCatalog VALUES (2, 
'<doc id="123">
    <sections>
   <section num="1"><title>XML Schema</title></section>
   <section num="3"><title>Benefits</title></section>
   <section num="4"><title>Features</title></section>
    </sections>
</doc>')

Select:

SELECT xCol.query('/doc[@id = 123]//section')   
FROM XmlCatalog   
WHERE  xCol.exist ('/doc[@id = 123]') = 1

...and so on. The query language exceeds more or less in a subset of xpath 1.0.

If you amend an XSD it is checked on Inserts and Updates and stored within the xml of each element. As far as i understand the doc it is also allowed to add multiple schemas for one column so that entries can reference to different schemas.

EDIT:
Ok, after reading the specific parts of the documentation i think i understand what your problem is. The reference isn't very clear on that point but as far as i understand it only Entries with one top level node can to be bound to XSD schemas.
Due to the fact that XSD-Schemas require a single top level node defining the used XSD file it won't be possible to validate fragments containing more than one top level element. I haven't tried but i think it can't be done.
However it seems to be valid to define a CONTENT column, amend an XSD and store both, XML with one top level node referencing the XSD as well as XML-fragments which will only checked for wellformedness. The fragments can be accessed using the XPath query language show in the select statement above.
I can't tell you much about performance implications. The reference mentions that XSDs are stored inline so this will need some extra space within the db. The XPath queries need to be executed too. Despite the fact that xpath usually is quite fast i guess it could decrease performace cause it needs to be performed on each row to get the result. To be sure i think you have to check the execution plan for your specific query depending on size and complexity of the stored xml as well as the xpath expression.