November 5, 2002 at 1:17 pm
I have a table named TEST. It has 4 columns EmpId, EmpName, EmpAddress, EmpPhone. The data in the table is
EmpId EmpName EmpAddress EmpPhone
1 Mark Aaaa 123
2 Bill Bbbb 456
3 Joe Cccc 789
Now what i want to do is get a transpose of this table for empId = 2 and generate the following XML output string:
<fields>
<field name = "EmpId" value = "2"/>
<field name = "EmpName" value = "Bill"/>
<field name = "EmpAddress" value = "Bbbb"/>
<field name = "EmpPhone" value = "456"/>
</fields>
Please look carefully, the attribute "name" has the column names of my table TEST and the attribute "value" has the actual values of the corresponding columns for EmpId = 2.
Could someone please help me on getting this output
November 5, 2002 at 1:54 pm
Well, here's a start, but somehow I don't feel it's the full solution; nevertheless, it might get you to thinking. Try using FOR XML EXPLICIT:
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Fields!1!Fields],
NULL AS [Field!2!Name],
NULL AS [Field!2!Value]
FROM Test
WHERE EmpId = 2
UNION ALL
SELECT 2, 1, NULL, 'EmpId', EmpId
FROM Test
WHERE EmpId = 2
UNION ALL
SELECT 2, 1, NULL, 'EmpName', EmpName
FROM Test
WHERE EmpId = 2
UNION ALL
SELECT 2, 1, NULL, 'EmpAddress', EmpAddress
FROM Test
WHERE EmpId = 2
UNION ALL
SELECT 2, 1, NULL, 'EmpPhone', EmpPhone
FROM Test
WHERE EmpId = 2
FOR XML EXPLICIT
I believe that will generate the output you are looking for. See Books Online for documentation about FOR XML EXPLICIT, including an explanation of why I generated the output the way I did. Somehow, though, as I said, I don't feel this is a full solution - it works, but it's not very dynamic.
Matthew Burr
November 5, 2002 at 2:13 pm
Thank you Matthew. But as you said that the solution posed by you is not dynamic. Matthew, my table has almost 200 columns, and i do not want to hard code them into the SELECT clause.
I have a solution for this problem right now which works. I am selecting all the column names and inserting them in a temporary table. Then i am selecting the corresponding values and updating the temporary table (here i have to use a EXEC statement to get corresponding values for the particular column name). Then i select from this temporary table using FOR XML EXPLICIT. It works, but the speed is so slow due to 200 columns and as you know EXEC reduces the performance still more.
So could you please help on this.
Thank you.
November 5, 2002 at 2:52 pm
Just playing, how about something like this.
DECLARE @tblname VARCHAR(255)
set @tblname = 'putyourtablenamehere'
DECLARE @colxmlstr VARCHAR(8000)
SET @colxmlstr = 'SELECT ''<fields>
'
SELECT @colxmlstr = @colxmlstr + '<field name = "' + [name] + ' value = "'' + cast(' + [name] + ' as varchar(800)) + ''"/>
' from syscolumns where [id] = object_id(@tblname)
set @colxmlstr = @colxmlstr + '</fields>'' as XMLOutput FROM ' + @tblname
EXEC (@colxmlstr)
Edited by - antares686 on 11/05/2002 3:02:09 PM
November 5, 2002 at 3:14 pm
Antares686,
Thank you for your suggestion.
Thats what i already have and it is working. Could you please suggest something that does not use EXEC statement because, it really is affecting the performance.
November 5, 2002 at 3:23 pm
Try change EXEC to sp_executeSQL, it will attempt to reuse the execution plan but that is the best you can hope for with a dynamically changing structure. Unless you do the PRINT of the @colxmlstr variable to save for reuse later.
PRINT @colxmlstr
And if this output code will be reused then save the output to an Stored Procedure. This would be the all around best I can think of right now.
November 11, 2002 at 3:04 pm
Thank u all
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply