When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?

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]

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

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

The first character isn't Unicode, per se. I mean, technically all characters in XML within SQL Server are encoded as UTF-16 Little Endian, so in that sense they are all Unicode. But, what you are seeing is just the escaped notation for a character, in this case "2", which has a hex / binary value of "32".

The problem is simply that XML names cannot start with a number. The following tests show that an attribute name or element name starting with a number gets an error, but starting with an underscore ( _ ) or a letter is just fine.

SELECT CONVERT(XML, N'<test><row 2016-12-17="2" /></test>');
/*
Msg 9455, Level 16, State 1, Line 10
XML parsing: line 1, character 12, illegal qualified name character
*/


SELECT CONVERT(XML, N'<test><2016>a</2016></test>');
/*
Msg 9455, Level 16, State 1, Line 10
XML parsing: line 1, character 8, illegal qualified name character
*/


SELECT CONVERT(XML, N'<test><row _2016-12-17="2" /></test>');
/*
<test>
  <row _2016-12-17="2" />
</test>
*/


SELECT CONVERT(XML, N'<test><row x2016-12-17="2" /></test>');
/*
<test>
  <row x2016-12-17="2" />
</test>
*/

So, you need to prefix the column names with a character that is valid as an initial character for an XML attribute or element name.

---

Also, are you sure that it is "working" with FOR XML AUTO? From what I can see, it is simply auto-converting the "invalid" character to _x0032_:

SELECT tmp.* FROM (SELECT 2) tmp([2016]) FOR XML AUTO;

Returns:

<tmp _x0032_016="2" />