FOR XML EXPLICIT Output Reverses Itself

• samloomis

  Newbie

  Points: 7

  More actions

  September 15, 2014 at 8:43 pm

  #148501

  I'm having some trouble with a query that returns its results with the FOR XML EXPLICIT option. 3 values are returned for each row in the table. For some reason, every other set of 3 values is reversed in order even though ORDER BY is set. Does anyone know why this is happening? Thanks.

  USE indexdb

  GO

  DECLARE @StartDocNum INT

  SET @StartDocNum = 391900

  DECLARE @StopDocNum INT

  SET @StopDocNum = 391950

  SELECT * INTO TempIndexTable FROM f_sw.doctaba WHERE f_docnumber >= @StartDocNum and f_docnumber <= @StopDocNum

  UPDATE TempIndexTable SET f_docnumber = '' WHERE f_docnumber IS NULL

  UPDATE TempIndexTable SET f_docclassnumber = 0 WHERE f_docclassnumber IS NULL

  UPDATE TempIndexTable SET f_entrydate = 0 WHERE f_entrydate IS NULL

  SELECT

  1as Tag,

  NULLas Parent,

  f_docnumberas[INDEX name=!1!"F_DOCNUMBER" value],

  f_docclassnumberas[INDEX name=!2!"F_DOCCLASSNUMBER" value],

  f_entrydateas[INDEX name=!3!"F_ENTRYDATE" value]

  FROM TempIndexTable where f_docnumber >= @StartDocNum and f_docnumber <= @StopDocNum

  UNION ALL

  SELECT

  2as Tag,

  NULLas Parent,

  f_docnumber,

  f_docclassnumber,

  f_entrydate

  FROM TempIndexTable where f_docnumber >= @StartDocNum and f_docnumber <= @StopDocNum

  UNION ALL

  SELECT

  3as Tag,

  NULLas Parent,

  f_docnumber,

  f_docclassnumber,

  f_entrydate

  FROM TempIndexTable where f_docnumber >= @StartDocNum and f_docnumber <= @StopDocNum

  ORDER BY [INDEX name=!1!"F_DOCNUMBER" value],[INDEX name=!2!"F_DOCCLASSNUMBER" value],[INDEX name=!3!"F_ENTRYDATE" value]

  FOR XML EXPLICIT;

  DROP TABLE TempIndexTable

  Results

  <INDEX name="F_DOCNUMBER" value="391902" />

  <INDEX name="F_DOCCLASSNUMBER" value="1" />

  <INDEX name="F_ENTRYDATE" value="16077" />

  <INDEX name="F_ENTRYDATE" value="16077" />

  <INDEX name="F_DOCCLASSNUMBER" value="1" />

  <INDEX name="F_DOCNUMBER" value="391906" />

  <INDEX name="F_DOCNUMBER" value="391916" />

  <INDEX name="F_DOCCLASSNUMBER" value="1" />

  <INDEX name="F_ENTRYDATE" value="16077" />

  <INDEX name="F_ENTRYDATE" value="16077" />

  <INDEX name="F_DOCCLASSNUMBER" value="1" />

  <INDEX name="F_DOCNUMBER" value="391920" />

  <INDEX name="F_DOCNUMBER" value="391929" />

  <INDEX name="F_DOCCLASSNUMBER" value="1" />

  <INDEX name="F_ENTRYDATE" value="16091" />

  <INDEX name="F_ENTRYDATE" value="16129" />

  <INDEX name="F_DOCCLASSNUMBER" value="2" />

  <INDEX name="F_DOCNUMBER" value="391935" />