DrLechter
asked on
Converting to XML-like format
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.
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.
For XML will give you actual XML output... you need to do it through the select statement... using functions may help
Many times I'm asked for non-standard XML format and I end up building it in the SQL statement
SELECT '<xml tab>' + field + '</tag>'
FROM TABLE
if the field is of decimal or integer type then you have to convert it to character
CAST (field AS varchar (100))
Many times I'm asked for non-standard XML format and I end up building it in the SQL statement
SELECT '<xml tab>' + field + '</tag>'
FROM TABLE
if the field is of decimal or integer type then you have to convert it to character
CAST (field AS varchar (100))
ASKER
As I mentioned in my original question, I wanted the XML hierachical structure with none of the tags presents. That would seem to suggest that if one were to use the FOR XML approach, one would have to string the tags out later. It might also be necessary to convert meta characters back to their original form (such as > being converted back to ">").
I decided to take another approach: I put the records from my three tables into a temp table along with an artifical key. The key allows me to sort the records into the correct order as required by the hierachical structure. This approach allows me to generate the hierachical structure without having to undo the undesirable stuff the FOR XML does for this application. Here is what I came up with:
(If anyone sees a more direct approach, I would appreciate the input).
-- fill temp temp with Ivans_Policy_Records and key
insert into #TempIvansRecord (SortKey, IvansRecord)
select
right (replicate (' ', 8) + convert (nvarchar, ipr.Ivans_Policy_Record_ID ), 8) -- iprID part of key
+ replicate (' ', 8) -- iirID part of key
+ replicate (' ', 8), -- ivrID part of key
ipr.Ivans_Policy_Record
from Ivans_Policy_Record ipr
where ipr.Ivans_Export_ID = @IvansExportID
-- fill temp temp with Ivans_Insured_Records and key
insert into #TempIvansRecord (SortKey, IvansRecord)
select
right (replicate (' ', 8) + convert (nvarchar, ipr.Ivans_Policy_Record_ID ), 8) -- iprID part of key
+ right (replicate (' ', 8) + convert (nvarchar, iir.Ivans_Insured_Record_I D), 8) -- iirID part of key
+ replicate (' ', 8), -- ivrID part of key
iir.Ivans_Insured_Record
from Ivans_Insured_Record iir
inner join Ivans_Policy_Record ipr on ipr.Ivans_Policy_Record_ID = iir.Ivans_Policy_Record_ID
where ipr.Ivans_Export_ID = @IvansExportID
-- fill temp temp with Ivans_Vehicle_Records and key
insert into #TempIvansRecord (SortKey, IvansRecord)
select
right (replicate (' ', 8) + convert (nvarchar, ipr.Ivans_Policy_Record_ID ), 8) -- iprID part of key
+ right (replicate (' ', 8) + convert (nvarchar, iir.Ivans_Insured_Record_I D), 8) -- iirID part of key
+ right (replicate (' ', 8) + convert (nvarchar, ivr.Ivans_Vehicle_Record_I D), 8), -- ivrID part of key
iir.Ivans_Vehicle_Record
from Ivans_Vehicle_Record ivr
inner join Ivans_Policy_Record ipr on ipr.Ivans_Policy_Record_ID = iir.Ivans_Policy_Record_ID
inner join Ivans_Insured_Record iir on iir.Ivans_Policy_Record_ID = ipr.Ivans_Policy_Record_ID
where ipr.Ivans_Export_ID = @IvansExportID
-- now insert data into the real Ivans_Record table in the correct order
insert into Ivans_Record (Ivans_Export_ID, Ivans_Record)
select
@IvansExportID,
tir.IvansRecord
from #TempIvansRecord tir
order by tir.SortKey
I decided to take another approach: I put the records from my three tables into a temp table along with an artifical key. The key allows me to sort the records into the correct order as required by the hierachical structure. This approach allows me to generate the hierachical structure without having to undo the undesirable stuff the FOR XML does for this application. Here is what I came up with:
(If anyone sees a more direct approach, I would appreciate the input).
-- fill temp temp with Ivans_Policy_Records and key
insert into #TempIvansRecord (SortKey, IvansRecord)
select
right (replicate (' ', 8) + convert (nvarchar, ipr.Ivans_Policy_Record_ID
+ replicate (' ', 8) -- iirID part of key
+ replicate (' ', 8), -- ivrID part of key
ipr.Ivans_Policy_Record
from Ivans_Policy_Record ipr
where ipr.Ivans_Export_ID = @IvansExportID
-- fill temp temp with Ivans_Insured_Records and key
insert into #TempIvansRecord (SortKey, IvansRecord)
select
right (replicate (' ', 8) + convert (nvarchar, ipr.Ivans_Policy_Record_ID
+ right (replicate (' ', 8) + convert (nvarchar, iir.Ivans_Insured_Record_I
+ replicate (' ', 8), -- ivrID part of key
iir.Ivans_Insured_Record
from Ivans_Insured_Record iir
inner join Ivans_Policy_Record ipr on ipr.Ivans_Policy_Record_ID
where ipr.Ivans_Export_ID = @IvansExportID
-- fill temp temp with Ivans_Vehicle_Records and key
insert into #TempIvansRecord (SortKey, IvansRecord)
select
right (replicate (' ', 8) + convert (nvarchar, ipr.Ivans_Policy_Record_ID
+ right (replicate (' ', 8) + convert (nvarchar, iir.Ivans_Insured_Record_I
+ right (replicate (' ', 8) + convert (nvarchar, ivr.Ivans_Vehicle_Record_I
iir.Ivans_Vehicle_Record
from Ivans_Vehicle_Record ivr
inner join Ivans_Policy_Record ipr on ipr.Ivans_Policy_Record_ID
inner join Ivans_Insured_Record iir on iir.Ivans_Policy_Record_ID
where ipr.Ivans_Export_ID = @IvansExportID
-- now insert data into the real Ivans_Record table in the correct order
insert into Ivans_Record (Ivans_Export_ID, Ivans_Record)
select
@IvansExportID,
tir.IvansRecord
from #TempIvansRecord tir
order by tir.SortKey
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DrLechter,
> It sounds suspiciously like “FOR XML” might be useful for this.
yes you are right
See this
http://www.sitepoint.com/article/data-as-xml-sql-server