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 |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-12-21 : 11:11:31
|
| I have a view that pulls nearly 60 fields from various tables (it's horrible but they are all needed honest guv - or so everyone is telling me). I have some test data which returns a single record to this view and I need to output it in XML. I don't care what the XML looks like or which XML mode it is in, I just want some form of XML. My problem is that whatever variant of FOR XML I use in Query Analyser, when I try to save it or when I try to output to a file it gets truncated. I set the column width to 8192 in QA which is the max it would accept and this didn't appear to help much. I'm sure XML is a good idea but I don't want to have to learn everything about it by tomorrow!!Anyone have any ideas? Many thankssteveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-12-21 : 12:02:22
|
| You can always assemble the XML yourself in a text or ntext column of a table variable, then select it out at the end. It's ugly, but it would work.Cheers-b |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-21 : 12:44:51
|
| If you do this in, say, ASP you have to set up an ADOStream to gather the data, and output-from. Its a fairly messy piece of code, compared to straight-ADO recordsets, if you aren't familiar with it.Note that there is no way [that I know of!!] to do this in a mock-up environment in QA(I posted some ASP code to do this deed a while back - shout if you can't find it and I'll have a trawl)Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-21 : 13:08:39
|
| If you can write a small ASP 3.0 or VB 6 app, you can get the data into a ADODB.RecordSet and then call rs.save "c:\blah.xml", 1Or do something similar with .Net and save the data into a Dataset and do a ds.WriteXML("C:\blah.xml")Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-12-22 : 03:03:04
|
| OK many thanks for the info guys. At the moment I only need to supply this as a sample dataset for a new interface for our system that's being developed elsewhere. I think I'm going to have to leave this to their developers - I only promised to provide either a recordset or the standard XML output from SQL Server 2000steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
|
|
|
XXXXML