When I have to output a set of XML data without using the SQL Server
XML-related commands, I have found that this will only work if all of
the fields of the output are concatenated. And, when I say all, I mean
it:

SELECT '<item>
<ID>' + Cast(ID as varchar(10)) + '</id>
<name>' + EmpName + '</name>
<gender>' + CASE WHEN male = 1 THEN 'M' ELSE 'F' END + '</gender>
<birthdate>' + Convert(varchar(10), BirthDate, 101) + '</birthdate>
</item>' As OutputData
FROM MyTable
WHERE Month(BirthDate) = Month(getdate())

Anything less will generate badly spaced-out XML.


==+==+==+==+==+==+==+==+==+==+==+==+
Brendt W. Hess
Database Administrator, Motosport, Inc.
brendt.[Email address protected]
==+==+==+==+==+==+==+==+==+==+==+==+

-----Original Message-----
From: Walter Dorciak
[mailto:mssqldba-ezmlmshield-x56516281.[Email address protected]
Sent: Tuesday, May 29, 2007 9:21 AM
To: LazyDBA Discussion
Subject: Osql output - trim characters.

Hi all,

I am creating a simple XML file using osql command (SQL 2000). Osql
calls stored procedure that SELECTs output.

Output is a combination of strings, fields and variables. The problem is
the ouptut includes full length of a variable or field, I am unable to
trim padding spaces.

For example.

Declare @text varchar(100)

Set @text = 'abc'

Select '<element>'
,ltrim(rtrim(@text))
,'</element>'

Even though I try to trim @text, it will be padded with 97 spaces. Is
there an osql switch or any other way that would eliminate trailing
spaces?


Thanks,
___________________

Walter Dorciak
MIS Manager
AGC Electronic Materials
www.agcem.com



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page