Converting to XML-like format
Posted on 2006-07-05
I have three tables arranged in standard SQL relationships: Policy_Record (ipr), Insured_Record (iir) and Vehicle_Record (ivr). The relationship is that each ipr can have one and only one iir. Each ipr can have 0 or more ivr’s.
I want to export the data in a hierarchy similar to XML. That is, I want to export an ipr, an iir and 0 or more ivrs. This sequence must be repeated once for each ipr. The main difference I can see between this and “real” XML is that this particular export must not have any tags. Also, the actual records to be exported are fixed length char strings based on a concatenation of elements in the base tables. For example, there are multiple fields in ipr. These fields would be combined into a fixed length, fixed format string of 300 bytes. I actually built some user defined functions to perform formatting for each of the record types. Note that each record type has a record type indicator embedded in the string (POL, INS, VEH). This is how the records are decoded on the receiving end.
Do you have any idea how this can be accomplished? It sounds suspiciously like “FOR XML” might be useful for this.