When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode? I am using this great example https://dba.stackexchange.com/a/25818/113298 from Bluefeet, to create a pivot and transform it to xml data.

Declaring the param

DECLARE @cols AS NVARCHAR(MAX),  @query  AS NVARCHAR(MAX);


Next there is a CTE with a lot of code, the endresult of the CTE is put in a temp DB (same as in the example)

SELECT 
B.[StayDate] -- this is a date dd-mm-yyyy
, B.[Guid]
INTO #tempDates
FROM BaseSelection B


Generating the cols (same as the example)

SELECT @cols = STUFF((SELECT distinct ',' +QUOTENAME(convert(char(10), [StayDate] , 120)) 
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');


The result set is what i should expected   

set @query = 
   'SELECT [Guid],' + @cols +'
    FROM
    (
        SELECT 
            [StayDate] 
           ,[Guid]
        FROM #tempDates
    ) A
    pivot
    (
        count([StayDate])
        for [StayDate] in (' + @cols +')                    
    ) p
    '
EXEC sp_executesql  @query ;




When I try to transform it to XML my attributes are only partially converted

set @query = 
   'SELECT [Guid],' + @cols +'
    FROM
    (
        SELECT 
            [StayDate] 
           ,[Guid]
        FROM #tempDates
    ) A
    pivot
    (
        count([StayDate])
        for [StayDate] in (' + @cols +')                    
    ) p
    for xml auto
    -- when using for XML path i will get a error
    -- FOR XML PATH(''''), ROOT(''root'') 
    -- Msg 6850, Level 16, State 1, Line 3
    -- Column name '2016-12-17' contains an invalid XML identifier 
    -- as required by FOR XML; '2'(0x0032) is the first character at fault.
    '
EXEC sp_executesql  @query ;


resultset

&lt;p Guid="3C3359E3-CFE5-E511-80CA-005056A90901"
  _x0032_016-12-17="2" --&gt; should be 2016-12-17="2" 
  _x0032_016-12-18="2" --&gt; should be 2016-12-18="2" 
  _x0032_016-12-19="2" --&gt; should be 2016-12-19="2" 
/&gt;


Have I missed something, why is only a portion of the date converted to unicode?

How can i fix this?
Attribute names in XML are not allowed to start with a number, see NameStartChar.

You have to come up with alternative names for your attributes and encode that in a separate @cols variable specifying column aliases for your dynamic pivot query.

SELECT @cols2 = STUFF((SELECT distinct ',' +
                       quotename(convert(char(10), [StayDate] , 120)) + 
                       ' as '+ QUOTENAME('z'+convert(char(10), [StayDate] , 120)) 
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');


Result;

[2016-12-20] as [z2016-12-20],[2016-12-21] as [z2016-12-21]


&lt;p Guid="6365FC57-F476-4703-B9D4-1EB81288FF30" z2016-12-20="0" z2016-12-21="1" /&gt;
&lt;p Guid="B38FA9DB-B4E1-4725-8F3B-3AF6E009C10A" z2016-12-20="1" z2016-12-21="0" /&gt;


When you are using for xml auto SQL Server does that for you.