I Want Ot Convert Xml Data Format Into The General Data Format
Jun 9, 2006
hi sir
i have table hh .it has two columns
one is hhno is integer datatype another hhdoc is xml data type like
hh table
hhno hhdoc
------------
---------------------------------------------------------------------------------
100
<suresh>sfjfjfjfjf</suresh>....................................
101
<ramesh>hhfhfhf</ramesh>..................................
how to convert the xml data format into the general data format pls
help me with examples
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?
I've got a field I'm trying to convert into a date format as it currently sits as a text field. The date setup is of the format DD/MM/YYYY. Some of the fields are NULL, however for the sake of filling in gaps I've just set the nulls to '01-01-1905'.
I've tried to use CAST to change the data into a readable format for SQL Server however I tend to get one of two messages:
Conversion failed when converting date and/or time from character string.
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] ....
Result: Need to Display [Total Service Time] in below Format:
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Using SQL Server 2005, I have set up a database for a school to provide timetables for students, teachers etc. The data is currently displayed in this form:
DAY LESSON YR SUBJ GRP STAFF ROOM Mon 1 7 PE T Da Gym Mon 2 7 PE T Da Gym Mon 3 7 Ma A Pr 5 Mon 4 7 Ma A Pr 5 Mon 5 7 Ma A Pr 5 Mon 6 7 Dr T Fn 36 Mon 7 7 Dr T Fn 36 Mon 8 7 En T Bn 21 Mon 9 7 En T Bn 21 Tue 1 7 De T Df 27 Tue 2 7 De T Df 27
etc.
I would like to display this as timetable with DAYS as row headings and LESSON numbers as column headings, with the remaining column info. in the above table combined into one value:
1 2 3 4 etc
MON (7 PE T Da Gym) (7 PE T Da Gym) (7 Ma A Pr 5) (7 Ma A Pr 5) TUE (7 De T Df 27) (7 De T Df 27) etc. WED THU FRI Should I be doing this with PIVOT (scares the life out of me ...) or is there some other solution? Many thanks.
E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate, convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date from GLBATCH
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 .
I 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?
Hi, 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.
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.
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?
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.
Query: 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.
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
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!
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?
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
