SQL Server 2012 :: Putting Data Into XML Format
Oct 7, 2015
Need getting data into XML format as shown in the last code block.The datatypes and table structures are pretty much fixed, but I can re-hash data into another Temp Table, CTE, etc..This is a server running SQL Server 2012, but I'd guess any version that understands FOR XML PATH should be fine.
Source tables and data
if object_id('Tempdb..#Element1') is not null drop table #Element1;
create table #Element1 (
[Attr1] varchar(10)
,[Attr2] varchar(4)
[code]...
View 9 Replies
ADVERTISEMENT
Jan 28, 2015
how can i put multiple values in the variables.
for eg:
Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.
I am getting syntax error(,)
View 5 Replies
View Related
Jun 18, 2015
I have below table:
IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)
and i want to have a table in below format: (query for it)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
View 9 Replies
View Related
May 5, 2005
Hello,
I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater.
Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName)
What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table?
Thanks!James
View 1 Replies
View Related
May 5, 2014
declare @xml table (xmldata xml)
insert @xml select
N'<parseObject name="Motel">
<fields>
<field name="vehicleno" fieldType="int" fieldSize="">
[Code] ....
I want to extract data in in table format
ParseObjectName FieldType FieldSizeGrammar
Motel VehicleNo Int NULL div.biz-page-subheader li > span.i-phone-biz_details-wrap
mapbox-icon span.biz-partno[/size]
View 2 Replies
View Related
Oct 23, 2015
I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
--------------------------------------------------------------------------------------------------
Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015
--------------------------------------------------------------------------------------------------
Laptop | Tom | Tom | Tom | Avail | Avail
Projector | Avail | Avail | Avail | Avail | Bob
Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
View 8 Replies
View Related
Feb 28, 2014
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'
View 3 Replies
View Related
Aug 21, 2015
I need to handle date format "41981".
View 9 Replies
View Related
Jun 21, 2004
The tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.
View 1 Replies
View Related
Mar 10, 2015
I am looking to be able to add 15 minutes to a time value that is in character format. here is sample data:
0530
0545
0600
0615
0630
0645
0700
0715
0730
0745
Whenever there is a leading zero, I need to preserve that as well. Here is an example of what I an looking for:
0545 + 15 = 0600
0600 + 15 = 0615
1345 + 15 = 1400
View 9 Replies
View Related
Jun 24, 2015
I have a data file listing open and close hours for the day of the week. I need to format it like
Mon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
View 9 Replies
View Related
Nov 3, 2015
I am trying to find a solution to get the result set to fetch a particular string format from a table in my database, which has a column of NVARCHAR data type
CREATE TABLE #ActivityComments(Comments NVARCHAR(MAX))
INSERT INTO #ActivityComments VALUES('This is the study code for Field Phase S14-04932-01')
INSERT INTO #ActivityComments VALUES('Phase reporting has the study S15-04931-01 which is obselete')
INSERT INTO #ActivityComments VALUES('Phase running study code S14-04932-02 is not valid')
The output of the query should be like:
S14-04932-01
S15-04931-01
S14-04932-02
Is there any way possible to achieve this..
View 7 Replies
View Related
Apr 17, 2014
I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.
I tried with pivot but does not seems to be working.
View 2 Replies
View Related
Jun 18, 2014
I am currently working for the Sybase to Sql Server migration project and have been able to test migrate few tables using SSIS. After migration, i was doing some data comparision and could see the date formats are different between Sybase and Sql server. However, there are no issues with data like day in sybase becoming month in Sql server except the formats are different.
Do I need to act on this date formats? Not sure if this would cause any issues in front end application that will consume sql server date data.
View 1 Replies
View Related
Nov 9, 2014
I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
View 0 Replies
View Related
Mar 23, 2015
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
View 6 Replies
View Related
Apr 24, 2015
Why am I getting message "A valid table name is required for in, out, or format options."
I used the syntax from a tutorial about bcp utility. I am trying to create a format file for flat file import and export.
My server instance is "stat-hpsqlexpress"
The database name is "STATRLO"
Owner is "dbo"
Table name is "PM-allactivity-emaillog_042315"
The bcp comand I am trying to run is:
bcp STATRLO.dbo.PM-allactivity-emaillog_042315 format nul -c -t, -f C:databaseActivity_c.fmt -S stat-hpsqlexpress - T
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Yes I know the instance says sqlexpress...it was upgraded.
View 3 Replies
View Related
Jun 15, 2015
I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305
View 7 Replies
View Related
Sep 27, 2006
First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please
View 2 Replies
View Related
Apr 22, 2015
Table1 contains a CODES column which may store multiple codes separated by commas.
The descriptions for these codes are stored individually in a column in in Table2.
I would like to return the descriptions from Table2 in the same format as the codes from Table1.
Table1 Stores multiple codes per ID in a single column.
ID CODES
-- --------------
01 Code1,Code2,Code3
Table2 Stores the definitions for the codes in Table1
CODE DESCRIPTION
----- ---------------
Code1 Description1
Code2 Description2
Code3 Description3
The tables are what the tables are. Here is what I am trying to accomplish:
ID CODES DESCRIPTIONS
-- ----------------- --------------------------------------
01 Code1,etc Description1,Description2,Description3
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
View 7 Replies
View Related
Dec 4, 2014
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00
View 5 Replies
View Related
Feb 2, 2006
I'm trying to put a date into a SQL Server table. The database field type is "smalldatetime". The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#). When I run the following code the date in the database is always ends up being: 1/1/1900.
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _ "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery()
Any other field types work fine, it's just dates that aren't working ???? Can someone please provide a code snippet showing me what I'm doing wrong??
View 3 Replies
View Related
Jan 31, 2006
Where can I further educate myself on this subject?
Right off from the start I would assume that installing SQL Server 2000 or 2005 on Windows Server 2003 that is set up as a web server hosting a website would be against "best practices." Is my assumption right?
Common sense tells me to not to host a website on a pc that is also hosting my database.
View 4 Replies
View Related
Jun 9, 2006
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 Related
Apr 5, 2007
I have a simple Website and Database created in VWD exp.
I've just added a windows account to GoDaddy.com.
Unfortunately there is no way to port your database on to the GoDaddy server directly, but it says you must instead, recreate the database by copying and running SQL statements from a file called "personal-add.sql". (their "help" page: http://help.godaddy.com/article.php?article_id=695)
I can't find any such file. Can someone please let me know what I'm missing here?
Thanks.
View 4 Replies
View Related
Jan 30, 2015
I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:
Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM
Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2
Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.
View 8 Replies
View Related
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
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
Mar 7, 2004
My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??
View 3 Replies
View Related
Mar 13, 2000
I missed the days of 6.5 when I can take a database from one server create a device for LOAD on another sever and use the LOAD database command to put that database on another server that has MS SQL SERVER on it..
So what is the trick with 7.0 if DTS is out of the question??
View 4 Replies
View Related
Sep 18, 2014
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
View 2 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related
Oct 21, 2014
I want to select weekly data from daily data.lets say Today's date-10/23/2014(Thursday) My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--
View 2 Replies
View Related
