FOR XML EXPLICIT - Insert XML Data into a different Table

Greetings,
How do I insert the XML document returned from the usage of FOR XML Explicit
into a different table within a Stored Procedure.
I have been able to use the FOR XML Explicit to generate the XML document
but I need to insert it into a different table as a Text value.
TIA,
KOYIf you are using SQL Server 2005, you can just insert it. In SQL Server
2000, FOR XML results have to be returned to the client-side. So you would
have to write an OLEDB/ADO program that returns the data and then reinserts
it. If you need to drive it from the server, you can use the sp_OA stored
procs.
But the best thing would be to get the April CTP of SQL Server 2005 if
running Beta software is an option...
Best regards
Michael
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:8693AAA4-B0BE-4926-A179-2039BC811F1C@.microsoft.com...
> Greetings,
> How do I insert the XML document returned from the usage of FOR XML
> Explicit
> into a different table within a Stored Procedure.
> I have been able to use the FOR XML Explicit to generate the XML document
> but I need to insert it into a different table as a Text value.
> TIA,
> KOY

FOR XML EXPLICIT - Insert XML Data into a different Table

Greetings,
How do I insert the XML document returned from the usage of FOR XML Explicit
into a different table within a Stored Procedure.
I have been able to use the FOR XML Explicit to generate the XML document
but I need to insert it into a different table as a Text value.
TIA,
KOY
If you are using SQL Server 2005, you can just insert it. In SQL Server
2000, FOR XML results have to be returned to the client-side. So you would
have to write an OLEDB/ADO program that returns the data and then reinserts
it. If you need to drive it from the server, you can use the sp_OA stored
procs.
But the best thing would be to get the April CTP of SQL Server 2005 if
running Beta software is an option...
Best regards
Michael
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:8693AAA4-B0BE-4926-A179-2039BC811F1C@.microsoft.com...
> Greetings,
> How do I insert the XML document returned from the usage of FOR XML
> Explicit
> into a different table within a Stored Procedure.
> I have been able to use the FOR XML Explicit to generate the XML document
> but I need to insert it into a different table as a Text value.
> TIA,
> KOY

FOR XML Explicit

Greetings,
How do I insert the XML document returned from the usage of FOR XML Explicit
into a different table within a Stored Procedure.
I have been able to use the FOR XML Explicit to generate the XML document
but I need to insert it into a different table as a Text value.
TIA,
KOYI don't think you can do this directly with T-SQL in SQL Server 2000. You
will probably need an application to do this.
I thought perhaps you could create a temp table with a single ntext field
and then do INSERT #temp SELECT ... FOR XML EXPLICIT, but the INSERT
statement doesn't allow you to use the FOR XML clause.
If your system can handle the transfer to the TEXT field being done in
batch-mode, rather than immediately, you could have an app that was launched
by SQL Agent that read the XML in and then inserted the text into a text
field; cheesy but do-able.
Mike|||Unfortunately, you will have to extract the data out and then import back
in. Some archive to get you started.
http://groups.google.co.uk/groups?q...ver+oj+textcopy
-oj
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:14FF705A-EC18-4FC0-ABFE-18041AC132B4@.microsoft.com...
> Greetings,
> How do I insert the XML document returned from the usage of FOR XML
> Explicit
> into a different table within a Stored Procedure.
> I have been able to use the FOR XML Explicit to generate the XML document
> but I need to insert it into a different table as a Text value.
> TIA,
> KOY