2

I wasn't able to find a relevant post, so I decided to ask.

I have the following table in my SQL Server database:

ID       attname    value 
---------------------------------
22405543 blktradind N 
22405543 brkref     IRVTGB2X
22405543 buyamt     104650.2000 
22405543 buycurref  USD 
22405543 Buy53ref 
22405543 Buy56ref 
22405543 Buy57ref   IRVTBEBB

How can I convert this table by using FOR XML variations to a dynamic XML result based on the "attname" that each message has?

For the excerpt above, the desired result would be:

<Message id=22405543>
  <blktradind>N</blktradind>
  <brkref>IRVTGB2X</brkref>
  <buyamt>104650.2000</buyamt>
  <buycurref>USD</buycurref>
  <buy53ref />
  <buy56ref />
  <buy57ref>IRVTBEBB</buy57ref>
</Message>

Thanks

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Panos
  • 55
  • 7
  • This is covered in every FOR XML tutorial -- did you try anything -- do a google search? – Hogan Jul 13 '16 at 19:11
  • 1
    Don't rush into conclusions and look carefully to what I want. I searched but found nothing. – Panos Jul 14 '16 at 08:01
  • Panos, I understand your answer to @Hogan, but without the `@Hogan` there'll be no alert on his side, so he probably would not notice this answer. Just for your information: If you comment, the owner of the question / answer will be alerted by default. If you want an alert to a special user you have to add his nick with a leading `@`. – Shnugo Jul 14 '16 at 10:59
  • @Panos -- Please read the FAQ -- there is an expectation that you try something on this site. This is not a site where you get free consultation. If you did ANY work at all you should show the code that didn't work. If you don't show any code the assumption is that you did no work. This is a question about SQL, there is no SQL in your question. My assumption (which I still believe to be correct) is that you basically did no work before asking me for help. I've no interest in helping people who do no work first -- so I did the nice thing and gently pointed that out. As I did here. – Hogan Jul 14 '16 at 12:15
  • @Shnugo - Thanks Shnugo, I'll keep that in mind – Panos Jul 14 '16 at 15:31
  • 1
    @Hogan - I understand why you may have thought that I did no work and just popped in and asked, but I just wanted to be straightforward and to the point with my question without adding all the things I tried. The bottom line was that I didn't get anywhere with them, even though I spent about 3 hours trying to find a way. The closest I got was `datavalue` and many other variations but never had the value as an actual tag name. I found your comment ironic suggesting "*a google search*" to a developer. Plus it's not covered in *every FOR XML* tutorial. – Panos Jul 14 '16 at 15:53
  • @Panos - As you wish, IMO if you had examples of the stuff you tried you would have gotten an answer quicker than 16 hours. The average time is about 15 mins for well asked questions. – Hogan Jul 14 '16 at 17:12
  • @Hogan, might be, that most of the 28 visitors just did not know how to do this? It is absolutely not trivial and - for sure! - not covered *in every XML tutorial* how to create XML with dynamically named elements. Just being curious: Would you've been able to answer this? – Shnugo Jul 14 '16 at 17:26
  • @Shnugo - of course – Hogan Jul 14 '16 at 18:55

2 Answers2

1

This is not possible normally. SQL Server does not support variable column aliases for your output. But there are workarounds:

string concatenation

The approach is a bit ugly, as I normally would not prefer to create XML via string concatenation. But by wrapping the value with a SELECT FOR XML PATH itself, this is even stable with forbidden characters like <> or &. 

DECLARE @tbl TABLE(ID BIGINT,attname NVARCHAR(100),value NVARCHAR(100));
INSERT INTO @tbl VALUES 
 (22405543,'blktradind','N') 
,(22405543,'brkref','IRVTGB2X') 
,(22405543,'buyamt','104650.2000')  
,(22405543,'buycurref','USD')  
,(22405543,'Buy53ref',NULL) 
,(22405543,'Buy56ref',NULL) 
,(22405543,'Buy57ref','IRVTBEBB');

WITH DistinctIDs AS
(
    SELECT DISTINCT ID FROM @tbl
)
SELECT ID AS [@id]
      ,(
        SELECT CAST(N'<' + attname + N'>' + ISNULL((SELECT value AS [*] FOR XML PATH('')),N'') + N'</' + attname + N'>' AS XML)
        FROM @tbl AS tbl
        WHERE tbl.ID=DistinctIDs.ID
        FOR XML PATH(''),TYPE
       )
FROM DistinctIDs
FOR XML PATH('Message')

The result

<Message id="22405543">
  <blktradind>N</blktradind>
  <brkref>IRVTGB2X</brkref>
  <buyamt>104650.2000</buyamt>
  <buycurref>USD</buycurref>
  <Buy53ref />
  <Buy56ref />
  <Buy57ref>IRVTBEBB</Buy57ref>
</Message>

dynamic SQL

You could build the full statement dynamically and use EXEC(@cmd) to execute it. Something like this:

(Attention!!: The SELECT TOP 1 to get the ID is not appropriate for actual data!)

DECLARE  @cmd NVARCHAR(MAX)=
(
    SELECT 'SELECT ''' + CAST((SELECT TOP 1 ID FROM @tbl) AS NVARCHAR(100)) + ''' AS [@id] ' 
    + (
    SELECT ',''' + ISNULL(value,'') + ''' AS [' + attname + ']'
    FROM  @tbl
    FOR XML PATH('')
    )
    + ' FOR XML PATH(''Message'')'
);
EXEC(@cmd)
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Ugly as it may be, it fits my requirements perfectly and it is a great answer. Thanks Shnugo, much appreciated. – Panos Jul 14 '16 at 10:55
0

As far as I know, there is no control over the way the output is generated in the FOR XML output, aside from manipulating the data types and field names in the query.

You would need to generate it as it normally does, then reprocess it possibly with XSLT to make it into what you want.

This could actually be done on the server if it has support for the CLR.

Terry Carmen
  • 3,720
  • 1
  • 16
  • 32