This is probably a question for Stack Overflow, but I would like a Firebird specific answer so asking here.
I would like to store simple documents in a Firebird database as an ordered collection of paragraphs as follows: Document 1 Record 1 -> 1. Heading 1 Record 2 -> 1.1 Heading 2 Record 3 -> 1.1.1 Heading 3 Record 4 -> 1.1.1.1 Paragraph bla bla bla Record 5 -> 1.1.1.2 Paragraph bla bla bla Record 6 -> 1.1.2 Heading 3 Record 7 -> 1.1.2.1 Paragraph bla bla bla Record 8 -> 1.1.2.2 Paragraph bla bla bla Record 9 -> 2. Heading 1 Record 10 -> 2.1 Heading 2 ... Document 2 Record n -> 1. Heading 1 Record n + 1 -> 1.1 Heading 2 and so on. I would like concurrent access to different paragraphs of the document and each record can link to paragraph(s) (records) in another 'document'. Therefore I want to move away from creating the document in XML and storing it in a BLOB. I think CTE is out because I can't determine the order of siblings and nested sets are not good for doing a lot of inserts (as is likely when writing documents). Probably the obvious way is to use path enumeration, but I guess this is a relatively common problem (although googling doesn't find many results) and wondered if anyone has a good solution?
