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
