Best practice for handling XML schema hierarchies?

I have a number of tables with columns of xml datatype. Each of these columns are typed against a different XML schema collection. However, each of the XML schema collections contain a hierarchy of schema definitions - and the schemas towards the top of the hierarchy are used by a number of different XML schema collections. I want to define the schemas in such a way that if I need to change a schema towards the top of the hierarchy, I only need to change it in one place.

I understand that it is not possible to reference a schema in one collection from another - is my understanding correct? (If I am wrong, then please disregard the following)

If the schemas must be duplicated in each xml schema collection that needs them, then I am considering the following approach. Are there any better methods available?

- Create a 'reference' xml schema collection that contains all the schemas
- Create a table that relates a schema to all the collections that need it
- Write a stored proc that updates all the individual collections appropriately when the reference collection is updated

Using this method, I would anticipate updating the 'reference' schema and running the stored procedure at a quiet time

I could just use a single collection for everything - but although it would ensure that the contents of a column satisfied a schema - it wouldn't check that it satisfied the correct schema. I guess I could put separate validation on the column to ensure that the right contents had been added, but this seems to run counter to the whole idea of using the XML collections

Any thoughts?

You are correct that you cannot refer to schemas from other schema collections.

You approach of using a master schema collection sounds ok. Alternatively, you could use a special table that contains each one of the schemas in an XML datatype column. That way, you could even perform updates on the schemas programmatically, and you would preserve annotations and comments in the schema as an added bonus.

You then could still do the stored procs.

Note however, that you have to be careful with evolving your schemas in that they should only be gaining new elements and types. Otherwise the schema collection will disallow such updates since the cost of revalidation and potential validation failure of old data was too high for be done implicitly...

Best regards

Michael

|||Thanks Michael - will give it a go

Best Method for Handling Long Strings in Stored Procedures?

What is the best way to handle long strings in stored procedures, especially strings that my exceed 8000 characters?

I have a procedure that retrieves a couple groups of sales records that require follow-up for each salesman. This information is then formatted into the body of an HTML email (the number of data items for each quote require the use of tables to be readable in the email) by appending the necessary HTML and record data to a VARCHAR(8000) variable which is ultimately used as the body of the email.

The procedure functions great but I have encountered two unexpected issues I need some help resolving.

1. How can I handle strings longer than 8000 characters? It looks like if I have more than about 12 – 15 records returned by the time I add the HTML I am exceeding this limit and the balance is just truncated.

2. There seem to be "breaks" that are inserted in string/variable based on criteria I haven't figured out. Occasionally these occur in the middle of one of the HTML tags and break the corresponding rendering. I have tried, with limited to success to prevent this by inserting CHAR(10) characters at appropriate places to keep line lengths to about 80 characters but that consumes valuable character space and seems to only be about 60% effective. Can anyone enlighten me on what is causing this?

Thanks for your help.For item 1 use the ntext field type. You can store an unlimited length text value in an ntext field. You can set and get data with an ntext field just like you would with a varchar. You can't use ntext fields quite like you would a character fields (e.g., you can't use it in a WHERE clause) but if you just want to save and retrieve it you should be ok.

For item 2 I don't know, I haven't encountered that problem.|||I am using a variable to hold the string in the stored proc as it is assembled. It was my understanding that text and ntext fields could not be used for variables. Are you creating a cursor with an ntext field or what?

What I am trying to do it insert some text into the variable (header) then append some more text (section header) then I iterate throught the results from the select statement appending each returned record to the variable. Then I append more text (2nd section header) and again iterate throught those select results and append each. Then I return this now very fat variable to the calling proc which uses it as the body of the email.

Is there a better approach for this type of task? Basicaally I am trying to dynamically build an email body that can exceed 8000 characters with some regularity.

Thanks,

--Marshall|||It might be easier to do the string building from a calling program (C#, VB.Net). I don't know of a way to concatenate ntext fields in a sproc. When I have used ntext fields they just pass into and out of a sproc. The sproc itself doesn't manipulate them.