Why XML Output is Truncated

[Mo]

Hi,
I am trying to generate an XML output form the database. I am
executing

select * from Request_vw for xml auto

The output data is being truncated. is there a limit on the output
result of an XML query?

Thanks

[Hugo Kornelis]

On Mon, 30 Jul 2007 07:51:41 -0700, Mo wrote:

>Hi,
I am trying to generate an XML output form the database. I am
executing

select * from Request_vw for xml auto

The output data is being truncated. is there a limit on the output
result of an XML query?

Thanks

Hi Mo,

In SQL Server Management Studio: Tools / Options / Query Results / SQL
Server / Results to Text / Maximum number of characters displayed in
each column

In Query Analyzer: Tools / Options / Results / Maximum characters per
column

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Mo]

Thank you for the great pointer. Here is the problem. I am calling a
function to retrieve the data in XML format. The problem is that it
returns only nvarchar(8000) which is smaller than what I need. I think
that is why the returning data is being truncated. my function is as
follows. Any ideas on how to return larger XML string is greatly
appreciated.
CREATE FUNCTION GetXMLRequests()
RETURNS nvarchar(max)
AS
BEGIN
-- Declare the return variable here
RETURN (select * from Request_vw for xml auto)

END
GO

[Hugo Kornelis]

On Mon, 30 Jul 2007 18:37:42 -0700, Mo wrote:

>Thank you for the great pointer. Here is the problem. I am calling a
function to retrieve the data in XML format. The problem is that it
returns only nvarchar(8000) which is smaller than what I need. I think
that is why the returning data is being truncated. my function is as
follows. Any ideas on how to return larger XML string is greatly
appreciated.
CREATE FUNCTION GetXMLRequests()
RETURNS nvarchar(max)
AS
BEGIN
-- Declare the return variable here
RETURN (select * from Request_vw for xml auto)

END
GO

Hi Mo,

Any reason why you want to return nvarchar(max) rather than use the
dedicated xml datatype?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis