Hi For generating audit reports, I want to populate a sql table with fieldnames and fieldvalues (converted to varchars) from a table. Example if my table is Table Customers: cust_code cust_name ---------- ---------- 'cust1' 'Dupont' I want to get the folowing table : Table result Fieldname Field_Value ---------- ------------- 'Cust_code' 'cust1' 'Cust_name' 'Dupont' My feeling is that the simplest way to do that is to use XML, the answer could be "generic" it si very easy to obtain a xml view of the table : select * from Customers for xml auto; create table customers (cust_code varchar(15), cust_name varchar(25)) insert customers select 'cust1', 'Dupont' select * from customers for xml auto <customers cust_code="cust1" cust_name="Dupont"/> How to get fieldnames and field values in a result table ? Please help I am a XML beginner ! Patrick
pi wrote: > I want to get the folowing table : > > Table result > > Fieldname Field_Value > ---------- ------------- > 'Cust_code' 'cust1' > 'Cust_name' 'Dupont' > <customers cust_code="cust1" cust_name="Dupont"/> > > How to get fieldnames and field values in a result table ? Here is an example that works with SQL Server 2005, making use of the nodes XQuery method: DECLARE @x xml; SET @x = '<customers cust_code="cust1" cust_name="Dupont"/>'; SELECT T.att.value('local-name(.)', 'nvarchar(20)') AS Fieldname, T.att.value('.', 'nvarchar(20)') AS Field_Value FROM @x.nodes('//@*') T(att); I have however some doubts that you need to use XML to create such a table. -- Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/
"Martin Honnen" a écrit dans le message de news: ehj$AoIAJHA.5660@TK2MSFTNGP04.phx.gbl... > pi wrote: > >> I want to get the folowing table : >> >> Table result >> >> Fieldname Field_Value >> ---------- ------------- >> 'Cust_code' 'cust1' >> 'Cust_name' 'Dupont' > >> <customers cust_code="cust1" cust_name="Dupont"/> >> >> How to get fieldnames and field values in a result table ? > > Here is an example that works with SQL Server 2005, making use of the > nodes XQuery method: > > DECLARE @x xml; > SET @x = '<customers cust_code="cust1" cust_name="Dupont"/>'; > > SELECT T.att.value('local-name(.)', 'nvarchar(20)') AS Fieldname, > T.att.value('.', 'nvarchar(20)') AS Field_Value > FROM @x.nodes('//@*') T(att); > > > I have however some doubts that you need to use XML to create such a > table. > > > -- > > Martin Honnen --- MVP XML > http://JavaScript.FAQTs.com/ Thanks Martin for your expert answer. To be honest going to a XML variable was not my first idea, but after spending some time trying to deal with pure SQL and system tables , I had the idea of XML, which I find very tricky. (And I didnt find the pure SQL answer to that problem ...) Congratulations Patrick
