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 test a stored procedure ? how ?

Ok ,
I'm new in stored procedures, I created one, so I want to test it without
going and coding a program in C# or VB.NET, is it any way to run it where it
will ask my parameters and get the result using the SQL Enterprise Manager ?
ThanksEasiest in Query Analyzer:
EXEC usp_proc_name
@.param1 = 123,
@.param2 = 'ABC'
David Portas
SQL Server MVP
--|||In addition to executing within Query Analyzer, there is also a feature to
run the procedure step by step in debug mode. Read up on "Transact-SQL
Debugger" in Books Online or MSDN.
"Carlos" <cp@.swa.com> wrote in message
news:%23$UsSnCyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Ok ,
> I'm new in stored procedures, I created one, so I want to test it without
> going and coding a program in C# or VB.NET, is it any way to run it where
> it will ask my parameters and get the result using the SQL Enterprise
> Manager ?
> Thanks
>|||Ok I used the Query analyzer now where do I see my returned value ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
> Easiest in Query Analyzer:
> EXEC usp_proc_name
> @.param1 = 123,
> @.param2 = 'ABC'
> --
> David Portas
> SQL Server MVP
> --
>|||Pass a variable and specify OUTPUT. A nice feature of Query Analyzer is
that it will script the EXEC statement for you. Press F8 to open the
Object Browser, right-click on your proc, click "Script Object to new
window as" and then "EXECUTE". Result:
DECLARE @.RC int
DECLARE @.param1 int
DECLARE @.param2 varchar(10)
-- Set parameter values
EXEC @.RC = [JUNK].[dbo].[usp_proc_name] @.param1 OUTPUT , @.param2

>From the same point in the Object Browser you can also access the
debugger as JT suggested.
David Portas
SQL Server MVP
--|||declare @.x as int
If you want to print the result of an output parameter:
exec myproc @.a=1, @.b=2, @.c = @.x output
print @.x
If you want to print the result of a return value:
EXECUTE @.x = myproc @.a=1, @.b=2
print @.x
"Carlos" <cp@.swa.com> wrote in message
news:ufE8JxCyFHA.448@.TK2MSFTNGP11.phx.gbl...
> Ok I used the Query analyzer now where do I see my returned value ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
>|||Can you post a sample of what you are trying to test? It would be much
easier for us to determine what you exactly want to test.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Carlos" <cp@.swa.com> wrote in message
news:ufE8JxCyFHA.448@.TK2MSFTNGP11.phx.gbl...
> Ok I used the Query analyzer now where do I see my returned value ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
>|||Hi Carlos,
Here are some articles for your reference about how to debug SQL Server
stored procedures and the necessary configuration settings and steps for
each approach
HOW TO: Debug Stored Procedures in Visual Studio .NET
http://support.microsoft.com/kb/316549/EN-US/
Troubleshooting tips for T-SQL Debugger in Visual Studio .NET
http://support.microsoft.com/kb/817178
Debugging Stored Procedures
http://www.windowsitpro.com/Article...6356/16356.html
You may also search keywords debug SQL stored procedure in google.com for
more
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

How to store GPS Coordinates in database, Which Data Type?

I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.

Like LAT ( N38 28.025' ) and LONG (W105 52.098' )

The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.

I hope I have explained this right. The numbers in BOLD are what I need to maintain.

Thanks for any help in advance.

Steve

Hi Steve,

are you using SQL Server 2005 ? YOu could create your own data type for that ! I am not quite sure if since now anybody did already one for that reason but it would be worth a try searching in google for CLR +data type +sql server 2005 (and then the appropiate buzz words you probably know for your problem, like special names for the coordinates etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You could look at a SQL CLR User defined data type.The other option is to store as integers (or numerics) and then have a function that converts from the raw numeric value to the format above.

With a UDT you implement a parse method that allows you to interpret text into a value that you can store.

Be careful with UDTs though as changing them once in place is very difficult, as it is in use and so you can't just upgrade the base assembly.