5

In this code I am converting the subjects(columns) English , Hindi , Sanskrith , into rows

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 select @colsUnpivot 
               =stuff(
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)'),
        1, 8, ''
    );

print @colsunpivot

select  @query 
              = 'select Name, Subject,Marks
                 from result2
                 unpivot
                        (
                          marks for subject in (' + @colsunpivot + ')) as tab'

exec sp_executesql @query;

Please explain what does for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)') do here .

17

A common technique for aggregate string concatenation before SQL Server 2017 is using XML data type methods along with STUFF to remove the extra delimiter. In SQL Server 2017 and later, STRING_AGG makes this task much cleaner and easier. The STRING_AGG equivalent:

select @colsUnpivot = STRING_AGG(quotename (C.name),',')
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

The components of the XML expression technique for aggregate string concatenation are as follows.

This subquery will generate a result set with one row per column in the source table with a comma preceding each column name:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

Example result:

,[column1]
,[column2]
,[column3]

Adding the FOR XML PATH(''), TYPE converts these rows into a single strongly-typed XML text node with the concatenated rows, allowing XML methods like value to be used on the type:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE

Result (XML type):

,[column1],[column2],[column3]

Invoking the method value('.', 'NVARCHAR(MAX)') on that XML node converts the XML node to an nvarchar(MAX) string. The value XML data type method takes an XQuery expression with '.' representing the current node (only node here) and the second argument being the desired SQL data type to be returned.

SELECT
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)')

Result (nvarchar(MAX)):

,[column1],[column2],[column3]

Finally, the STUFF function removes the extraneous leading delimiter from the string. There is an error in your original example; the length should be 1 instead of 8 so that only the comma is removed:

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Final result (nvarchar(MAX)):

[column1],[column2],[column3]

I sometimes see folks omit the , TYPE).value('.', 'NVARCHAR(MAX)') when using this technique. The problem with that is that some characters must be escaped with XML entity references (e.g; quotes) so the resultant string will not be as expected in that case.

EDIT:

Incorporating Mister Magoo's recommendation to use (./text())[1] instead of just . as the node spec, the query below will improve performance with large data sets.

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'');
| improve this answer | |
  • in value('.',NVARCHAR(MAX)) what does '.' do ? – Harish Kumar May 21 '18 at 11:45
  • @HarishKumar, I added more info to my answer. Hopefully, it's now clearer. – Dan Guzman May 21 '18 at 11:58
  • 1
    Dan (and @HarishKumar ): Since this was part of the question, I would make it clearer that , TYPE returns the resulting XML (from the FOR XML) as an actual XML type such that the value() method can be called on that subquery, and without , TYPE the FOR XML will return the same thing but as NVARCHAR(MAX) which then would not allow for the .value() to be added. – Solomon Rutzky May 22 '18 at 5:54
4

As Dan said, but also...

Get in the habit of using the text() node in xml values, it won't make much difference to a small result set, but I see no reason to ever NOT use it, and you will really see a benefit in larger data sets.

The red section is painful and can be avoided by the use of (./text())[1]

Compare plans with use of text() node.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.