Timothy Graffham (9/19/2012)
I was very excited to go to my post this morning and see 8 responses. I thought I was going to get some real help. Instead, the first response was an attempt but didn't help really, and then everyone just used my post for a chat about general complaints with XML as a technology.Very disappointing, folks. Can you please have those sort of conversations in their own threads?
My format that I illustrated in the original question was not flexible. I MUST display this information in this format even if I have to loop through the data procedurally and dynamically build it as a string. It's an installed base issue that I have to support.
My question is, is it possible? Can anyone tell me how to do this?
All fair points. These forums are in a 'discussion format' as opposed to a 'Q&A format' like http://ask.sqlservercentral.com and sometimes the ensuing discussions tend to dwarf the fact that sometimes people are just looking for a straight-forward answer to their question 🙂
The answer to your question is not simple. You have big challenges because the app is expecting data values to be used as element names which is outside what FOR XML provides. You might want to think about building the XML 'by hand', i.e. by using a variable to build a string in the form the app wants, but that will be a hellish coding assignment.
....
I found this link that asks a similar question: http://stackoverflow.com/questions/3240297/how-do-i-use-column-values-as-xml-element-names-using-for-xml-in-sql-server-2005
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato