Just so you know, my message about concatenating everything was
reformatted by (apparently) Outlook. You can simplify the view while
avoiding the multiple SET and SELECT statements by writing the SELECT in
a multiple-line format (note: I am hitting ENTER twice between each line
because hitting enter once was reformatted into a single line):

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.

This will generate XML similar to:

<item>
<ID>1</ID>
<name>Brendt Hess</name>
<gender>M</gender>
<birthdate>19900125</birthdate>
</item>


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

-----Original Message-----
From: Walter Dorciak
[mailto:mssqldba-ezmlmshield-x71447444.[Email address protected]
Sent: Tuesday, May 29, 2007 3:00 PM
To: LazyDBA Discussion
Subject: RE: Osql output - trim characters.

Combination of concatenate everything into one line, and trim fields
using LEN function is working, although it could get complicated for
complex output. Another suggestion was to look into BCP which should not
pad character fields with spaces.


declare @Line varchar(2000)
Declare @Length int


set @Line = '<QALITY02><IDOC BEGIN="1">...<DATUM>'
set @Line = @Line + (CONVERT(char(8), getdate(), 112)) + '</DATUM>...'

-- determine number of actual chars to output
set @Length = len((SELECT to return my field value))
set @Line = @Line + left((SELECT to return my field value), @Length)

set @Line = @Line + '<more XML stuff>'


SELECT @Line


Thanks,
___________________

Walter Dorciak

-----Original Message-----
From: Gilson Corey
[mailto:mssqldba-ezmlmshield-x30852366.[Email address protected]
Sent: Tuesday, May 29, 2007 2:07 PM
To: LazyDBA Discussion
Subject: RE: Osql output - trim characters.

This might not help much... I believe OSQL is putting a space in there
to pad out the rest of the varchar field. The only time I've had to
deal with this issue it was with a very small data set, so this solution
might not be scalable enough for you.

I used dynamic sql to build a list of queries that pulled the data from
the table one row at a time. The dynamic part was necessary to cast the
varchar field as a char with its exact length.

The idea of it was this:


declare @string varchar( 100), @Command varchar( 1000) select @string =
'abcdef'

select @Command = 'select ''<tag>'' + convert( char( ' + convert(
varchar( 5), ( len( @string))) + '), ''' + @string + ''') + ''</tag>'''
exec( @command)

What this leaves you with is a query that returns your varchar field
with a precise length, and nothing for OSQL to fill in.


-----Original Message-----
From: Walter Dorciak
[mailto:mssqldba-ezmlmshield-x56516281.[Email address protected]
Sent: Tuesday, May 29, 2007 11: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



CONFIDENTIAL AND PROPRIETARY
The contents of this communication are confidential and proprietary to
ShopLocal, LLC and may not be reproduced, published or disclosed to
others without express authorization of ShopLocal, LLC

Copyright (c) 2007 ShopLocal, LLC May not be used, copied or distributed
without permission. All Rights Reserved.



---------------------------------------------------------------------
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



---------------------------------------------------------------------
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