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 |
|
nitinag
Starting Member
20 Posts |
 Posted - 2003-07-21 : 21:08:09
|
| I am storing Large XML data in a text field(users are uploading this data on web) in temp table, Now a job will run to process that XML into the correct tables and then delete the row. I need to get the Data in a variable as a input to sp_xml_preparedocument which will give me a handle to query using OPENXML EXEC sp_xml_preparedocument @XMLDoc output, ???(xml datavariable shd be here)Problem is that I cannot declare Text field locally to get the Data , not sure how to do it , I thought of doing it with dynamc sql but doesn't work Any ideas how can I do it ?thanks alot |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-21 : 21:14:45
|
| You can't. The only way to pass data as a text variable is into a stored procedure, and it MUST be passed as a literal value. You cannot refer to a text column and you can't manipulate a text variable at all.Ken Henderson'sThe Guru's Guide to SQL Server Stored Procedures, XML and HTML has some code in it that can handle large XML data, and is an excellent book in other areas as well. You can pick it at the SQL Team Bookstore.Edited by - robvolk on 07/21/2003 21:15:05 |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-07-21 : 21:20:45
|
| I'm guessing you can't use a subquery to get that text field either eh Rob?Text fields are very strange.Here's a W.A.G:One solution would be to store the XML files on disk, store the path to the XML files in the database. You can then use a job or external program to run through the files and process them as needed.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-21 : 21:52:10
|
| Nope, no subquery. Even Ken admits that it's pretty dumb that SQL Server can't load an XML file directly (without using the XML bulk load utility that is) God knows I tried a ton of possibilities before giving up. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 09:50:27
|
| What about Yukon?Brett8-) |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-07-22 : 10:11:17
|
| Yukon can surely do it... Yukon can do everything =) |
|
|
|
nitinag
Starting Member
20 Posts |
Posted - 2003-07-22 : 12:58:03
|
| I guess then only way is to call XML data in a outside Job and then resend the Data to stored proc, that will make the data go around twice but I am left with no option.I do not want to store the XML files on disk and do cean up later , users are uploading Excel files and I am converting that to XML and then storing into DB(don't want to process at same time because the process is expensive). This will make the data go around for a total of 3 times :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-22 : 13:06:00
|
| Why are you even using XML? Just convert the Excel files to CSV or tab-delimited format and use BULK INSERT or bcp to import the data. No point in adding XML bloat when you don't need it. Not to mention you avoid the limitations of text variables. |
|
|
|
|
|
|
|
|
need to declare Text field local variable , Help!