Create element names from data in "FOR XML PATH" query?

Hi, all. I am writing a stored procedure to create an XML-formatted export from a relational database. I am succeeding for the most part with "FOR XML PATH" queries, thanks to help from these forums, but I've hit a new issue.

I have a table we'll call "facet", and here is a subset of the table's columns:

- facet_id (nvarchar(10))

- facet_type (nvarchar(3))

- facet_value (nvarchar(255))

Part of the XML schema requires that I list these facets, and the element ID is the facet ID. I need to create this:

<facet_id>facet_value</facet_id>

<facet_id>facet_value</facet_id>

...with, of course, both "facet_id" and "facet_value" populated from the database columns. This part of the extract creates subelements to the facet owners, and there is a lower level that contains subelements to some of the facets.

Is there any way to do this? The only alternative I can see is to create a table function to pivot the "facet" table into a horizontal version of itself, but this is ugly for two reasons: performance and the complications it will create when I have to create the subelements to the facets themselves.

Thanks!

I have a similar need. I have a table-valued function that I want to return XML in which the field name itself is defined by data. In my case, these are phone numbers and I want to query a table and return a list

<PrimaryPhone>444-444-4444</PrimaryPhone>
<HomePhone>555-555-5555</HomePhone>

etc., where the node name is defined in a link table.

Best I can come up with so far is something like:

SELECT
'<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE

But the special symbols (<, >, etc) are automatically converted to their escaped equivalents so that will not work.

Any advice?|||

cast the string expression to xml should work:

SELECT cast
( '<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' as xml) AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE