ARJUN ARJUN - 1 month ago 7
SQL Question

what is type means

STUFF((SELECT distinct ',' + QUOTENAME(c.Error_Code)
FROM
(SELECT Connection_type, Error_Code, Count
FROM
(SELECT
Connection_Type, error_code, count(*) AS count,
row_number() over(partition by Connection_Type order by count(*) desc) as ROWNUM
FROM
Staging
WHERE
TransactionDate >= convert(varchar, getdate() -1, 111)
AND Status != 'Deliver'
GROUP BY
Connection_Type, error_code) a
WHERE
rownum <= 10) c
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


what does "TYPE" means?what is significant of using TYPE

Answer

It returns a value typed as XML.

A common alternative that does not use this and just returns directly as string is below.

SELECT STUFF((SELECT ',' + QUOTENAME(c.Error_Code)
              FROM   (VALUES('FOO & BAR'),
                            ('1 < 4 ') ) c(Error_Code)
              FOR XML PATH('')), 1, 1, '') 

But this does not handle XML entitisation correctly and returns

[FOO &amp; BAR],[1 &lt; 4 ]

Returning the XML datatype and calling .value on it correctly returns

[FOO & BAR],[1 < 4 ]
Comments