Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.
<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...
</Customer>
</Customers>
Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!
My apologies for the delay in answering....
If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...
Here is the EXPLICIT solution:
select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"
union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers
union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers
union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers
-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit
and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):
select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')
Best regards
Michael
