0

In a former post about generic creation of pivot table I found a working example. I only fail to fully understand the part where the XML ends up to be a varchar column.

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(product) 
                from product
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

I tried to look it up in both the description of the STUFF statement and the FOR XML, but not able to find a explanation of the used code [.value('.', 'NVARCHAR(MAX)')]. It converts XML to VARCHAR, but in which context?

Also in this post there is a partial reference:

The .value('.', 'varchar(max)') simply retrieves the value from the resulting XML fragment, without XML-encoding any "special" characters.

It does not makes clear to me in which context the additional .value is coming from. Is it part of the SELECT or the XML or the STUFF? Can someone explain the context of it? Or point me to the correct documentation?

Community
  • 1
  • 1
J. Kruijt
  • 66
  • 1
  • 13
  • http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – Gurwinder Singh Feb 17 '17 at 12:21
  • Please reevaluate the question....I have edited the question. I think i have explained why former answers where not adequate to my question. – J. Kruijt Feb 17 '17 at 12:48
  • Hi, it's not that complicated... `SELECT ... FOR XML PATH('x')` will create an XML, where each row is wrapped in ``tags. Now let the `PATH('')` emtpy. You get XML which looks like a concatenated string, because there are no ``tags at all. But it is XML. With `,TYPE` you force the sub-select to return XML typed and with `.value()` you read the XML's content. The given examples are actually erronous when it comes to special characters, especially with `<, > and &` as part of your text parts. – Shnugo Feb 20 '17 at 01:09

0 Answers0