FOR XML return as a Scalar

When returning a result as XML using FOR XML SQL Server 2005 is returning
serveral rows when the result is greater than 2036 in length. Each row is
breaking on this length. I would like FOR XML to return all the xml in a
singe row single column so that I can use a select scalar for the results.
Currently I have to use a data reader and a string builder. I'm hoping that
there is a way to control the size if the output for FOR XML so that I can d
o
a scalar read of any result.
Thanks,
TylerHi Tyler,
Please post your statement for us to take a look at.
Thanks,
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tyler Carver" <Tyler Carver@.discussions.microsoft.com> wrote in message
news:DE8A3709-2E31-49F6-B30C-C93B4B1195E0@.microsoft.com...
> When returning a result as XML using FOR XML SQL Server 2005 is returning
> serveral rows when the result is greater than 2036 in length. Each row is
> breaking on this length. I would like FOR XML to return all the xml in a
> singe row single column so that I can use a select scalar for the results.
> Currently I have to use a data reader and a string builder. I'm hoping
> that
> there is a way to control the size if the output for FOR XML so that I can
> do
> a scalar read of any result.
> Thanks,
> Tyler|||In SQL 2005 you can use the TYPE directive to get a scalar XML value like:
SELECT * FROM tbl FOR XML AUTO, TYPE
In SQL 2000, you cannot do this directly in Query Analyzer. FOR XML returns
an XML stream which can be retreived as single string only if you use an API
which supports a stream interface. Since Query Analyer uses ODBC, the values
will be munged to 2032 characters per row.
One alternative is to use XML EXPLICIT with the edge table ( need to know
the resultset upfront ). Another is to extract the data externally ( to an
app or flat file ) and stitch them back together to form single XML
document.
Anith|||"Tony Rogerson" wrote:
> Please post your statement for us to take a look at.
Here is a SQL statement that returns a large XML result:
SELECT *
FROM Categories
FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE
This returns serveral rows from the database that need to be concatenated.
I want to change the statement so that only one row one column is returned.
Tyler|||For test...
select name as [text()]
from sys.objects
for xml path( '' ), root( 'sysobjects' ), type
So yours would be...
SELECT *
FROM Categories
FOR XML PATH(''), ROOT('ArrayOfCategory'), TYPE
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tyler Carver" <TylerCarver@.discussions.microsoft.com> wrote in message
news:E46BBA07-4B47-4C49-B5B6-BC4D69F66E65@.microsoft.com...
> "Tony Rogerson" wrote:
> Here is a SQL statement that returns a large XML result:
> SELECT *
> FROM Categories
> FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE
> This returns serveral rows from the database that need to be concatenated.
> I want to change the statement so that only one row one column is
> returned.
> Tyler|||Are you using SSMS? Can you post the results of:
DECLARE @.xml AS XML
SET @.xml = ( SELECT *
FROM Categories
FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE )
SELECT DATALENGTH( @.xml )
Are you getting an error?
Anith|||Sorry, you'll need a delimiter as well...
select name + ',' as [text()]
from sys.objects
for xml path( '' ), root( 'sysobjects' ), type
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u3ACF6b%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> For test...
> select name as [text()]
> from sys.objects
> for xml path( '' ), root( 'sysobjects' ), type
> So yours would be...
> SELECT *
> FROM Categories
> FOR XML PATH(''), ROOT('ArrayOfCategory'), TYPE
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Tyler Carver" <TylerCarver@.discussions.microsoft.com> wrote in message
> news:E46BBA07-4B47-4C49-B5B6-BC4D69F66E65@.microsoft.com...
>|||"Anith Sen" wrote:
> In SQL 2005 you can use the TYPE directive to get a scalar XML value like:
Thanks Anith, this is exactly what I was looking for. We had several sprocs
without the Type directive and the example I had happened to have it and was
therefore unknown to us, working.
Thanks, again.
Tyler