All geek questions in one place

Can a FOR XML AUTO clause produce unexpected results compared to regular row sets?

I am trying to understand some anomalies in my SQL results when returning with FOR XML AUTO. Oddly enough, I have different results in XML. In short, the number of Institutions should always be 603, but the XML data gives me 3 different totals, as shown in this set of fragments: XML returned for web application

The ORDER BY below simply goes through the selection to order the result. To debug this, I manually launched the stored procedure (commented out the FOR XML clause and created 3 "debug" tables filled with the passed SORT batch (each result set contains 603 rows if FOR XML AUTO is not used).

Each result set contains 603 rows if FOR XML AUTO is not used

I hope the problem is now clearly shown. Here is the stored procedure (the bottom logic, where the @Sort value defines ORDER BY, is most suitable, I think):

    ALTER Procedure [dbo].[FLAS2_List_Awards_V4]

-- EXECUTE FLAS2_List_Awards_V4 2,0
(
    @Sort   int = 1 
                    -- 1 = descending order on TotalAmount
                    -- 2 = descending order on TotalAwards
                    -- 3 = ascending order on Institution
    ,@Range int = 0
                    -- 0 = no filtering 
                    -- 1 = < $1 million
                    -- 2 = < $5 million
                    -- 3 = < $10 million
                    -- 4 = < $15 million
                    -- 5 = < $20 million
                    -- 6 = > $20 million
)
As

CREATE TABLE #TempMarkers
(
    ID          nchar(6) NOT NULL
,   Institution nvarchar(255) NOT NULL
,   Street      nvarchar(255) NULL
,   City        nvarchar(255) NULL
,   State       nvarchar(255) NULL
,   Zip         nvarchar(255) NULL
,   Latitude    decimal (28, 18) NULL
,   Longitude   decimal (28, 18) NULL
,   TotalAwards decimal (16, 0) NULL
,   TotalAmount decimal (16, 0) NULL
)
INSERT INTO #TempMarkers
(
    ID          
,   Institution 
,   Street      
,   City 
,   State       
,   Zip  
,   Latitude
,   Longitude       
,   TotalAwards 
,   TotalAmount 
)
SELECT DISTINCT
        C.ID 
      , C.InstitutionName
      , NULL AS street
      , NULL AS city
      , NULL AS state
      , NULL AS zip
      , NULL As Latitude
      , NULL As Longitude
      , NULL As TotalAwards
      , NULL As TotalAmount
  FROM dbo.FLAS2_Schools2 C
-- where c.ID in (135717,434584)
UPDATE #TempMarkers 
    SET      Street     = x.street
            ,City       = x.city
            ,State      = x.state
            ,Zip        = x.zip          
    FROM dbo.FLAS2_Schools2 X  
    WHERE X.ID = #TempMarkers.ID
    AND
    X.InstitutionName = #TempMarkers.Institution
UPDATE #TempMarkers 
    SET     Latitude    = Z.lat
            ,Longitude  = Z.Long
    FROM dbo.ZipCodesPreferred Z                         
    WHERE Z.ZipCode = #TempMarkers.Zip
CREATE TABLE #TempGrants
(
    ID          nchar(6) NOT NULL
,   TotalAwards decimal (16, 0) NULL
,   TotalAmount decimal (16, 0) NULL
)

EXECUTE dbo.FLAS2_List_Awards_V3_PrepAwards @Range

CREATE TABLE #FinalMarkers
(
    ID          nchar(6) NOT NULL
,   Institution nvarchar(255) NOT NULL
,   Street      nvarchar(255) NULL
,   City        nvarchar(255) NULL
,   State       nvarchar(255) NULL
,   Zip         nvarchar(255) NULL
,   Latitude    decimal (28, 18) NULL
,   Longitude   decimal (28, 18) NULL
,   TotalAwards decimal (16, 0) NULL
,   TotalAmount decimal (16, 0) NULL
)
INSERT INTO #FinalMarkers
(
    ID          
,   Institution 
,   Street      
,   City 
,   State       
,   Zip  
,   Latitude
,   Longitude       
,   TotalAwards 
,   TotalAmount 
)
select 
    t1.ID           
,   Institution 
,   Street      
,   City 
,   State       
,   Zip  
,   Latitude
,   Longitude       
,   t2.TotalAwards 
,   t2.TotalAmount 
FROM #TempMarkers t1
join #TempGrants t2 
on t1.id = t2.id

--SELECT * FROM #FinalMarkers

IF @Sort = 1 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
order by Marker.TotalAmount DESC, Marker.Institution, GrantAmount DESC
for xml auto, root('root')
END
IF @Sort = 2 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude       
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
order by Marker.TotalAwards DESC, Marker.Institution ,GrantAmount DESC
for xml auto, root('root')
END
IF @Sort = 3 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude       
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
order by Marker.Institution ,Marker.Street, GrantAmount DESC
for xml auto, root('root')
END

By the way, each of the "DEBUG" tables had exactly 1,117 rows, regardless of how it was ordered at creation time, using the INTO clause.

Finally, here is a snippet of what the web application is going through (counting the “Tokens”): enter image description here

+4
source share
2 answers

Well, it was a difficult problem for me (and why I put the generosity), but the help I received did not help much.

. - GROUP BY ORDER BY, Marker.Street(. ). BTW, FOR XML AUTO .

    IF @Sort = 2 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude       
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
GROUP BY Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber 
        ,Award.TotalObligatedAmount        
order BY Marker.TotalAwards DESC
        ,Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,GrantAmount DESC
for xml auto, root('root')
END

GROUP BY @Sort, OP .

0

, ID # 140652, . - . , 603 . . XML Auto . , , < 603. "ORDER BY" ... , . " XML Auto msdn, , , AUTO . xml, . https://msdn.microsoft.com/en-us/library/ms188273.aspx

-1

Source: https://habr.com/ru/post/1669423/

More articles:


All Articles