How to trim XML's attribute values?

I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>
In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
sql

How to trim XML's attribute values?

I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>

How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:

> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like
:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>|||You can also check for available types on
2f3o.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
[vbcol=seagreen]
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
>|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> glsD
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/d..._ca-co_2f3o.asp[v
bcol=seagreen]
> thanks and regards
> Chandra
> "Chandra" wrote:
>
like:[vbcol=seagreen]
or[vbcol=seagreen]|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:[vbcol=seagreen]
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> glsD
> o_2f3o.asp" target="_blank">http://msdn.microsoft.com/library/d... />
o_2f3o.asp

How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?
Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:

> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>
|||You can also check for available types on
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
[vbcol=seagreen]
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> glsD
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp[vbcol=seagreen]
> thanks and regards
> Chandra
> "Chandra" wrote:
like:[vbcol=seagreen]
or[vbcol=seagreen]
|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:[vbcol=seagreen]
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> glsD
> http://msdn.microsoft.com/library/de...ca-co_2f3o.asp

How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:
> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>|||You can also check for available types o
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
> >
> > I have a tabls of SQL 2000, about the data of student.
> > The data is migrate from another databse,
> > There is a field called birthday, it stores the data with the formate like:
> > 1994/11/03 AM 02:09:00
> >
> > When I bind the field to datagrid, how can I suppress the time part , or
> > how can I trim the time part form database?
> >
> >
> >|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> thanks and regards
> Chandra
> "Chandra" wrote:
> > Hi,
> > You can try as
> > select convert(varchar(10), getdate(), 102)
> >
> > thanks and regards
> > Chandra
> >
> >
> > "ad" wrote:
> >
> > >
> > > I have a tabls of SQL 2000, about the data of student.
> > > The data is migrate from another databse,
> > > There is a field called birthday, it stores the data with the formate
like:
> > > 1994/11/03 AM 02:09:00
> > >
> > > When I bind the field to datagrid, how can I suppress the time part ,
or
> > > how can I trim the time part form database?
> > >
> > >
> > >|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
>> BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
>> You can also check for available types on
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>> thanks and regards
>> Chandra
>> "Chandra" wrote:
>> Hi,
>> You can try as
>> select convert(varchar(10), getdate(), 102)
>> thanks and regards
>> Chandra
>>
>> "ad" wrote:
>>
>> I have a tabls of SQL 2000, about the data of student.
>> The data is migrate from another databse,
>> There is a field called birthday, it stores the data with the
>> formate like: 1994/11/03 AM 02:09:00
>> When I bind the field to datagrid, how can I suppress the time
>> part , or how can I trim the time part form database?

How to trim leading zeros from a varchar column?

My table has a column named [Liability] varchar datatype which has the data in the format

(

3535.00,

00393.99,

00Loan,

0.00,

.00

*.00

)

I want to trim the leading zeros so that the output should be(trim only the leading zeros)

(

3535.00,

393.99,

Loan,

0.00,

.00

*.00

)

Can someone show my the sql statement for this?

Thanks.

Code Snippet

--To ignore leading '0' and <SPACE> characters:

select substring(Liability,patindex('%[^0 ]%',Liability),8000)

from <MyTable>

--To ignore ONLY leading '0' characters:

select substring(Liability,patindex('%[^0]%',Liability),8000)

from <MyTable>

|||

rusag2,

I think that your suggested solution will also remove the leading zero from [ 0.00 ] which 'should' be retained.

Perhaps something like this would be closer to the desired output:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( '3535.00' )
INSERT INTO @.MyTable VALUES ( '00393.99' )
INSERT INTO @.MyTable VALUES ( '00Loan' )
INSERT INTO @.MyTable VALUES ( '0.00' )
INSERT INTO @.MyTable VALUES ( '00.00' )
INSERT INTO @.MyTable VALUES ( '.00' )
INSERT INTO @.MyTable VALUES ( '*.00' )

SELECT

CASE
WHEN isnumeric( MyValue ) = 1 THEN cast( cast( MyValue AS decimal(18,2)) AS varchar(20))
ELSE substring( MyValue, patindex('%[^0]%', MyValue ), 20 )
END
FROM @.MyTable

--

3535.00
393.99
Loan
0.00
0.00
0.00
*.00

|||

Thanks Arnie.

That worked!!!!!

sql

How to trim both side?

There are only LTRIM and RTRIM functions in T-SQL
But I want to trim both side.
How can I do?Use both :)
SELECT LTRIM(RTRIM(' this is a string with whitespace on both sides ')
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uyfpTXTfGHA.4828@.TK2MSFTNGP05.phx.gbl...
> There are only LTRIM and RTRIM functions in T-SQL
> But I want to trim both side.
> How can I do?
>

How to trim both side?

There are only LTRIM and RTRIM functions in T-SQL
But I want to trim both side.
How can I do?Use both
SELECT LTRIM(RTRIM(' this is a string with whitespace on both sides ')
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uyfpTXTfGHA.4828@.TK2MSFTNGP05.phx.gbl...
> There are only LTRIM and RTRIM functions in T-SQL
> But I want to trim both side.
> How can I do?
>

how to trim a field value in rss

can any one explain how to trim a value on bothside in rss

for eg:

'asd '

' asd'

i wanna this output 'asd'............

can i give this command

=Format(Fields!company.Value.Trim)

You can use Trim function like this:

Trim(Fields!company.Value)

This will trim both leading and trailing spaces.

Pls mark this post as answer if your problem is solved.

Shyam