11

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 ;

enter image description here

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

<p Guid="3C3359E3-CFE5-E511-80CA-005056A90901"
  _x0032_016-12-17="2" --> should be 2016-12-17="2" 
  _x0032_016-12-18="2" --> should be 2016-12-18="2" 
  _x0032_016-12-19="2" --> should be 2016-12-19="2" 
/>

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

How can i fix this?

  • Which version of SQL Server is this for? – ypercubeᵀᴹ Dec 20 '16 at 16:12
  • Sql Server 2012, but that is not the point, it is the specs of the xml that's important in this case – Bunkerbuster Dec 20 '16 at 19:08
  • This seems like an XY-problem. Using a date as an attribute name in XML seems ill advised even if this worked as intended. I'd be more inclined to store the date as the value of an attribute or maybe as the text of an element, depending on what I planned to do with it. If necessary, I'd make multiple elements with pairs of attributes. – jpmc26 Dec 21 '16 at 6:45
14

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.

  • That was the missing link, also for xml path(''), root('root') is now working. – Bunkerbuster Dec 20 '16 at 15:26
6

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" />

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.