Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBASlut
Yak Posting Veteran
71 Posts |
 Posted - 2005-08-23 : 16:44:01
|
| is it possible to put the results of:select *from employee (nolock)where emp_id ='4315719'for xml auto,elementsinto a variable that I can insert into a table/column? |
|
|
azamsharp
Posting Yak Master
201 Posts |
Posted - 2005-08-23 : 17:40:16
|
| I dont know about assigning the xml to the variable but you can access on the user interface layer whatever you select in the query. Take a look at this example written in ASP http://www.sitepoint.com/article/data-as-xml-sql-server/3Hope this Helps!Mohammad Azam www.azamsharp.net |
 |
|
|
DBASlut
Yak Posting Veteran
71 Posts |
Posted - 2005-08-23 : 18:17:54
|
| thanks for that info, what I'm trying to do is , 1) get record from a table in XML format 2) save that XML data into another table as XML. This has to be done all in T-SQL. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-24 : 17:14:13
|
| SQL 2005 has an XML data type, at least for fields, I don't recall off-hand whether you'll be able to declare a variable as an XML type. Otherwise, you might try inserting it directly into a large varchar or text field without using the variable. Something like:INSERT INTO myNewTable (myBigVarcharField)select *from employee (nolock)where emp_id ='4315719'for xml auto,elements---------------------------EmeraldCityDomains.com |
|
|
|
DBASlut
Yak Posting Veteran
71 Posts |
Posted - 2005-08-25 : 18:45:33
|
| AjarnMark: I tried that but I'm getting:Server: Msg 6819, Level 16, State 1, Line 5The FOR XML clause is not allowed in a INSERT statement. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-25 : 18:58:38
|
| I thought that might happen. I'd guess you've basically got two options then. Either get a beta version of 2005 and see if you can accomplish it, or output the results of your XML to something outside of SQL Server, such as a web page or text file and then stuff it back in to your destination field. But you said it has to be done all in T-SQL...WHY?Or there's always option 3 which is to give up or rewrite the requirements.---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
XML = Variable