XML Path in SQL Server

Now we see the XML Path (Xpath) query in SQL Server.

Here using Xpath query we can get the column values into a single row.

SELECT name FROM sysusers                

Result: It will display all user name in a column.

SELECT name FROM sysusers FOR XML PATH ('')

Result: It will display all user name in a single row with name tag.

SELECT ',' + name FROM sysusers FOR XML PATH ('')

Result: It will display all user name in a single row by removing tag and added comma(,) before each user name.

SELECT STUFF((SELECT ',' + name FROM sysusers FOR XML PATH ('')),1,1,'')

Result: It will display all user name in a single row and first comma(,) is not showing for using STUFF() function.

This is how we can get all column values into a single row value.

               

Now we see the Reverse case. Split the Row values (which is separated by comma) into a Column i.e. row values are divided into a column.

SELECT T.Name AS [TableName], C.name AS [ColumnList]

FROM   sys.tables T

       INNER JOIN sys.columns C ON T.object_id = C.object_id

WHERE  T.name = 'OUSR'

Result: It will display the table name and its all column name. Here I use OUSR table. You can try with other table.

Now I use a Temporary table to save this Column names into a single Row (separated by comma) by using XML path and STUFF() function.

SELECT V1.* INTO #TEMP

FROM ( SELECT 'OUSR' AS [TableName], STUFF((SELECT ',' + C.name

              FROM   sys.tables T

                           INNER JOIN sys.columns C ON T.object_id = C.object_id

              WHERE  T.name = 'OUSR'

              FOR XML PATH ('')),1,1,'') AS [ColumnList]

       ) V1

SELECT * FROM #TEMP

Result: All column names are in a single row (separated by comma)

Now see the row values into a column list.

SELECT A.TableName, 

       Split.a.value('.', 'VARCHAR(MAX)') AS ColumnList

FROM  (SELECT TableName , 

       CAST ('<tag>' + REPLACE(ColumnList , ',', '</tag><tag>') + '</tag>' AS XML) AS String 

       FROM  #TEMP ) AS A

CROSS APPLY String.nodes ('/tag') AS Split(a) 

Result: Row value are now separated by Column.

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

https://plus.google.com/+ArkaGupta-arkagupta/posts