CODEWITHSUNDEEP
sqlsql-servert-sql
mohamed-mhiri
mohamed-mhiri

Reputation: 232

Value and TYPE in XML PATH

I have the following query to get all the employees firstname in the XML format :

SELECT ' ,' +de.Firstname 
              FROM dbo.Employee de
              FOR XML PATH ('')

I want to understand what's the difference if I update it to below :

  SELECT (SELECT ' ,' +de.Firstname 
              FROM dboDimEmployee de
              FOR XML PATH (''),TYPE).value('.[1]', 'NVARCHAR(MAX)');

What do TYPE and value() stand for ?

Upvotes: 2

Views: 1190

Answers (1)

Martin Smith
Martin Smith

Reputation: 452988

The TYPE means the sub query returns a value of XML datatype instead of a string.

The .value then calls a method on the XML datatype to get the value of the element as a string.

The reason why the second one is preferable to the first is in the case that the result of the concatenation contains characters such as <. These will appear as desired in the final result and not as raw XML entities such as &lt;

SELECT 'John' AS Firstname into #TEMP UNION ALL SELECT 'Jack <&>'   

SELECT ' ,' +de.Firstname -- ,John ,Jack &lt;&amp;&gt;
              FROM #TEMP de
              FOR XML PATH ('')


SELECT (SELECT ' ,' +de.Firstname -- ,John ,Jack <&>
              FROM #TEMP de
              FOR XML PATH (''),TYPE).value('.[1]', 'NVARCHAR(MAX)');

The second one behaves the same way as

SELECT CAST(',John ,Jack &lt;&amp;&gt;' AS XML).value('.[1]', 'NVARCHAR(MAX)');

(By the way if you run these in SSMS you may be surprised that the result of the first one is actually a string as it appears as clickable XML in the gridview but this is due to special casing of the implicit column name XML_F52E2B61-18A1-11d1-B105-00805F49916B not the datatype)

Upvotes: 2

Related Questions