Problem with SELECT...FOR XML in SQL 2000

Hi,
I have a problem with a SELECT..FOR XML Statement. I would like to do
something like:
DECLARE @.DeletedXml VARCHAR(8000)
SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id = @.Id
FOR XML AUTO, ELEMENTS)
But I get this error when I try to run: Incorrect syntax near 'XML. I have
then consulted the manual, and I can see to my big surprise that this is not
supported. Is there any way I can solve this? I want to save the @.DeletedXml
variable in a table.
Thanks
HenrikYou'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
Server 2000, you'll have to save it as native SQL datatypes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a problem with a SELECT..FOR XML Statement. I would like to do
> something like:
> DECLARE @.DeletedXml VARCHAR(8000)
> SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id =
> @.Id FOR XML AUTO, ELEMENTS)
> But I get this error when I try to run: Incorrect syntax near 'XML. I have
> then consulted the manual, and I can see to my big surprise that this is
> not supported. Is there any way I can solve this? I want to save the
> @.DeletedXml variable in a table.
> Thanks
> Henrik
>|||it's a bit messy, but you can
1. Put the FOR XML command as a stoerd proc
2. In .NET, execute the stored proc w/ the SqlXmlCommand object
3. Store the results of the stored proc in a stream -- Dim strm As
IO.Stream = xmlCommand.ExecuteStream()
4. Write the plain-text contents of the stream to the Database|||Hi Tom,
How can I save it as native SQL types?
Thanks Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> You'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
> Server 2000, you'll have to save it as native SQL datatypes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
>|||Thank you very much for your reply. You are right this is a bit messy :-)
I unfortunately can't do it in a stored procedure because I am using the
deleted and inserted tables.
And I would like if possible to do it all in SQL.
<scottstein@.gmail.com> wrote in message
news:1134406828.872432.69030@.g14g2000cwa.googlegroups.com...
> it's a bit messy, but you can
> 1. Put the FOR XML command as a stoerd proc
> 2. In .NET, execute the stored proc w/ the SqlXmlCommand object
> 3. Store the results of the stored proc in a stream -- Dim strm As
> IO.Stream = xmlCommand.ExecuteStream()
> 4. Write the plain-text contents of the stream to the Database
>|||You'll have to save the columns of the inserted/deleted tables to the
columns of another table or you can concatenate the values into a string and
save that - as long as the total string length is <= 8000 bytes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> How can I save it as native SQL types?
> Thanks Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>|||Hi Tom,
Again thanks.
Ok, that is of course an idea, to skip the FOR XML attribute and generate
the XML myself. I guess that is what you mean? The 8k barrier is no problem.
Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
> You'll have to save the columns of the inserted/deleted tables to the
> columns of another table or you can concatenate the values into a string
> and save that - as long as the total string length is <= 8000 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Yeah. basically, do an INSERT SELECT * FROM deleted. When you go to SQL
2005, you can then store it as XML.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%236J6x$1$FHA.264@.tk2msftngp13.phx.gbl...
> Hi Tom,
> Again thanks.
> Ok, that is of course an idea, to skip the FOR XML attribute and generate
> the XML myself. I guess that is what you mean? The 8k barrier is no
> problem.
> Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
>