MS SQL XML - Problem between SQL Server 2000 and 2005

Author

Problem between SQL Server 2000 and 2005

christian.renninger@gmail.com

2006-11-20, 7:21 pm

I have a problem with a stored procedure which compiles fine on SQL
Server 2005 with the DB Compatibility Level set to SQL Server 2000 and
the same procedure on SQL Server 2000.

I created an example below, I need to use the XML result in an other
Select statement, that is why I have it as an inline view.

This code work fine on SQL SERVER 2005 with the DB set as 2000 but it
doesn't work on SQL Server 2000. Any idea ?

DROP TABLE Toto
go
CREATE TABLE Toto (i INT IDENTITY, T VARCHAR(10))
go

INSERT INTO Toto VALUES ('a')
INSERT INTO Toto VALUES ('b')
INSERT INTO Toto VALUES ('c')

go

CREATE PROC TEXTXML
AS

SELECT (
SELECT 1 AS TAG
,NULL AS PARENT
,i & #91;TOTO!1!IDENTITY!
Element]
,t & #91;TOTO!1!VALUE!Ele
ment]
FROM Toto
ORDER BY TAG
FOR XML EXPLICIT
)

go

EXEC TEXTXML

Kent Tegels

2006-11-21, 12:21 am

Hello christian,

The difference boils down to how the two engines serialize tables to XML.
In 2000, the serialization is done post-query so the output of a FOR XML
query is never available to a subsequent step. In 2005 the engine, regardless
of compatibility mode, serializes in-query.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Sponsored Links