Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  SQL Server, ASP and For XML AUTO, ELEMENTS

Author  Topic  xanthus Starting Member 1 Post Posted - 2006-06-27 : 17:36:43 We currently have a table that consists of over 200 columns like y0_e_mnt, y1_e_mnt, y2_e_mnt, y0_mnt_os, y1_mnt_os, y2_mnt_os etc as money. This table begs to be split into parent-child and that is what I want to do. I want to split the current table to parent parent_id non-splittable column1 ... children child_id parent_id year_offset money_value where each row in the child table uses the year_offset/money_value column for the previous y0_e_mnt, y1_e_mnt etc. The data in this table is returned to ASP pages (as well as a LOT of reports) using FOR XML AUTO, ELEMENTS. The ASP then sets the dataSrc and dataFld of the numerous HTML elements using the known XML data island returned and tag(column) names. The problem is how to tell the ASP page what each new child row represents in the new tables so it can set the dataFlds correctly. For example, the %_mnt columns represent maintenance costs, the %_mnt_os columns represent maintenance-outside-services, so I need to know. The easiest way for both ASP and reports is to return the data in the same XML format as before, and that is my question. I want to specify the XML tag names like you would in a select statement with the AS clause so they match the old column names. I have considered saving the column name to return in the child record such as [children].[column_name] varchar(255). With this I then I thought to specify the AS with the previous column name field like select [children].[money_value] AS [children].[column_name] but that was quickly out the window! I also thought of using Dynamic SQL to create a temp table with [column_name], load it and return it with the FOR XML clause. I have also looked at the EXPLICIT option, SQL templates, and XSL transformation once the ASP/reports get the XML. I am unsure of all of these though. Does anyone have any ideas, or do I need to just bite the bullet and change everything to handle the XML returned with the new parent-child tables? Thanks