CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012xquery
user786
user786

Reputation: 4364

sql from xquery need some explanation

I came across this line of code and it's not making any sense to me

SELECT @cols = STUFF((
        SELECT ',' + QUOTENAME(DateT)
        FROM cico
        GROUP BY DateT
        ORDER BY DateT
        FOR XML PATH('')
            ,TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

I know for xml path('') is to create xml structure of result set returned from select statement

but what is (select ....,TYPE).value(....)

the above line is simply assigning TYPE to (select..., ) only brackets. its not a function. in other words its like this select .... ,TYPE

another thing that is difficult for me to understand is why we are assigning value to bracket (...).value(...)

Upvotes: 0

Views: 42

Answers (1)

shA.t
shA.t

Reputation: 16958

TYPE
Specifies that the query returns the results as the xml type.

SQL Server support for the xml (Transact-SQL) enables you to optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive. This allows you to process the result of a FOR XML query on the server.

SELECT ... FRO XML PATH(''), TYPE will returns xml, then you can use xquery methods like .value() over it.
Note that without using TYPE your query will returns an error :

Cannot call methods on nvarchar(max).

Because you can not use xquery methods over a nvarchar(max) result.

Upvotes: 2

Related Questions