How to return large amount of data in the XML format

[Pavel]

I have SQL 2000 and need to retrieve fairly large amout of data (~
50.000 characters) in XML format and then insert it into the field of
the text type.
As 'FOR XML' can't be used with either local variables, INSERT INTO or
SELECT INTO this makes "XML support" quite useless in many aspects.

Can anyone please help me in solving this.
Thanks a lot for your help and time.

Pavel

[Erland Sommarskog]

Pavel (p.*********@ausbulk.com.au) writes:

I have SQL 2000 and need to retrieve fairly large amout of data (~
50.000 characters) in XML format and then insert it into the field of
the text type.
As 'FOR XML' can't be used with either local variables, INSERT INTO or
SELECT INTO this makes "XML support" quite useless in many aspects.

You can try:

INSERT tbl (xmlcol)
SELECT * FROM OPENQUERY(LOCALSVR, 'SELECT .... FOR XML')

Where LOCALSVR has been created as

EXEC sp_addlinkedserver
@server = 'LOCALVR',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'LocalServer'

That is, you use the deprecated OLE DB over ODBC provider. This works
so far that you get XML back. However, you may find that the text
has been broken into many rows. (If you would use SQLOLEDB, the real
SQL Server provider, you get a blob back.)

If this does not work out, you will have a find a client to pick up the
XML and send it back.

In SQL 2005, the XML support is considerably enhanced, and you should
be able to do this without weird workarounds.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp