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

View 1 Replies


ADVERTISEMENT

Convert SQL Table Data To XML Format

Oct 31, 2006

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

View 2 Replies View Related

Convert Text Data To Date Format

Nov 13, 2013

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.

-Or-

error converting data type varchar to date

View 2 Replies View Related

Convert Date Format - Data Type Varchar

Aug 6, 2013

I have a column which is

Data type : varchar(10)
Date format : 8/5/2013

I want to convert that column to the following

Data type varchar: (8)
Date format : 20130805

View 5 Replies View Related

Data Access :: Date Formatting (Convert Minutes Into HH:MM Format)

Jun 20, 2015

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:

But Some values are repeating ....

View 2 Replies View Related

Adapter To Convert CSV Format File To SAP IDOC Format

Nov 16, 2006

Hi All,

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.

Regards,

Kuldeep Chauhan

View 2 Replies View Related

How To Convert A Table Containing Timetable Data Into A Proper Timetable Format

Mar 7, 2008

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.

View 1 Replies View Related

How To Convert Long Date Format To Short Date Format In Store Procedure.

Feb 1, 2008

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.

View 1 Replies View Related

Transact SQL :: Cast Or Convert Date In Format YYYY-MM-DD Into New Format Of MM/DD/YYYY?

Nov 27, 2015

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

View 11 Replies View Related

Data Format Issue While Importing Data From Excel To SQL

Jul 17, 2007

hi



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

View 1 Replies View Related

Transact SQL :: Display Normal General Date Format

Jul 30, 2015

I've come accross this code and need to alter it to display a normal General Date format.

CASE WHEN p.BIRTHDTTM IS NULL THEN ''
ELSE RIGHT('0' + LTRIM(CONVERT(VARCHAR(20), BIRTHDTTM, 113)) , 20)
END AS BIRTHDTTM

The original data (which is a DOB field) looks like this

1936-08-14 00:00:00.000

And the code above is turning it into this which is good but means I cannot then format in SSRS

14 Aug 1936 00:00:00

So ideally I'd like the code above to be altered so that it gives me

14/08/1936

View 14 Replies View Related

SQL Data Into XML Format

Mar 18, 2003

Hi,
how can transfer sql server data into XML format?.
Thanks,
Ravi

View 3 Replies View Related

Data Format

May 13, 2004

How can I display a value as 0.00 from a field of datatype numeric?

View 1 Replies View Related

Format DataSource Data

Oct 14, 2007

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?

View 2 Replies View Related

Exporting Data Into .CSV Format

Jan 17, 2008

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

View 2 Replies View Related

Data Format Transformations

Jun 18, 2008

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 Related

Format Column Data

Apr 3, 2006

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
 

View 3 Replies View Related

Outputing Data In A Particular Format

May 31, 2006

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 Related

Check Data Format

Jun 18, 2001

1)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?

View 1 Replies View Related

Export Data To XML Format

Jan 7, 2005

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

View 2 Replies View Related

Format Of Data - How It Works

Jun 20, 2014

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 Related

Export Data From SQL To .PDF Format

Feb 6, 2007

is 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.

View 10 Replies View Related

Converting Data Format

Nov 16, 2007

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!

View 3 Replies View Related

Getting Data In Particular Format In View

Jul 20, 2005

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 Related

Data In Desired Format

May 5, 2008

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

View 8 Replies View Related

How To Get Week Data As 1-2 And 3-4 Format

Apr 25, 2008

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 Related

Converting The Value To Data Format

Aug 14, 2007

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

View 2 Replies View Related

Converting Data From MS SQL Format

May 1, 2008

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.

View 1 Replies View Related

Sql Server Data To Cobol Format

Apr 16, 2007

Hi,
I have a need to convert sql server data to cobol format. Has anyone done this?

View 3 Replies View Related

SQL Server 2005 Data Format

Jul 5, 2007

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
 
 
 

View 2 Replies View Related

Changing Format Of Data In Gridview Using VB

Mar 24, 2008

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

View 2 Replies View Related

Money Data Type Format

Mar 23, 2006

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 Related

Date Format In Transfering Data In DTS

Aug 24, 2000

Dear 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

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved