StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A version where there is no dynamic involved. If you have column names that is invalid to use as element names in XML this will fail.</p> <pre><code>select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key], T2.N.value('text()[1]', 'nvarchar(max)') as Value from (select * from TableA for xml path(''), type) as T1(X) cross apply T1.X.nodes('/*') as T2(N) </code></pre> <p>A working sample:</p> <pre><code>declare @T table ( Column1 varchar(10), Column2 varchar(10), Column3 varchar(10) ) insert into @T values('V1','V2','V3') select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key], T2.N.value('text()[1]', 'nvarchar(max)') as Value from (select * from @T for xml path(''), type) as T1(X) cross apply T1.X.nodes('/*') as T2(N) </code></pre> <p>Result:</p> <pre><code>Key Value -------------------- ----- Column1 V1 Column2 V2 Column3 V3 </code></pre> <p><strong>Update</strong></p> <p>For a query with more than one table you could use <code>for xml auto</code> to get the table names in the XML. Note, if you use alias for table names in the query you will get the alias instead. </p> <pre><code>select X2.N.value('local-name(..)', 'nvarchar(128)') as TableName, X2.N.value('local-name(.)', 'nvarchar(128)') as [Key], X2.N.value('text()[1]', 'nvarchar(max)') as Value from ( -- Your query starts here select T1.T1ID, T1.T1Col, T2.T2ID, T2.T2Col from T1 inner join T2 on T1.T1ID = T2.T1ID -- Your query ends here for xml auto, elements, type ) as X1(X) cross apply X1.X.nodes('//*[text()]') as X2(N) </code></pre> <p><a href="http://sqlfiddle.com/#!6/76829/3" rel="noreferrer">SQL Fiddle</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload