Dlaczego podczas konwersji dynamicznego SQL (zapytania przestawnego) na dane wyjściowe xml pierwsza cyfra daty jest konwertowana na Unicode?

11

Korzystam z tego wspaniałego przykładu /dba//a/25818/113298 z Bluefeet, aby utworzyć oś przestawną i przekształcić ją w dane XML.

Deklaracja parametru

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

Następnie jest CTE z dużą ilością kodu, wynik CTE jest umieszczany w temp DB (tak samo jak w przykładzie)

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

Generowanie cols (tak jak w przykładzie)

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

Zestaw wyników jest tym, czego powinienem się spodziewać

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

Kiedy próbuję przekształcić go w XML, moje atrybuty są tylko częściowo konwertowane

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 ;

zestaw wyników

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

Czy coś przeoczyłem, dlaczego tylko część daty jest konwertowana na Unicode?

Jak mogę to naprawić?

sql-server xml pivot

— Bunkier
źródło

Dla której wersji SQL Server to jest?

— ypercubeᵀᴹ

Sql Server 2012, ale nie o to chodzi, to specyfikacja xml jest w tym przypadku

— ważna

To wydaje się problemem XY. Używanie daty jako nazwy atrybutu w XML wydaje się niewskazane, nawet jeśli działało to zgodnie z przeznaczeniem. Byłbym bardziej skłonny do przechowywania daty jako wartości atrybutu, a może jako tekstu elementu, w zależności od tego, co planowałem z tym zrobić. W razie potrzeby utworzę wiele elementów za pomocą par atrybutów.

— jpmc26,

14

Nazwy atrybutów w XML nie mogą zaczynać się od liczby, patrz NameStartChar .

Musisz wymyślić alternatywne nazwy dla swoich atrybutów i zakodować je w osobnej @colszmiennej określającej aliasy kolumn dla dynamicznego zapytania przestawnego.

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,'');

Wynik;

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

Gdy używasz for xml autoSQL Server, robi to za Ciebie.

— Mikael Eriksson
źródło

To było brakujące łącze, także dla ścieżki xml („”), root („root”) już działa.

— Bunkerbuster

6

Pierwszym znakiem nie jest sam w sobie Unicode. Mam na myśli, że technicznie wszystkie znaki w XML w SQL Server są zakodowane jako UTF-16 Little Endian, więc w tym sensie wszystkie są Unicode. Ale to, co widzisz, to tylko znak ucieczki dla znaku, w tym przypadku „2”, który ma wartość szesnastkową / binarną „32”.

Problem polega po prostu na tym, że nazwy XML nie mogą zaczynać się od cyfry. Poniższe testy pokazują, że nazwa atrybutu lub nazwa elementu zaczynająca się od liczby otrzymuje błąd, ale rozpoczęcie od znaku podkreślenia ( _) lub litery jest w porządku.

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

Musisz więc poprzedzić nazwy kolumn znakiem, który jest poprawny jako znak początkowy dla atrybutu XML lub nazwy elementu.

Czy jesteś pewien, że „współpracuje” FOR XML AUTO? Z tego, co widzę, jest to po prostu automatyczna konwersja „nieprawidłowego” znaku na _x0032_:

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

Zwroty:

<tmp _x0032_016="2" />

— Solomon Rutzky
źródło