| Author |
Topic |
|
DBA in the making
Aged Yak Warrior
638 Posts |
 Posted - 2010-10-19 : 07:04:59
|
Hi all, I've written a query that looks like this:SELECT Col1, Col2, Col3FROM TableNameFOR XML RAW This query returns the desired xml text. Question, how do I insert the results of this text into a text column in a table?------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
sql-programmers
Posting Yak Master
190 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-22 : 07:15:39
|
Why do you want to insert an xml document into a text-column?
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-10-22 : 09:39:15
|
quote:
Originally posted by Peso
Why do you want to insert an xml document into a text-column?
N 56°04'39.26"E 12°55'05.63"
Well, it's a work around for this issue:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151716If I can't write the data out to an excel sheet from within a transaction, the next best thing is to temporarily store it in the db somewhere and then build a process that rights it out to a sheet once the transaction is finished. As I plan to use this for a variety of processes, each with unique datasets to be exported, it's a choice of having a tables for each process, tailored to the specific requirements of such, or converting it to XML, which would allow me to store a variety of different results sets in the same table. If you can suggest another method of achieving the same, I'm all ears.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-10-22 : 09:40:04
|
quote:
Originally posted by Peso
Why do you want to insert an xml document into a text-column?
N 56°04'39.26"E 12°55'05.63"
Thanx for that, I'll take a look at it (when time permits, of course :) ).EDIT: Unfortunately, the XML datatype does not exist in SQL Server 2000. :(------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-23 : 03:46:47
|
quote:
Originally posted by DBA in the making
Unfortunately, the XML datatype does not exist in SQL Server 2000. :(
That's my point.All support has ended for SQL Server 2000. If something happens to your server Microsoft won't help you fix it.Consider an upgrade to SQL Server 2008R2, which has "native" support for XML documents AND is supported by Microsoft.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-10-24 : 01:27:22
|
it's been many years but as I recall in 2000 you can't do anything with xml but return it to a client. there's no way to insert the output of the various FOR XML results to tables or consume them from other procs.
elsasoft.org |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-10-25 : 03:48:02
|
quote:
Originally posted by Peso
quote:
Originally posted by DBA in the making
Unfortunately, the XML datatype does not exist in SQL Server 2000. :(
That's my point.All support has ended for SQL Server 2000. If something happens to your server Microsoft won't help you fix it.Consider an upgrade to SQL Server 2008R2, which has "native" support for XML documents AND is supported by Microsoft.
N 56°04'39.26"E 12°55'05.63"
We are looking at upgrading the server at some stage in the future when time permits, but for the moment I have to make do with what I have. I haven't been in this role for very long, and have identified a number of issues with the system I have inherited. That's probably one of the smaller issues.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-10-25 : 03:49:09
|
quote:
Originally posted by jezemine
it's been many years but as I recall in 2000 you can't do anything with xml but return it to a client. there's no way to insert the output of the various FOR XML results to tables or consume them from other procs.
I think you're right. I've had a good look around the help files and the internet, and haven't seen a single example of what I'm after. Looks like I'll have to do it the hard way for now.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
sathiesh2005
Yak Posting Veteran
85 Posts |
Posted - 2010-11-09 : 04:17:20
|
| Hi,I have an xml file generated with FOR XML AUTO,ELEMENTS statement in SQL server 2000. I want to read this xml file and convert it back to a table. Is it possible?@Sathiesh |
|
|
|
|
For XML --> table