FOR XML EXPLICIT Output Reverses Itself

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOR XML EXPLICIT Output Reverses Itself

Author

Topic

samloomis
Starting Member

1 Post

Posted - 2014-09-16 : 12:20:11

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
1 as Tag,
NULL as Parent,
f_docnumber as [INDEX name=!1!"F_DOCNUMBER" value],
f_docclassnumber as [INDEX name=!2!"F_DOCCLASSNUMBER" value],
f_entrydate as [INDEX name=!3!"F_ENTRYDATE" value]
FROM TempIndexTable where f_docnumber >= @StartDocNum and f_docnumber <= @StopDocNum
UNION ALL
SELECT
2 as Tag,
NULL as Parent,
f_docnumber,
f_docclassnumber,
f_entrydate
FROM TempIndexTable where f_docnumber >= @StartDocNum and f_docnumber <= @StopDocNum
UNION ALL
SELECT
3 as Tag,
NULL as 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" />
Preview #1613925

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources