SQL Data Into XML Format
Mar 18, 2003Hi,
how can transfer sql server data into XML format?.
Thanks,
Ravi
Hi,
how can transfer sql server data into XML format?.
Thanks,
Ravi
hi siri have table hh .it has two columnsone is hhno is integer datatype another hhdoc is xml data type likehh tablehhno hhdoc---------------------------------------------------------------------------------------------100<suresh>sfjfjfjfjf</suresh>....................................101<ramesh>hhfhfhf</ramesh>..................................how to convert the xml data format into the general data format plshelp me with examples
View 1 Replies View Relatedhi
when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .
ex:
ARIZONA
ALABAMA
STATE
but i m getting imported
as :
ARIZONA ALABAMA STATE
How to Format a single column while importing?
Regards
Raj
How can I display a value as 0.00 from a field of datatype numeric?
View 1 Replies View RelatedI have an SQL database with a number of fields, one of which is for storing the date (datetime).When I insert data into the table, I only supply the date because I don't want or need the time to be listed. However, when I execute a query, the data that shows up in my gridview is (e.g) 13/10/2007 12:00:00 AM.
What I want ti know is, how can I format the datetime when I run the query so that it only shows 13/10/2007?This is how I'm retrieving the data at the moment:
SqlCommand command = new SqlCommand("SELECT * FROM tblMail", sqlConn);conn.Open()SqlDataReader reader = command.ExecuteReader();dataGrid.DataSource = reader;dataGrid.DataBind();conn.close()
In my dataGrid, I have a number of fields that are bound to the data source, such as Date, Name, Address etc etcAnyway, any ideas on how I can format the SQL DateTime so it only shows the date in my datagrid?
Hi
I am trying to export data from SQL which is ok and I can do that part. However from the result of the query I need to add the values of certain columns. Because I'm a newbie to SQL I'll explain and try and show images as best as I can as we go....
So far I have written a query
select Stockitem.code, Warehouseitem.ConfirmedQtyInStock, Warehouseitem.UnConfirmedQtyInStock, Warehouseitem.QuantityAllocatedStock, Warehouseitem.QuantityAllocatedBOM, Warehouseitem.QuantityAllocatedSOP, WarehouseItem.WarehouseIDfrom WarehouseItem INNER JOIN StockItem ONWarehouseItem.ItemID = Stockitem.itemidINNER JOIN BinItem ONBinitem.itemid = stockitem.itemidwhere WarehouseItem.WarehouseID = '3403' AND BinItem.BinName LIKE 'S%' AND BinItem.BinName <> 'S' OR BinItem.BinName LIKE 'T%' AND BinItem.BinName <> 'T'order by stockitem.code
This returns the following results....
Code ConfirmedQtyInStock UnConfirmedQtyInStock QuantityAllocatedStock QuantityAllocatedBOM QuantityAllocatedSOP WarehouseID
12345 96.00000 .00000 .00000 3.00000 13.00000 3403
Now I am trying to find out the amount of freestock available for everything with the WarehouseID 3403. Therefore in the example above to be able to workout how many '12345' (Code) we have in stock, the equation used to find out the free stock available is as follows....
Free Stock = (ConfirmedQtyInStock + UnConfirmedQtyInStock) – (QuantityAllocatedStock+ QuantityAllocatedBOM + QuantityAllocatedSOP)
Therefore if I manually use the equation above the amount of freestock available =
Free Stock = (96+0) - (0+3+13) = 80 (free stock).
Then once I have the equation to be able to output the 'freestock' quantity, I need to export the results into an excel spreadsheet with the following layout...
ColumnA
Code,FreeStock
12345,80
Hope I've explained this in a good manner, any help would be gratefully received.
Simba
I am using SQL 2005 SSIS. I need to do a data conversion for a date field in a txt file. I used the import wizard to bring my txt file into SQL 2005 but didn't convert the date. The date is displayed in the flat file as 20070612. Can someone help me convert the date. I did add an OLE DB Source to the Data Flow screen and selected command what do I do next and what do I write?
View 9 Replies View RelatedHi,
I am entering values from .Net app to a table in Sql 2k. The Identity field needs to be in 'ABC0000012' format (3 fixed letters and 7 digits - incremented by 1). My code MUST BE within a stored_proc. I am trying to format the column to have reqd. number of preceeding zeros. How can I get those ?
Pls help.Sam
I wish to extract the data from an SQL database table to a txt file. There are certain integer fields that I want to output as 7 chars long with leading zeroes. So for example if the column January_total contains the value 123 I would want that ouptut as 0000123. Can anyone advise me as to what the best way to go about doing this is - I've looked at how CAST and CONVERT work but they dont seem to to offer the functionality I require.
View 2 Replies View Related1)How would l write sintax to check data format in sql? Eg field customerNo should be 10 characters long and alpha numeric. Date should be etc.
2) How do l reference a value in another cursor?
Hi,
What's the best way to export data from SQL Server to XML format. I've taken over a VB application written to carry out this task but it seems more complicated than it needs to be. Is it possible to just miss out VB and use say a DTS program instead??
I have been given a schema(XSD) file and as far as I'm aware any xml has to be formatted according to this schema.
Thanks
Matt
i realized that there is much talk where data will be stored, what are the ways to organize data, but did not see anywhere what format this data. If they are text files they are .txt? All of them? Why Linux in a text file does not necessarily need to have this extension.For example, this forum, every time a thread is created it creates a text file with the contents of the thread inside a folder that represents the board? Is that how it works?
View 1 Replies View Relatedis there a specific command that can do this using the query analyzer?
Please help me on this.
Thank you.
Your future is made by the things you are presently doing.
hi guys how can I change this format
date|amount
02/25/07| 00.00
02/26/07| 00.00
To
date|amount
02/25/07| 0.00
02/26/07| 0.00
thanks!
I want a report in excel from my SQL database from one table. I wantto create a view and then use DTS package to export it to excelformat.But how can i get the following format in a view?? They should begrouped by date.Date Column 1 column 2 column 329/10/2003 one $30.00 somedatetwo $45.00 somedata2three $67.00 somedata3Total ******** $142.0030/1/2003 blah blah blahblah blah blahand so on . How can i get such a format in a view.Thanks in advance. I can't use SHAPE command in view i guess.
View 1 Replies View RelatedQuery:
Select convert(CHAR(45),surname+','+fname) Name from beneficiary
Output from the above query:
Name
---------------------------------------------
BICKFORD ,ROSA
KOCH ,ERIC
I am desired Results as follows: Please help
Name
---------------
BICKFORD,ROSA
KOCH,ERIC
I am doing reports and I am generating reports for weeks data. And I need data in the format of 1-2 week, 3-4 week and etc. So, I have tried to use aggregate functions also but they are not applicble for table and lists where all my report items are present. So. some one who can come up with the solution will be appreciated.
View 8 Replies View RelatedHello Everyone,
Please guide me in converting the value to date format.
from source i'm getting the value (which is acchally a data value) '20070730'.
I need this value to be in date format '2007/07/30'
Please help me in getting it done.
thank you
We will be getting a disk from a company that we have been working with that will have all our data on it in
MS SQL format. The problem is, that we do not have SQL anything here to be able to utilize our data!
Are there any good programs on how to convert this data to something we could use? What programs can we convert this data to?
Any insight appreciated. I'm fairly computer literate - but not with SQL at all.
Hi,
I have a need to convert sql server data to cobol format. Has anyone done this?
I have a PC install the SQL Server 2005 Eng Version and have a database with table.In the table , there is a datetime format column.
So, I write a SQL Insert into Table1 (startdate) values ('4-Oct-2006 10:24:29 AM'), It works, record inserted.
I have another Server (Windows Server 2003 Chinese Version with SQL Server 2005 Chinese Version). Also,have a database with table and run the same sql statment
Insert into Table1 (startdate) values ('4-Oct-2006 10:24:29 AM'). It does not work, since the error message is in chinese, so I try to translate in english.
The error says something like, "when converting the string to datetime, it fails"
And then I try to rewrite the SQL into Insert into Table1 (startdate) values ('10/4/2006 10:24:29 AM'). It works.
So I want to ask and option I can set in SQL Server 2005 to make me use different kinds of date format in the SQL.
Please Help, thanks
stepby
Hello all,
I am trying to modify the output of a SELECT statement in a VB asp.net page that pulls data from a SQL DB. I only need to modify one column in the gridview, but I'm having some issues.
Here is the situation:SELECT TOP 24 ID, RecID, Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM mTrafficORDER BY Timestamp DESC
The Timestamp column returns a number value (i.e. 564566).In terms of a date, 564566 doesn't mean anything to the user, so I need to convert this number into a recognizable and accurate date.
The formula I need to implement is Timestamp/1440 - 1
The following SELECT statement returns the number as a date, but makes all records the same date:
SELECT TOP 24 ID, RecID, Convert(datetime, Timestamp/1440 + 1) as Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM mTrafficORDER BY Timestamp DESC
What do I need to do to implement this function dynamically into my gridview?
Thanks in advance!
Chris
Why does SQL add 4 zeros at the end of a money data type? I have to format my strings once they are retrieved because of this. I am not sure if I did something wrong, but shouldn't it only have 2 trailing zero's?
View 1 Replies View RelatedDear all DBA,
i really need some help from all of you.
I currently have some DTS package which will transfer data from
text file to the SQL 7.0
This transformation is schedule using the Sql Agent.
The problem is the transfering data which have the Date datatype.
the Date format is in the dd/mm/yyyy. But the default language i set in my
sql server is english which suppose will use the date format mm/dd/yyyy.
therefore, the Sql server will treat my data which in dd/mm/yyyy as mm/dd/yyyy. so, the data like '08/09/2000' (in dd/mm/yyyy) become '08/09/2000'
(in mm/dd/yyyy)
i already set my user'language who will login in the sql agent as 'british english'. I also the OLE connection in the DTS package that i built using 'british english'.
when i run the agent in the Sql Enterprice Manager, it use the dd/mm/yyyy format which is correct. BUt when i log out and let it run as services which mean let the agent excute the DTS. it use back the mm/dd/yyyy fromat which cause the data become wrongs.
i hope someone can give me some advice on this.
Thanks for your help
from
samuel yeong
Hi
I am trying to export an table data to csv format. The problem here is the table columns are dynamic. The DTS exports only the columns available during the DTS design time and it ignores if any new columns are added after the design. I need solution for this asap.
Thanks
SqlJerin
Hello,
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
Thanks all in advance,
Saurav
My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??
Dear friends,
In SQLServer, it seems to make data stored in Unicode, you need to go to each column type and change it from varchar to nvarchar.
Apart from this, is there a direct way of Setting Unicode for the whole database at once, which would set all coulns to the unicode equivalent.
It seems there is such an option in Oracle
Please comment on this..
Regards
Benny
Hi
I have a table that stores the period and year as two seperate fields the problem that i have is that when the data is entered in the data base the period is sometime entered as 6 or 06, hence i have a table thaat looks like as follows:
Period Year
====== ====
6 2006
06 2005
12 2006
3 2005
2 2005
4 2005
04 2005
03 2005
when i currently query the information using the period i use the LIKE command in SQL which gives me the results but the problem is when the like is done with 2 as the period value it also retrieves the 12, 02, and 2, in that case the period 12 was not that was requested.
I was wondering is there a way to reformat the data so that all the data is in a consistent format?
Or is there a better way of quering the information in the current format?
Hey all,I have a basic table that looks something like this.CREATE TABLE MyTable(ID INT IDENTITY PRIMARY KEY,Company_ID INT NOT NULL,Round VARCHAR(50) NOT NULL,Details VARCHAR(250) NOT NULL)It has a few rows of data that look like this:Identity Company_ID Round Details--------------------------------------------1 5 A Blah, blah.2 5 B Generic data, blah blah.3 5 WERT More generic blah blah.Now what i'm trying to do during my select statement is select all the rowsthat belong to company_id 5 but if any of the rows round value contains thetext "WERT" convert that text into just a "--" for presentation purposes,but still select that row. I can't seem to figure out how i would transformthe text in the select statement? My immediate thought was substring /replace but i would need to combine it with an if else statement which i'veno idea how to make work in a select (sub-query maybe?) statement. Is thispossible? Perhaps i'm stuck iterating through the returned data within theclient application before presenting?Any help, as always, would be greatly appreciated.Muhd
View 5 Replies View RelatedI have 1st week and 2 nd week data in my database and for a particular report I need to add up the corresponding values of both the weeks and display them in one column as 1-2 format. I have tried out various options on reporting side but I was not able to do anything. Now can anyone tell me if I can write a query for that?? If so what is the format of the query and where do I need to write it???????
View 7 Replies View Related
hi all;
i have a doubt;
can we modify the data / format the data in a dataflow task?
Requirement
1. Excel file source
2. Convert data "Dec-07"(Mon-YY format) into "Dec-2007"(Mon-YYYY)
3. OLEDB Destination
how to do the second step?
Hello Everyone,
Please guide me in converting the value to date format.
from source i'm getting the value (which is acchally a data value) '20070730'.
I need this value to be in date format '2007/07/30'
Please help me in getting it done.
thank you