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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 XXXXML

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 thanks


steve

To 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
Go to Top of Page

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
Go to Top of Page

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", 1

Or 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>
Go to Top of Page

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 2000

steve

To alcohol ! The cause of - and solution to - all of life's problems
Go to Top of Page
   

- Advertisement -