Transact SQL :: How To Get Value Of A Particular Field From XML Data
Nov 6, 2015
We are running the following querry which insert data row from an XML source
INSERT INTO End_ReelProcessValue
(LineId
,ReelHist_id
,Variable_tagName
,ProcessMeasure
)
[Code] ....
From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :
[Variable_Tagname] CHAR(50) '@Name',
[ProcessMeasure] NVARCHAR(254) '@Value'
What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.
How can I retrieve that particular record ?
View 2 Replies
ADVERTISEMENT
Jul 20, 2015
I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975 ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656 ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712 ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
View 4 Replies
View Related
May 14, 2015
I am using a BCP format file to import a CSV file. The file looks like the following:
"01","02"
The format file looks like the following:
6.0
2
1 SQLCHAR 0 0 """ 0 ""
2 SQLINT 0 0 "","" 1 MROS
3 SQLINT 0 0 ""
" 2 MROF
When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02. These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file. The results was that the 01 became 12592 and the 02 became 12848. where these numbers are coming from?
View 7 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
Aug 28, 2015
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
View 3 Replies
View Related
Jul 11, 2005
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Nov 16, 2015
I can not modify DDL of the table, so creating a trigger etc is not an option. This is my syntax that I am using, and each #Testing.abcd has different possible options.
For example, when #Testing.abcd = Test1 then #Data.field2
okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
Declare abcd varchar(100), @sql varchar(1000)
Create Table #Testing (abcd varchar(100), efgh varchar(500)
Insert Into #Testing Values
('Test1', 'zzz'),
('Test2', 'xxx'),
[Code] ....
View 5 Replies
View Related
Oct 13, 2015
I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25)
BI int
Track int
CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
Trial BI Track CAT
A 5 3 1
B 5 3 2
[code]...
View 5 Replies
View Related
Mar 25, 2008
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies
View Related
Aug 10, 2015
I have [TableAccount.AccountType] field which can store these values:
Value
C
S
E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
View 4 Replies
View Related
Sep 22, 2015
I have a column with Full Names (e.g. Jane Doe)...how can I transform it via t-sql for end resutl Doe, Jane?
View 8 Replies
View Related
Oct 5, 2015
I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.
<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>
I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.how this can be handled in SQL query?
View 2 Replies
View Related
Sep 23, 2015
I have 2 database, databaseA and DatabaseB with same table structure.
I change lot of table structure in databaseB.
So i want compare what field that i add or delete in databaseB.
it's like what field there are in databaseB and not found in databaseA or nothing in databaseB but exist in databaseA.
How can i do that?
View 7 Replies
View Related
Nov 18, 2015
I have a small table named Exclude_Cusno_Item that has a varchar(29) field named
ITEM. When I query the table to show all of the records like this:
Select * from Exclude_Cusno_Item
The result set contains a record with 'AAA' in the ITEM field. However, where I write a query with a WHERE clause to select this specific 'AAA' record like this:
Select * from Exclude_Cusno_Item where item = 'AAA'
I get no results. What could be happening that would prevent the 'AAA' record from appearing in the result set of this query? Note that the 'AAA' entry has no trailing or leading blanks.
View 12 Replies
View Related
May 28, 2015
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
View 3 Replies
View Related
Dec 5, 2007
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
View 5 Replies
View Related
Apr 2, 2008
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
View 6 Replies
View Related
Sep 18, 2015
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5.
Desired data format: Name1 Name2 Name3 Name4 Name5.
Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally
Bush/Jim/Sr/Etal/Trustee
View 43 Replies
View Related
Jul 18, 2015
use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
View 9 Replies
View Related
Oct 6, 2015
When we are getting data in a table and we want to replace characters with other characters. For example, We have a table with a street address, and there are numerous ascii character values we want to review and replace if they exist. We were looking at using a table with 2 columns, 1 containing each ascii character value the other it's preferred replacement value. Then updating the street address searching through each ascii character and replacing it if needed. Currently, we are running it through a looping process searching each individual address for each ascii character, and updating it.
View 4 Replies
View Related
Sep 17, 2015
How to modify values in a SQL Database. In the table PRDDEmpmaster there is a field called email_address, and for those fields that have a value I need to change that value to add { } opening/closing "curly braces" to that field.
For example,
RPARSONS@YAHOO.COM becomes
{RPARSONS@YAHOO.COM} . Nulls/empty fields remain as is.
View 6 Replies
View Related
Oct 8, 2015
I have the following querry:
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,
[Code] ....
That query is suppose to add to calculation field OutStock and InStock based on the value of n
When executing this query I get the following message :
Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
View 3 Replies
View Related
Aug 21, 2015
Any easier way to do what I am trying, without having a table with all the dates and week numbers.
Scenario: Week number of a DateTime Field where the year does not start on January 01<sup>st</sup> but April 1<sup>st</sup> to 31 Mach.
Issue: A week always starts on a Monday so if the 1<sup>st</sup> is on Tuesday, the first week is Tuesday – Sunday, if the 1<sup>st</sup> April is on Friday, the 1<sup>st</sup> week is Friday – Sunday and 7 day periods from there.
CREATE
TABLE [dbo].[DailyCanx](
[ID] [int] IDENTITY(1,1)
NOT NULL,
[DateCancelled] [datetime] NULL
)
ON [PRIMARY]
[code]....
I could create a table with all the start date and end dates of all the week numbers but I think there must be a better way. using SQL Server 2008 R2.
View 5 Replies
View Related
Oct 22, 2015
I am trying to eliminate a scalar function by rewriting a view. I currently have a structure similar to the following:
With FirstQuery as (SELECT Trial, SBOI, TBOI,
FROM TU
WHERE SBOI Between 22000 and 22999 and TBOI < 10000),
strt as (SELECT TOP 1 TimeOfEvent, Trial, SBOI, TBOI
From TU ORDER BY TimeOfEvent),
[Code] ....
What I trying to do is for the Trial, SBOI, and TBOI in FirstQuery, find the Start Time and Stop Time. TimeOfEvent is an integer in milliseconds. Say I have the following data:
TrialID SBOI TBOI TimeOfEvent
A 22000 5000 5
A 22000 5000 10
B 22000 5000 8
B 22000 5000 15
So the DISTINCT output would be:
Trial SBOI TBOI StartTime StopTime
A 22000 5000 5 10
B 22000 5000 8 15
The problem I am having is that strt is selecting the smallest time period. It doesn't seem to care what the current record in cte is. I thought that by using my joins, it would make it select smallest TimeOfEvent for the Trial, SBOI, and TBOI that are selected in cte. Obviously that is not the case. So, I was trying to add a WHERE to the strt Select Statement such as
WHERE TrialID = FirstQuery.TrialID and SBOI = FirstQuery.SBOI and TBOI = FirstQuery.TBOI but it isn't working either.
What can I do?
View 5 Replies
View Related
Jul 7, 2015
I have this rows as result of my query:
aaaaa bbbbb date result
------- ------- -------- ---------
a 100 7-7-15
a 100 7-7-15
a 100 8-7-15
b 200 7-7-15
b 200 8-7-15
b 200 8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa bbbbb date result
------- ------- -------- ---------
a 100 7-7-15 50
a 100 7-7-15 50
a 100 8-7-15 100
b 200 7-7-15 200
b 200 8-7-15 100
b 200 8-7-15 100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3) :
SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3,
CASE WHEN TRAPR3 IS NULL THEN
(CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
[Code] .....
View 8 Replies
View Related
May 12, 2006
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies
View Related
Dec 2, 2015
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
View 5 Replies
View Related
Oct 27, 2015
I have a field called PPH and this field type float. so, this field could be null, 0.0, or values. I am using case statement:
[PPH] = case when h.HoursFloat = 0 then 0 else
round(s.Pledges / h.HoursFloat,2) end,
I am trying to add where if h.HourFloat is null then 0 but, it does not work. I have tried different ways such as
[PPH] = CASE isnull (h.HoursFloat,0)
when 0 then 0 else ....
How to add another condition to check on null then 0.
View 9 Replies
View Related
Sep 16, 2015
I have a datetime field for e.g 2015-09-15 00:00:00.000 and an integer field for e.g.100809.
The integer field is actually a time value as in hours,minutes,seconds. For e.g. the value
100809 means
10-hours
08-minutes
09-seconds
I need to combine the datetime and the int field to get the output as below
2015-09-15 10:08:09
View 7 Replies
View Related
Sep 9, 2015
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies
View Related
Jun 30, 2015
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
View 2 Replies
View Related
May 28, 2015
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
View 7 Replies
View Related
