How can I convert xml into table using SQL Server 2005?

How can I convert the xml as:
<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
..
..
..
</row>
into table
a b c d ... ... ...
--
1 2 3 4 ... ... ...ABC wrote:
> How can I convert the xml as:
> <row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> ...
> ...
> ...
> </row>
> into table
> a b c d ... ... ...
> --
> 1 2 3 4 ... ... ...
This is an example using the stored procedure sp_xml_preparedocument and
the rowset provider OPENXML:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
</row>';
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML(@.iDoc, '/row', 2)
WITH (a int, b int, c int, d int);
EXEC sp_xml_removedocument @.iDoc;
Another approach is to use the XQuery nodes function as follows:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
</row>';
SELECT T.col.value('a[1]', 'int') AS a,
T.col.value('b[1]', 'int') AS b,
T.col.value('c[1]', 'int') AS c,
T.col.value('d[1]', 'int') AS d
FROM @.x.nodes('/row') AS T(col);
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Thanks, but I have problem if the number of tag under the row node is
dynamic, it is hard apply this method.
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
> ABC wrote:
> This is an example using the stored procedure sp_xml_preparedocument and
> the rowset provider OPENXML:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> </row>';
> DECLARE @.iDoc int;
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
> SELECT *
> FROM OPENXML(@.iDoc, '/row', 2)
> WITH (a int, b int, c int, d int);
> EXEC sp_xml_removedocument @.iDoc;
>
> Another approach is to use the XQuery nodes function as follows:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> </row>';
> SELECT T.col.value('a[1]', 'int') AS a,
> T.col.value('b[1]', 'int') AS b,
> T.col.value('c[1]', 'int') AS c,
> T.col.value('d[1]', 'int') AS d
> FROM @.x.nodes('/row') AS T(col);
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||ABC wrote:
> but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
That is true, I am not sure how to solve that case.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||It can't be completely dymanic for two reasons...
The first is XML should conform to a fixed schema, and secondly,
you're trying to push data into a fixed table.
On Thu, 5 Jul 2007 09:43:12 +0800,
"ABC" <abc@.abc.com> wrote in message
news:OXjNdXqvHHA.4516@.TK2MSFTNGP06.phx.gbl

> Thanks, but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
>
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
>

How can I convert xml into table using SQL Server 2005?

How can I convert the xml as:
<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
...
...
...
</row>
into table
a b c d ... ... ...
1 2 3 4 ... ... ...
ABC wrote:
> How can I convert the xml as:
> <row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> ...
> ...
> ...
> </row>
> into table
> a b c d ... ... ...
> --
> 1 2 3 4 ... ... ...
This is an example using the stored procedure sp_xml_preparedocument and
the rowset provider OPENXML:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
</row>';
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML(@.iDoc, '/row', 2)
WITH (a int, b int, c int, d int);
EXEC sp_xml_removedocument @.iDoc;
Another approach is to use the XQuery nodes function as follows:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
</row>';
SELECT T.col.value('a[1]', 'int') AS a,
T.col.value('b[1]', 'int') AS b,
T.col.value('c[1]', 'int') AS c,
T.col.value('d[1]', 'int') AS d
FROM @.x.nodes('/row') AS T(col);
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Thanks, but I have problem if the number of tag under the row node is
dynamic, it is hard apply this method.
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
> ABC wrote:
> This is an example using the stored procedure sp_xml_preparedocument and
> the rowset provider OPENXML:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> </row>';
> DECLARE @.iDoc int;
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
> SELECT *
> FROM OPENXML(@.iDoc, '/row', 2)
> WITH (a int, b int, c int, d int);
> EXEC sp_xml_removedocument @.iDoc;
>
> Another approach is to use the XQuery nodes function as follows:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> </row>';
> SELECT T.col.value('a[1]', 'int') AS a,
> T.col.value('b[1]', 'int') AS b,
> T.col.value('c[1]', 'int') AS c,
> T.col.value('d[1]', 'int') AS d
> FROM @.x.nodes('/row') AS T(col);
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||ABC wrote:
> but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
That is true, I am not sure how to solve that case.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||It can't be completely dymanic for two reasons...
The first is XML should conform to a fixed schema, and secondly,
you're trying to push data into a fixed table.
On Thu, 5 Jul 2007 09:43:12 +0800,
"ABC" <abc@.abc.com> wrote in message
news:OXjNdXqvHHA.4516@.TK2MSFTNGP06.phx.gbl

> Thanks, but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
>
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
>
sql

How can i convert Pivot table

Hi,
how can I convert a pivot table in Access to SQL Server.
Access SQL example:
TRANSFORM Sum(AM.TOTAL) AS SommaOfTOTAL
SELECT AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
FROM AM
GROUP BY AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
PIVOT AM.YEAR;
Thanks!!!Hi
There are many posts on how to pivot and crosstab posted in these groups
such as
http://tinyurl.com/8tqfr
SQL Server 2005 has features that make this alot simpler
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
John
"claude81" wrote:

> Hi,
> how can I convert a pivot table in Access to SQL Server.
> Access SQL example:
> TRANSFORM Sum(AM.TOTAL) AS SommaOfTOTAL
> SELECT AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
> FROM AM
> GROUP BY AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
> PIVOT AM.YEAR;
> Thanks!!!

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

this is the duty of presentation layer. as such you should change the font in the FE or GUI not in the database and its not possilble and its not logcally correct also. And also please tell us why you want to change the font in DB?

Madhu

|||Do you mean change the font, change the encoding, or change the collation? The font isn't stored in the server, the encoding is set by the application, but the collation of a column can be changed for a specific language or ordering. You can find more information on SQL Server collations at:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

|||I mean encoding, before user use font VNI-Times (Vietnamese language ) and save to database, now, if we show it with Unicode, we can not read anything, so that I am finding solution to convert encoding to Unicode.I intend export to excel, and import with some option that can change font encoding (if have any ).

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

this is the duty of presentation layer. as such you should change the font in the FE or GUI not in the database and its not possilble and its not logcally correct also. And also please tell us why you want to change the font in DB?

Madhu

|||Do you mean change the font, change the encoding, or change the collation? The font isn't stored in the server, the encoding is set by the application, but the collation of a column can be changed for a specific language or ordering. You can find more information on SQL Server collations at:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

|||I mean encoding, before user use font VNI-Times (Vietnamese language ) and save to database, now, if we show it with Unicode, we can not read anything, so that I am finding solution to convert encoding to Unicode.I intend export to excel, and import with some option that can change font encoding (if have any ).

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

this is the duty of presentation layer. as such you should change the font in the FE or GUI not in the database and its not possilble and its not logcally correct also. And also please tell us why you want to change the font in DB?

Madhu

|||Do you mean change the font, change the encoding, or change the collation? The font isn't stored in the server, the encoding is set by the application, but the collation of a column can be changed for a specific language or ordering. You can find more information on SQL Server collations at:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

|||I mean encoding, before user use font VNI-Times (Vietnamese language ) and save to database, now, if we show it with Unicode, we can not read anything, so that I am finding solution to convert encoding to Unicode.I intend export to excel, and import with some option that can change font encoding (if have any ).

How can I convert download SQL Server 2005 to a licensed version.

I downloaded a 180 day trial version of SQL Server 2005, and have it running. I have purchased a 5 user workgroup version. I would like to apply the 5 user license to the version that is currently installed.

My preference is to not reinstall everything since I have everything configured as I would like, and it is working great. What is the best approach?

Paul

The only way to do this is to Upgrade your Evaluation Edition to the Workgroup SKU. To do this you should run the Workgroup installation program and select the edition of SQL Server that you already have installed instead of installing a new edition.

Michelle

|||

Will this leave the installed database and all of its configuration as is?

Or will it change any of the configuration?

The reason I ask, is that due to a lack of proper planning we did not have SQL Server installed prior to a vendor arriving to install their application. I quickly downloaded the trial version, and installed it and ordered the Workgroup Edition. The applicationn is in and running, and I now want to make everything legal, and to have it run past the 180 day evaluation period.

If upgrading will change the application, I will need to pay the vendor to reconfigure things, and cause me problems.

Paul

sql

How Can I Convert Decimal To Hexadecimal

Hi!!!!!

I'm looking for a SQL FUnction that convert a decimal to Hexadecimal and

Hexadecimal to decimal data.

I know the way to convert for. But not with a SQL Function. certainly I

need to know How to express an Exponential Function.

Thank's.Hi!!!!!

I'm looking for a SQL FUnction that convert a decimal to Hexadecimal and

Hexadecimal to decimal data.

I know the way to convert for. But not with a SQL Function. certainly I

need to know How to express an Exponential Function.

Thank's.
check this...

/* User Defined Function To Convert HexaDecimal Value To Decimal Value
Input: HexaDecimal Value In String Format
Output: Decimal Value
*/
CREATE FUNCTION [dbo].[Fn_HEXCONV] (@.HEXVAL as VARCHAR(25)) RETURNS DECIMAL(20,0)
AS BEGIN
/* Declarations Of Variables Two Decimal Values To Store The Intermdeiate & Final Result,
String Value To Store The Hexadecimal Value During The Process,Two Counter Variables*/
DECLARE @.position int, @.INTVAL INT , @.CMDSTR NVARCHAR( 255 ) ,@.DECVAL DECIMAL(20,0),@.DECVALUE DECIMAL(20,0)
/* Initialising Variables */
SET @.position = 1
SET @.DECVAL=0
WHILE @.position <= DATALENGTH(REVERSE(@.HEXVAL)) /* Looping Through The String Until It Reaches The 0th Position */
BEGIN
/* Store The Decimal Value If the Hexa Value is Between A-F */
SET @.CMDSTR=CASE UPPER(SUBSTRING(REVERSE(@.HEXVAL) ,@.position,1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE SUBSTRING(REVERSE(@.HEXVAL) ,@.position,1) END
SET @.INTVAL=CAST(@.CMDSTR as INT) /* Casting The String To Integer */
SET @.DECVALUE=@.INTVAL
SET @.DECVAL=@.DECVAL+((@.DECVALUE)*POWER(CAST(16 AS BIGINT),@.position-1))/* Finding The Corresponding Decimal Value & Adding it To The Result */
SET @.position=@.position+1 /* Incrementing The Counter */
End
return CAST(@.DECVAL as Decimal(20,0)) /* Return The Converted Decimal Value Back */
End

Hope it will help you.
Joydeep ;)|||That functionality is built in, you don't need a function for it.DECLARE @.d DECIMAL(4)

SET @.d = 128

SELECT CAST(@.d AS VARBINARY(8)), CAST(0x0400000101000000 AS DECIMAL(4))-PatP

How can I convert datetime to number of minutes

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

Try something like this

(datepart(hh, tblTasks.TaskDuration) * 60) + datepart(mm, tblTasks.TaskDuration)

|||

I tried this and it will return the number of minutes for the hours; however, the Parenthesis will not stay around the (datepart(hh, tblTasks.TaskDuration) * 60) in the view. So the (mm) are not being added.

Well it is adding time for the minutes but 30 is calculating to 10, so 03:30 is returning 190 minutes and it should be 210.

Any ideas?

|||I gave you the wrong datepart signifier, try datepart(n, tblTasks.TaskDuration)|||

Ok, so now that I have the number of minutes, can I convert this to hours and minutes. What I mean is the reporting tool needs a numeric column to sum on, so 03:15 needs to be 3.25.

Is this possible?

|||

I tried and it looks to be returning the correct format. If you have any comments, I appreciate them.

CONVERT (FLOAT, DATEPART(hh, dbo.tblVolunteerTasks.VTaskDuration) * 60 + DATEPART(n, dbo.tblVolunteerTasks.VTaskDuration)) / 60

How can I convert DateTime to Date as Parameter?

Hi Guys! Need Help on this!! I am using a Datetime data type as my Parameter on my stored procedure in SQL Server 2005. I am also using Crystal Reports XI for my reporting using the stored procedure in SQL but my problem is that I want to use ONLY the DATE data type as my Parameter instead of using the datetime parameter in Crystal Reports! Since the SQL server does not have a Date data type, how can I convert this from DateTime to Only Date data type as my parameter?....Thanks!!

Use datatime data type and pass just date part from CR or strip off the time part wherever you are planning to use it.

declare @.d datetime

set @.d = getdate()

select dateadd(day, datediff(day, 0, @.d), 0)

go

AMB

|||

Thanks! but how do you pass just the date part from CR? Any idea would be greatly appreciated!! I can strip off the time part inside the stored procedure in SQL 2005 but CR is using the parameter which is datetime....

|||

Sorry about that, but I think that question could be answered better in a CR newsgroup. Try:

datetime(datepart("yyyy", {@.d}), datepart("m", {@.d}), datepart("d", {@.d}), 00, 00, 00)

AMB

|||Thanks AMB....that will work but that code is for the inside on the report...my problem lies in the parameter prompt window..how can I let the user only select the date without seeing the the time on the parameter prompt window?....|||

Sorry I have no idea. As I mentioned in my previos post, these questions would be better asked in a CR newsgroup.

AMB

how can i convert binary(8) to datetime?

HI! :shocked:
I tried to convert 0x01C3F0F5012D36E0, binary(8) to datetime
But
How can I do that?
thanks for allwhat does 0x01C3F0F5012D36E0 repesent in datetime as ?|||You can't convert it, it is bigger than the largest possible datetime:DECLARE @.dMax DATETIME

SET @.dMax = '9999-12-31 23:59:59.997'

SELECT Cast(@.dMax AS VARBINARY(8)), 0x01C3F0F5012D36E0-PatP

How can i convert binary(16) to Integer or numeric?

HI!!!:shocked:

Im trying to convert 0x00085180F0A2D511B69600508BE96424 to Integer or numeric format.

I just tried, At to many forms and combinations of that query
Help me !!!

SELECT CAST(CAST(CAST("field name " AS nvarchar) AS varbinary) AS float)

select cast(cast("field name " as varbinary)as integer)

select convert(int," field name") from FILE

select convert(varchar," field name") from FILE

The only answer that I have is
-1947638748 or or

And if I try with to many rows of the field at the same format,
It Answer me the same: -1947638748 for all the rows.

Thank`s for allcreate table #t1(f1 binary(16))

select *
from #t1

insert into #t1 values(convert(binary(16),'0x00085180F0A2D511B6960 0508BE96424'))

select convert(integer, f1)
from #t1

--The result is 1093813301. I dont see -1947638748

drop table #t1|||:D THANK YOU!!!, forXLDB

Just one more question please

Whats the reason for the space in the middle of the expression?

Before convert

Original Expresin
'0x00085180F0A2D511B69600508BE96424'

Convert expresin
'0x00085180F0A2D511B6960 0508BE96424'|||thank you...|||Sorry, but the actual answer is MUCH larger! You can't express that VARBINARY value as an integer, or even as a NUMERIC(38) which is the largest SQL Server will allow. The following code shows what I mean:DECLARE
@.b VARBINARY(16) -- binary image to convert
, @.i INT -- Which byte we're working on
, @.m FLOAT -- Multiplier for this byte
, @.a FLOAT -- Accumulator

SET @.b = 0x00085180F0A2D511B69600508BE96424
SELECT @.i = DataLength(@.b), @.m = 1, @.a = 0 -- Start with lowest order byte

WHILE 0 < @.i -- While bytes left to process
BEGIN
-- SELECT @.a, @.m, @.i, SubString(@.b, @.i, 1) -- Show your work
SELECT @.a = @.a + (@.m * CAST(SubString(@.b, @.i, 1) AS INT))
SELECT @.m = 256 * @.m, @.i = @.i - 1 -- Prepare for next byte
END

SELECT @.m -- Show results-PatP|||Thanks Pat Phelan

I like that explication step by step.
It really please me.sql

How can I convert a date and an amount in my select statement

I need to convert a date like 08/1/2009 to 0809

I also need to show currency as 100.00 and not 100.0000

How can I do these in a select statement?

SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment

I appreciate any help!

hi Jackxxx,

can you try this

SELECT convert(varchar,datepart(dd,getdate()))+convert(varchar,datepart(yy,getdate())) AS ExpirationDate, convert(decimal(10,2), 2323.2422)

thanks,

Satish.

|||

I tried the expiration date and the date was 7/1/2009 and your statement returned 172007.

Also I goofed on the other the field name is AmountPaid that I need to show 100.00 for.

|||

hi Jackxxx,

what i gave was an example you need to modify your actuall query accordingly like i've put getdate() so you need to put your datetime field in there similary amount field also.

thanks,

satish,

|||

It's almost perfect, the date still shows all for digits of the year. Is there a way to only show the last two digits? Like 09 for 2009

I very much appreciate your help!

|||

hi jackxxx,

i tried alot but its giving 4 digits atlast i had to cheat, use

select right(datepart(yy,getdate()),2)

hope it works now.

regards,

satish.

How can I convert 12/2/ to 12/2/current year

I have a field in my database that holds a date, the only part of the date I care about is the month and day (12/2/). I'm trying to use this field in a view column to show the for example 12/2/ and the current year (2007). Also if the month and day have passed like 11/1/ then it would be next year (2008).

Can anyone help me with this?

Look up the DatePart function in sql server. It has pretty much what you need.

|||

DECLARE @.ddatetimeSET @.d='11/20/2007'SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END

To put this in a view, just remove the DECLARE and SET statements. Copy the code from CASE through END into your select statement, and replace @.d with the field name from your table.Optionally add ' AS MyNewField' after the END to give the column a name.|||

Motley,

Is it possible to use this in a udf so I can use it in other views?

I tried the following, but received and error:

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 14

Incorrect syntax near 'END'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)
RETURNSDATETIME
BEGIN

SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()
THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)
ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)

END
GO

|||

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGIN RETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END )ENDGO

|||

Motley,

I get a new error: maybe I'm have missed something

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 12

Incorrect syntax near ')'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGINRETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END)GO

|||

Sorry, I editted the above code, it should work now. It was missing an END.

|||

Motley,

Thanks very much for your help, please take the rest of the week off.

How can I conserve the initial zero when convert numeric to string using STR()

Sorry to raise a stupid question but I tried many methods which did
work.
how can I conserve the initial zero when I try to convert STR(06) into
string in SQL statment?
It always gives me 6 instead of 06.

Thanks a lot.You can't "preserve" the zero. Integer 06 = Integer 6 = Integer
00000000006.

If you want to convert an integer to a varchar you can prepend a 0 character
to the result:

SELECT '0' + CAST(06 AS VARCHAR)

Returns '06'. The downside to this method is that if you do something like

SELECT '0' + CAST(10 AS VARCHAR)

You'll end up with '010' which may or may not be what you want. You can
build on this example with the SUBSTRING function to get exactly what you
really want out of it.

<angellian@.gmail.com> wrote in message
news:1148779910.070643.296910@.g10g2000cwb.googlegr oups.com...
> Sorry to raise a stupid question but I tried many methods which did
> work.
> how can I conserve the initial zero when I try to convert STR(06) into
> string in SQL statment?
> It always gives me 6 instead of 06.
> Thanks a lot.|||You are confusing the PHYSICAL display with the internal LOGICAL model.

This is SQL and not COBOL. There is no initial zero in a number; there
is an internal binary, BCD or whatever the hard uses representation.

Your next problem is that you do not understand that dispaly is NEVER
done in the database, but in the front end application. That is the
most basic concept of *any* tiered architecture, not just SQL.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Your next problem is that you do not understand that dispaly is NEVER
> done in the database, but in the front end application. That is the
> most basic concept of *any* tiered architecture, not just SQL.

Working so long as you have done in the database trade should have learnt
you to never say never.

There is at least one obvious case where formatting of output must be
done in SQL: to wit when the display is done in a standard query tool
like Query Analyzer. Which typically is the case for admin stuff.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> This is SQL and not COBOL. There is no initial zero in a number; there
> is an internal binary, BCD or whatever the hard uses representation.

This is SQL SERVER not SQL and not COBOL, SQL SERVER has many facilities to
aid the developer in creating a highly scalable, robust and maintainable
architecture.

Standard SQL is very weak in terms of features that we need out in the real
world.

> Your next problem is that you do not understand that dispaly is NEVER
> done in the database, but in the front end application. That is the
> most basic concept of *any* tiered architecture, not just SQL.

"Display" can never be done in the database because the database is a
service and has such has no UI, we use tools to get at the data.

The big problem here is your continued misconception that ALL formatting
should be done in the front end application, have you actually sat down and
thought about what that means? The fundemental principle of tiered
architecture design and development is that formatting is done where it is
most sensible and efficient, in terms of development and support cost and in
terms of performance.

My blog entry on this covers in more detail:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx

I see you use CTE, why don't you pull the results down into the application,
CTE's are a form of formatting for display purposes, as is COALESCE on the
SELECT clause, as is ORDER BY etc... Just where do you draw the line?

Anyway, you are still stuck in the mainframe model of all resources are in
the same box and that you use the VTAM protocol out to remote terminals.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1148830663.994422.182500@.y43g2000cwc.googlegr oups.com...
> You are confusing the PHYSICAL display with the internal LOGICAL model.
>
> This is SQL and not COBOL. There is no initial zero in a number; there
> is an internal binary, BCD or whatever the hard uses representation.
> Your next problem is that you do not understand that dispaly is NEVER
> done in the database, but in the front end application. That is the
> most basic concept of *any* tiered architecture, not just SQL.|||Something like this may help...Assuming your column name is COLUMN1 and
COLUMN1 has a numeric type.

select case
when COLUMN1 < 10 then '0' + cast(COLUMN1 as varchar(10))
end
when COLUMN1 > = 10 then cast(COLUMN1 as varchar(10)) end
......(rest of your statement...)

Hope this helps...|||Hi Angellian,

For 2 character string you can just use CASE...

declare @.number tinyint
set @.number = 2

select case when @.number between 0 and 9 then '0' else '' end + cast(
@.number as varchar(2) )

Otherwise, if your resultant string needs to be bigger than 2 characters do
this...

declare @.number int
declare @.string varchar(10)
declare @.size_of_fixed_string tinyint
set @.size_of_fixed_string = 10
set @.number = 40

print replicate( '0', @.size_of_fixed_string )

set @.string = left( replicate( '0', @.size_of_fixed_string ),
@.size_of_fixed_string - len( @.number ) ) + cast( @.number as varchar(10) )

print @.string

http://sqlblogcasts.com/blogs/tonyr.../05/29/765.aspx

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

<angellian@.gmail.com> wrote in message
news:1148779910.070643.296910@.g10g2000cwb.googlegr oups.com...
> Sorry to raise a stupid question but I tried many methods which did
> work.
> how can I conserve the initial zero when I try to convert STR(06) into
> string in SQL statment?
> It always gives me 6 instead of 06.
> Thanks a lot.|||>> There is at least one obvious case where formatting of output must be done in SQL: to wit when the display is done in a standard query tool like Query Analyzer. <<

No, that formatting is done in the Query Analyzer, which is a program
and not part of SQL. Trust me, we never voted on a "standard query
tool" in ANSI X3H2.|||>> I see you use CTE, why don't you pull the results down into the application, CTE's are a form of formatting for display purposes, as is COALESCE on the SELECT clause, as is ORDER BY etc... Just where do you draw the line? <<

UNH? CTEs are virtual tables and have nothing to do with display. Do
ypou also think that VIEWs and derived tables are formatting for user
display? COALESCE is a function that works with NULLs and CAST() to
get another internal data type result.

Things like CONVERT() on dates or PRINT in T-SQL is formatting.|||Just cause I've seen a couple of examples using CASE; I use trick
similar to your second example:

SELECT RIGHT('0' +CONVERT(varchar(2), @.number), 2)

Granted, it only works on a two-digit number, but it saves typing. The
REPLICATE idea is pretty smooth, though.

Stu

Tony Rogerson wrote:
> Hi Angellian,
> For 2 character string you can just use CASE...
> declare @.number tinyint
> set @.number = 2
> select case when @.number between 0 and 9 then '0' else '' end + cast(
> @.number as varchar(2) )
> Otherwise, if your resultant string needs to be bigger than 2 characters do
> this...
> declare @.number int
> declare @.string varchar(10)
> declare @.size_of_fixed_string tinyint
> set @.size_of_fixed_string = 10
> set @.number = 40
> print replicate( '0', @.size_of_fixed_string )
> set @.string = left( replicate( '0', @.size_of_fixed_string ),
> @.size_of_fixed_string - len( @.number ) ) + cast( @.number as varchar(10) )
> print @.string
> http://sqlblogcasts.com/blogs/tonyr.../05/29/765.aspx
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> <angellian@.gmail.com> wrote in message
> news:1148779910.070643.296910@.g10g2000cwb.googlegr oups.com...
> > Sorry to raise a stupid question but I tried many methods which did
> > work.
> > how can I conserve the initial zero when I try to convert STR(06) into
> > string in SQL statment?
> > It always gives me 6 instead of 06.
> > Thanks a lot.|||--CELKO-- wrote:
> >> I see you use CTE, why don't you pull the results down into the application, CTE's are a form of formatting for display purposes, as is COALESCE on the SELECT clause, as is ORDER BY etc... Just where do you draw the line? <<
> UNH? CTEs are virtual tables and have nothing to do with display. Do
> ypou also think that VIEWs and derived tables are formatting for user
> display? COALESCE is a function that works with NULLs and CAST() to
> get another internal data type result.
> Things like CONVERT() on dates or PRINT in T-SQL is formatting.

Hi Joe,

I didn't understand Tony's point about CTEs, but I think his point
about COALESCE stands. Surely, COALESCE is shorthand for having
formatting code at the front end like:

If Column1 is not null then
show Column1
Else if column2 is not null then
show Column2
Else if column3 is not null then
:
:
:
Else
show ColumnN
End If

Damien|||> UNH? CTEs are virtual tables and have nothing to do with display. Do
> ypou also think that VIEWs and derived tables are formatting for user
> display? COALESCE is a function that works with NULLs and CAST() to
> get another internal data type result.
> Things like CONVERT() on dates or PRINT in T-SQL is formatting.

Ok - I conceede CTEs, I was thinking about them within the scope of paging
on which you have in the past stated you would have the front end perform,
that literally means pushing a million rows over the network to the front
end.

> Things like CONVERT() on dates or PRINT in T-SQL is formatting.

The operator was trying to create a string with leading zeros which you
stated should be done in the front end.

Why on earth would you want to go to all the effort of using a 3GL / 4GL to
format the data when you can just simply do it in TSQL within the SQL Server
itself - nice and simple, nice and easy to support and maintain.

Your method relies on additional skills, the developer would need to
understand a programming language as well as SQL, that then translates into
a support and maintanence burden which costs money.

You can very easily do the formatting in the TSQL and use Integration
Services or DTS to export the data out to whatever you want - XML, XLS
etc...

Your recommendations around formatting date back to the 70's where rdbms
didn't have many facilities available for the developer other than SUM,
COUNT, MIN, MAX and AVG.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149114884.873451.172270@.g10g2000cwb.googlegr oups.com...
>>> I see you use CTE, why don't you pull the results down into the
>>> application, CTE's are a form of formatting for display purposes, as is
>>> COALESCE on the SELECT clause, as is ORDER BY etc... Just where do you
>>> draw the line? <<
> UNH? CTEs are virtual tables and have nothing to do with display. Do
> ypou also think that VIEWs and derived tables are formatting for user
> display? COALESCE is a function that works with NULLs and CAST() to
> get another internal data type result.
> Things like CONVERT() on dates or PRINT in T-SQL is formatting.|||>> Why on earth would you want to go to all the effort of using a 3GL / 4GL
>> to format the data when you can just simply do it in TSQL within the SQL
>> Server itself - nice and simple, nice and easy to support and maintain.

The general answer is that one would prefer to have the centralized database
as generic as possible so that it can support a variety of applications.

Having an application specific formatting at the central data source tend to
generate something called "application bias". Considering the OP's question,
given certain 5 applications requesting same data formatted in 5 different
ways, should he formulate a single generic query and do the formatting in
the application or should he create 5 different queries to support each
application? How about when the number of applications increases to 50? Or
say 500?

While it may appear to be efficient and easy to manage in the short term, it
can often be highly detrimental to the long term stability and management of
data centric systems.

This is nothing new but such bias is known to software engineers for decades
now. For details on why this separation of concern is important for data
oriented systems, ~Principles of Program Design~ by Michael Jackson is a
good book.

--
Anith|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> There is at least one obvious case where formatting of output must be
done in SQL: to wit when the display is done in a standard query tool like
Query Analyzer. <<
> No, that formatting is done in the Query Analyzer,

QA only has a standard formatting, with no options to specify a how a
certainly column should look like.

Thus, if you want a certain format when you look at the data in QA, SQL
is the only place to do formatting.

> which is a program and not part of SQL. Trust me, we never voted on a
> "standard query tool" in ANSI X3H2.

I never said so. I only meant to say that it is a plain query tool,
and about every RDBMS comes with one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Anith Sen (anith@.bizdatasolutions.com) writes:
> The general answer is that one would prefer to have the centralized
> database as generic as possible so that it can support a variety of
> applications.

I think the Perl has the right answer to this: There is more than one
way do it!

That is, if you can do things either in the server or the in the client/
middile layer, you can pick what fits best for the situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think of it the other way round, surely the 5 applications would call 1
single query and not different queries for different applications.

SQL Server is more a service orientated architecture, well - becoming that
anyway.

So, doing things centrally in the SQL Server is better because you only need
do it once and not in 5 places in 5 different langauges requiring 6
different skill sets.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e5n83p$v6r$1@.nntp.aioe.org...
>>> Why on earth would you want to go to all the effort of using a 3GL / 4GL
>>> to format the data when you can just simply do it in TSQL within the SQL
>>> Server itself - nice and simple, nice and easy to support and maintain.
> The general answer is that one would prefer to have the centralized
> database as generic as possible so that it can support a variety of
> applications.
> Having an application specific formatting at the central data source tend
> to generate something called "application bias". Considering the OP's
> question, given certain 5 applications requesting same data formatted in 5
> different ways, should he formulate a single generic query and do the
> formatting in the application or should he create 5 different queries to
> support each application? How about when the number of applications
> increases to 50? Or say 500?
> While it may appear to be efficient and easy to manage in the short term,
> it can often be highly detrimental to the long term stability and
> management of data centric systems.
> This is nothing new but such bias is known to software engineers for
> decades now. For details on why this separation of concern is important
> for data oriented systems, ~Principles of Program Design~ by Michael
> Jackson is a good book.
> --
> Anith

How can i add a tool to SQL Server as a add-in(plug-in)

I have developed a tool which you can use to convert XML(DTD or XML Schema) to relational database model. I want to add it to SQL Server.

Can i do it?

If you are asking if you can install a plug-in to Management Studio (SSMS) the answer is no. Currently SSMS is locked down. For the next release of SQL Server we are investigating how to expose a full extensibility interface.

Cheers,
Dan

How ASP into ASP.NET with SQL server 2000

Dear All,
I'm new on ASP.NET, now trying convert ASP with SQL 2000 into ASP.NET SQL 2000 server.
I facing a problem while read, write, update the SQL command.
like,
rs.open("Select * from tableA"),ocoon, permission,permission
If rs.eof then
rs.addnew()
rs.column1 = "1"
else
rs.column2 = "2"
rs.column3 = "3"
rs.update()
end if
rs.close()
set rs = nothing
this is how to change into ASP.NET with SQL selection and updating command??
Regards,
I would recommend reading up some articles on Data Access in ASP.NET. There are some Tutorials on this site.