SQL Server Performance Forum – Threads Archive

Storing Xml

Any suggestions on storing xml in the database? I’ve got an xml document which basically represents a set of questions and their attributes, the document also represents the relationship of these questions ie a question may have 3 possible awnsers and each answer could mean a branch off into certain child questions for that specific answer. I’ve thought about storing each question individually with references to parent or child questions ect.. but what I’d really like to do is store the document as is in a table and store the answers to the questions in another xml document in a table.
The reason I’d like it in xml is because I’d like to keep the hierachy of the questions and the relationships as it could get nasty trying to represent this in a table and all the questions can have different attritbutes. I’m thinking of using a text column data type as the size of the field is indeterminate. Does anyone have any experience doing this kind of thing and any advice on possible pitfalls or ideas ect..? Thanks
Shaun World Domination Through Superior Software

Don’t have much insight on XML but sure use the FOR XML clause of the SELECT statement, refer to books online (SQL 2K) which has fair explanation in this regard. HTH Satya SKJ

I’m familiar with OPEN XML and manipulating documents but I’ve no experience on how it performs, I assume it has to create an xml dom object (not sure about this) so I’m worried that too many hits on the database could bog it down. Also does anyone know about the overhead of storing data as text fields? I understand that text data fields are stored separately but that there is a way to force a text field to only store the data there if the data size is over a specific limit? I’ve forgotten where I read it though! Cheers
Shaun World Domination Through Superior Software

text (+ ntext and image) fields may be stored inline in the row by setting the table option ‘text in row’ to an integer between 24 and 7000. If subsquent text inserts or updates are smaller than this threshhold, they are stored within the data row. If this threshhold is exceeded a pointer to the text is stored instead. Storing your XML as a text is fine, but a few gotchas which may or may not concern you.. Searching will perform worse than if you were to replicate the structure of the XML in dedicated tables. You will also lose the ability to search flexibly (give me all questions with three or more choices etc). This might not be an issue for you. My other point would be if you ever wanted to change the structure of the XML, this will become more difficult than if you were generating XML on the fly from sql server. Of course, if you were to store each attribute in tables, you are then faced with the paradigm of how to represent an hierarchy which could be nested to an unknown amount of levels. It would then be very difficult to interrogate this data in a single TSQL statement (I think it would require an sp with cursors).
With this in mind, if the two caveats above dont concern you, and youre just streaming the xml straight back to an app/webclient with no processing required, then Id say youre probably right to choose to store it as a text.

Thanks very much, that ‘text in row’ was what I’d read about, I’m not to worried about interrogation of the xml documents as they’ll all be transformed with xslt within the application, the changing of the structure is one I might have to think about but will probably live with as the benefit of storing it as xml outweighs the problem of updating the document structure (famous last words <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />). Thanks for the help. Cheers Shaun World Domination Through Superior Software

]]>