XML String - SQL 2000 vs 2005 Issue

[MKVAB]

SQL 2000 doesn't support FOR XML RAW with ELEMENTS.

I am reverse engineering an app and I need to be able to produce results as they would appear if I was doing this:

SELECT *
FROM Customers
FOR XML RAW('Customer'), ELEMENTS

But, my DB is a 2000 server, not a 2005.

Ideas on how to change this FOR XML statement to return an XML string that is formed the same way as it would if run in 2005 with "FOR XML RAW('Customer'), ELEMENTS"?

All ideas are GREATLY appreciated!
-MK

 

[bborissov]

What SQL Server server you use?
I don't ask about DB but on what SQL Server you attached it.
Could you attach that DB on SQL Server 2005?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MKVAB]

Thanks Borislav!

Unfortunetly, I don't have the flexibility to change the attributes in anyway.

I thought I figured out a way. By doing this:

SELECT *
FROM Customers
FOR XML AUTO, ELEMENTS

It worked fine independently, but when I run it in my script I get "Incorrect syntax near 'XML'." So, still messing around with it.

 

[MKVAB]

Well, I've been able to get the work around to return correctly. The above does do the same thing. It's just that my real statement is MUCH longer and my error was occuring when writting the XML string to a variable.

Apparently you can SET a variable to an XML string in 2005, but not so in 2000.

Sooooooo, what I REALLY need is a way to set the results of this SELECT to a variable or insert into a temp table:

CREATE TABLE #test (one int, two int, three int)

INSERT INTO #test VALUES (1,2,3)

SELECT one,two,three
FROM #test PROVISIONDOC
FOR xml auto, elements

The XML retuns correctly. But, I need to now enter that value into a table. I just need that XML string in a variable. Surely there's a way to do that in 2000 right?

Ideas?

THANKS!