Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 XML = Variable
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DBASlut
Yak Posting Veteran

71 Posts

Posted - 08/23/2005 :  16:44:01  Show Profile  Reply with Quote
is it possible to put the results of:
select *
from employee (nolock)
where emp_id ='4315719'
for xml auto,elements

into a variable that I can insert into a table/column?

azamsharp
Posting Yak Master

USA
201 Posts

Posted - 08/23/2005 :  17:40:16  Show Profile  Visit azamsharp's Homepage  Reply with Quote
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/3

Hope this Helps!

Mohammad Azam
www.azamsharp.net
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts

Posted - 08/23/2005 :  18:17:54  Show Profile  Reply with Quote
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.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/24/2005 :  17:14:13  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

DBASlut
Yak Posting Veteran

71 Posts

Posted - 08/25/2005 :  18:45:33  Show Profile  Reply with Quote
AjarnMark: I tried that but I'm getting:
Server: Msg 6819, Level 16, State 1, Line 5
The FOR XML clause is not allowed in a INSERT statement.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/25/2005 :  18:58:38  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000