Create XML tags from data in a 'FOR XML' query?

I am writing a long stored procedure to produce an XML output from a
relational database. For the most part (with help from these forums), it's
going well.
New question: I have a table we'll call 'Facets'. A simplified subset of
its columns include
facet_guid
facet_id
facet_type
facet_value
Is there a way to configure the query to create a set of elements from this
table using "facet_id" as the element name and "facet_value" as the value of
the element?
If not, I'm faced with the prospect of creating a function to pivot the
table, which would cause further problems in that I have a lower-level table
that I need to associate with each facet (via sub-query) using the facet_gui
d.
Any assistance would be appreciated. Thanks!Hello Grelan,
The best way to do this would be build up the command as a string (in T-SQL
or otherwise), the use sp_executesql to get the job done. Where you'll first
run into problems is that column names can't be resolved from a variable.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/