Easy Way to Convert SQL Data to XML?

Hi to all :

Does anyone know how convert easily the SQL Records into XML?

OcinxLook into the FOR XML clause in the SQL Help files. That will return the data from the server already converted to Xml. You can also read the data into a DataSet and then call the GetXml method to convert the data in the DataSet to Xml.

Good luck.|||I have found this to be a good reference:XML and Internet Support -- Retrieving and Writing XML Data.

Terri

Easy Convert Question

Will the following SQL allow me to see the results of the computed column in
decimal format even if the Columns used in the calculation are defind as int
:
Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
I have used it but I am not getting any decimal places. It is showing the
decimals as 0. If this is not the problem could you please make some
suggestions.
Thanks
AdamTry multiplying the arguments by 1.0, or explicitly converting the arguments
to decimal.
By arguments, I mean the individual pieces, not the result of the
calculation.
A
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:836A42B5-D2D1-480B-82A5-A167007B0123@.microsoft.com...
> Will the following SQL allow me to see the results of the computed column
> in
> decimal format even if the Columns used in the calculation are defind as
> int:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards]))
> 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||You have to change the formula instead.
((#Operator2.[Count] * 1000.00)/#Closed2.[Dyed Yards])
AMB
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||Can you try this:
Convert(Dec(10,5), ((#Operator2.[Count]*1000.0)/#Closed2.[Dyed Yards]))
'Count'
Perayu
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||How about something like this
select price/cast(code as money) from xyz
i.e. the denominator gets converted to 'money' type :)
Cheers,
JP (Just a programmer:))
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O515emXqFHA.2652@.tk2msftngp13.phx.gbl...
> Try multiplying the arguments by 1.0, or explicitly converting the
> arguments to decimal.
> By arguments, I mean the individual pieces, not the result of the
> calculation.
> A
>
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:836A42B5-D2D1-480B-82A5-A167007B0123@.microsoft.com...
>|||Thanks guys, it worked
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||OR
select price/cast(code as decimal(10,4)) from xyz
Cheers,
JP (Just a Programmer;))
--
"JP" <someone@.somewhere.com> wrote in message
news:ucbhzwXqFHA.3104@.TK2MSFTNGP12.phx.gbl...
> How about something like this
> select price/cast(code as money) from xyz
> i.e. the denominator gets converted to 'money' type :)
> Cheers,
> JP (Just a programmer:))
> --
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O515emXqFHA.2652@.tk2msftngp13.phx.gbl...
>|||hi AB
you can try it as:
#Operator2.[Count]*1000.0)/#Closed2.[Dyed Yards] as [count]
hope this will help u
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam

Easier way to convert Non-Unicode to Unicode

I have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.

I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?

I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?

In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...

Thanks.

If SSIS has to use a namespace to identify a column...this happens when you have Source1.CustID and DataConversion1.CustID it can't do the mapping. If you changed the names in the source it could, but that might be more trouble than it's worth.