Extract A Value From XML

Feb 17, 2015

I have the following xml:

<MachineRepostitoryDS xmlns="http://Testing.com/Deployment/MachineRepostitoryDS.xsd">
<Components>
<Environment>QA400</Environment>
<HostName>HostTesting</HostName>
<Component>EventProcessorService</Component>
<CompStatus>OK. 17 Testing assemblies found</CompStatus>
<Version>4.0.24.0</Version>
<Path>D:TestingEventProcessor.WinService</Path>
</Components>

I would like to extract the version value from it (in the case above it's- 4.0.24.0).

View 3 Replies


ADVERTISEMENT

Hoe Do I Extract?

Mar 15, 2007

Hello All,

I have a table that has the first name and last name together in one field (stupid I know). I want to extract the first name from the field and copy it into another field names, well, firstname. I figure it might be possible since there is a comma after the last name. But how can I do this?

So for:

STRNAME
-------------
adams, john
-------------
washington, michael
-------------

I want it to be like this

STRNAME | STRFIRSTNAME
---------------------------
adams | john
---------------------------
washington | michael

So I guess there would be two parts to the statement:

1. First I need to select or trim the strname field to the comma. BUT I don't want to just trim it- I want to copy it.

2. The next thing I need to do is to copy the lastname (the part that I trimmed) into a new field which I created called lastname.

Any help would be greatly appreciated!

View 10 Replies View Related

Extract First Name From Last Name

Nov 14, 2007

I posted this a while ago with no luck and I'm going to try it again:

I have a column called strlastname with contains the firstname and last name of a client. I want to separate out the first name and last name into into two separate fields. I figured maybe I can do it easier if I copied the column into the table. So now I have a column called strfirstname that contains the first name and the last name and a colmn called strlastname that contains the first name and last name. The lastname is seperated from the first name with a comma like this:

doe, john

any guesses?

View 1 Replies View Related

How To Extract One Name?

Apr 17, 2008



Hello I have seperated 1 name 2 name and 3 names. useing conditional split searching spaces in between names.

but while taking only one name in dervied colum what should i enter to get it.

i tried the same expression that i used in 2 namees and 3 names for first_name.

but its giving me error is there adifferent way to get one name.

le tme know

View 6 Replies View Related

Extract INT From String?

Feb 19, 2014

I have a table which has important information stored within a text field as nText. The fields look like this

{B:P:8}
{B:D:18}
{A:P:821}
{E:D:38}
{A:D:9}
{B:D:18}

The integer after the last colon & before the curly brace is the one I need to extract. Is there a way to do this in SQL?

I tried this at first, but the problem is that it's not very tolerant to the varying lengths of the integer.

,RIGHT(LEFT(CAST(bN.Note AS NVARCHAR(50)),6),2)

I use Microsoft SQL 2008

View 6 Replies View Related

How To Extract Row Corresponding To First Occurrence Of ID

Mar 4, 2014

I have a sample table as follows:

IDITEM_EFFECTIVE_DTITEM_TERMAMOUNT
106738118-Jan-142141-200
106738102-Feb-142141-274.82
108766810-Jan-1421411238
108766810-Jan-142141-1238
108766810-Jan-142141309.5
108766810-Jan-142141-309.5
109754020-Jan-142141-3814.16
109754025-Feb-142141-100
110337727-Feb-142141-200.6
110337731-Jan-142141-19.24

I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.

1067381, 1087668, 109754, 1103377.

How does one handle this. I have not been able to come up with the sql yet.

View 4 Replies View Related

Extract Data From DB2

Jan 22, 2008

I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV

SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
FROM table1
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM

View 2 Replies View Related

Extract SQL Queries

Feb 6, 2008

Hi All,

I am looking to extract all the queries from a table in sql to either excel or office.

I have used SELECT Qstring FROM OUQR to list them but now i have to copy each one individually and the paste them to another document.

Is there a better way to go about doing this....

Thanks for the help....

Cheers

View 4 Replies View Related

Extract Data From SQL 6.5

Jul 23, 2005

I need to get some data from an enormous, creaky old SQL 6.5 database.I know nothing about either the data schema (though I believe some sortof documentation exists), nor 6.5 for that matter, having come to SQLServer at 7.0.My clients need the data in comma delimited format.Please, can anyone suggest any possibilities? One thing that occurredto me might be to create an Access application, use an ODBC link to theSQL DB, and then leverage Access' not inconsiderable functionality toget the data out.Does anyone foresee any problems with this, or any better ways?Forever in your debt.Edward--The reading group's reading group:http://www.bookgroup.org.uk

View 2 Replies View Related

Extract Data

Jan 10, 2008

I have the following data example,
Street
Forest Ridge Dr
Whites St
Alba Rd

I wish to achieve the following,
Street StreetSuffix
Forest Ridge Dr
Whites St
Alba Rd

I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
PostCode, [State])
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
FROM Addr1

But i get the following result which is no good!

Street StreetSuffix
Forest Forest
Whites Whites
Alba Alba


What am I doing wrong in the Street & StreetSuffix Functions above?


I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.


In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))

SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))

SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))

View 6 Replies View Related

Extract Value From A Column That Contains XML?

Aug 4, 2015

'm trying to get the value from a column in a SQL Server table that contains XML but the type of the column is not XML it is TEXT.

I tried this:

SELECT
[Id],
[Request]
FROM
[Tracker]
WHERE
[Request].value('/Credit[1]/Loan[1]/LoanApp[1]/Applicant[1]/Personal[1]/Individals[1]/Individual[1]/GivenName[1]/FirstName[1]', 'nvarchar(50)') = 'Tom'

but I get this error :

Cannot find either column "Request" or the user-defined function or aggregate "Request.value", or the name is ambiguous.

I tried to cast the column like this:

select
CAST(CAST(Request AS NTEXT) AS XML).value('(/Credit[1]/Loan[1]/LoanApp[1]/Applicant[1]/Personal[1]/Individuals[1]/Individual[1]/GivenName[1]/FirstName[1]', 'nvarchar(50)')

from Tracker but with this I get this error:

XML parsing: line 1, character 15, A string literal was expected

View 4 Replies View Related

HOW TO EXTRACT THE FOURTH NAME

Apr 15, 2008



Hello,

How do we extract the FOURTH STRING OF NAME FROM A FULL NAME SEPERATED BY COMMAS


John, Baker, Mike, Joe


FIRST NAME --- SUBSTRING(AuthRequestor,1,FINDSTRING(AuthRequestor," ",1) - 1)

SECOND/MIDDLE---- SUBSTRING(AuthRequestor,FINDSTRING(AuthRequestor," ",1),FINDSTRING(AuthRequestor," ",2) - FINDSTRING(AuthRequestor," ",1))

LAST NAME---- SUBSTRING(AuthRequestor,FINDSTRING(AuthRequestor," ",2),LEN(AuthRequestor))


HOW TO GET FOURTH NAME---- ?????? JOE


View 3 Replies View Related

How To Extract Zip File

May 8, 2007

Is there a way to extract the zip file and use the textfile within it to transform data.



Please let me know



View 3 Replies View Related

How To Extract Rule

Nov 3, 2006

Hi,

How do I extract rules and it's value from a database?
I can extract the rules through view(sys.objects) but where can I get it's content?

Regards
Marcelo Gamba

View 4 Replies View Related

Extract SQL Select Statement

Nov 29, 2006

Hi,

View 1 Replies View Related

Extract The Month Between Two Given Dates

Dec 4, 2007

hii all
i am havie a database with given fileds like projectid,projectcode,projectname,startdat,enddate,etc....
 my problem is to show all months and year between each projects startdate and enddate in a datagrd..and the table dont have month, year field..
i need to use sql quiery..
 if anybody can help me in this matter.. it will be greatful..
thanks in advance..binoy

View 3 Replies View Related

Data Extract Question

Aug 5, 2005

Hi, I'm hoping someone has an idea or two on this topic. 
Basically I have three tables of data say tContact, tQuestion, tAnswer

tContact
-----------
ContactID
Email
Name

tQuestion
------------
QuestionID
Question

tAnswer
------------
QuestionID
ContactID
Answer

I need to extract the data for the client and they would like to see
the data with one line per contact, but showing every answer to every
question... they would like the data formatted like this:

ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc........

Obviously to get the data I cansimply do an outerjoin to get all
contact data then all questions, and answers that exist... but that
will obviously return tabular data with one row per each
answer...  Does anyone have any ideas on how to do this using just
SQL?  I can pull the data and write a function that spits it out
to text using the Stringbuilder class and some logic, but I'm thinking
this must be possible in SQL natively... any help would be more than
appreciated.  Thanks in advance.

-e

View 1 Replies View Related

Extract Value From Middle Of String

Sep 9, 2005

Lets say I have a column of type varchar and need to extract an integer value from the middle of it.  The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?

View 2 Replies View Related

Best Way To Extract Part Of A Db And Deploy It

Sep 24, 2005

Hi,I'm using MSSQL2k and I have some customers in a customer table. I want to write a webapp and offer them the possibility to teach them using the app while having telephone contact with them. They can use the app on my server for some time and add data to it.After a while when someone wants to buy the app I would like to distribute the app as an Intranet app to their own server and let them have the data they already have wrote into the db on my server.Because there will be some customers with much data I'd like some guidance in how to plan the database to easily extract their own data and create a new db polutated with their data.Kenneth

View 1 Replies View Related

Extract Value From A Lookup Table

Feb 23, 2006

Hi,
I'm inserting records into a Table1 with the following fields :
tabID int 4ClientID int 4..etc etc.
In the insertitem template, I want the user to enter a Clientcode into textbox1.  Then I want that client code validated against a lookup table, ClientTable and if valid, get the ClientId value from that ClientTable, so that I can populate the ClientID field in Table1.
I've got a mental block and I cannot see how I can do that !
Any ideas most gratefully appreciated.
Thanks
Sunil
 
 
 
 

View 6 Replies View Related

Anyone Know How To Extract Datetime From Timestamp?

May 8, 2007

Anyone know how to convert a timestamp value to datetime that I can read?

When I run this:


Code:


select * from trans
where convert(datetime , time_stamp) >= getdate()-1
--'time_stamp' is the name of the column



I get:


Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

(0 row(s) affected)

View 1 Replies View Related

How To Extract Data After The Comma

Oct 5, 2005

hi there
i have a field name(fil_srt_cond) with the values of

cmpnt_name,ASC,1,2
cmpnt_stuff,DESC,2,3

i used the char index:-

substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord,

substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len,

substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str


to display them in their specific fields like

NAME STAR_CHAR LENGTH ORDER
Tag 1 2 ASC

however for cmpnt_stuff,DESC,2,3 i'm getting
NAME STAR_CHAR LENGTH ORDER
Stuff , , DES

therefore i'm not getting the required values 2 and 3.
can u pls help me to find a way how to get the data after the comma. thank you in advance

View 4 Replies View Related

How To Extract Only 1 Record For Each Staff?

Oct 30, 2005

Hi,
I have more than 1 record for each staff. This is the sample of record,

StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3
50424 Data4 Data5 Data6
50424 Data7 Data8 Data9
80336 Data4 Data5 Data6

Is it possible for me to extract only 1 record for each staff? The result should be like this..

StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3

Any idea? I used DISTINCT in sql statement but it's not working because the record is unique.
Thanx in advance..

View 1 Replies View Related

Extract And Compare Hour

Jan 22, 2007

Hi all:

As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table

Thanks.:confused:

View 1 Replies View Related

SQL Query To Extract DB2data

Jan 23, 2008

Hi i have a query
select 12 * int( "SQLUSER"."BTT120".yyyy) + int("SQLUSER"."BTT120".mm) as actual,
12*year(CURRENT DATE) + month(CURRENT DATE) as ending,
12*year(CURRENT DATE) + month(CURRENT DATE)-3 as starting
from "SQLUSER"."BTT120"
where "SQLUSER"."BTT120".yyyy ='2007' and "SQLUSER"."BTT120".mm in ('10','11','12')

which gives me ourput

Actual ending Starting
24094 24097 24094

My actual requirment is to get the data for the rolling 3 months for which i used the same logic as above in the WHERE clause.


SELECT "SQLUSER"."BTT120".YYYY CONCAT "SQLUSER"."BTT120".MM AS MO_YR
FROM "SQLUSER"."BTT120"
where (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) between
(12*year(CURRENT DATE)) + month(CURRENT DATE)-3 and
(12*year(CURRENT DATE)) + month(CURRENT DATE)

everything looks good but iam not getting output, there is data for the date range

(12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) = 24094
(12*year(CURRENT DATE)) + month(CURRENT DATE)-3 = 24094
(12*year(CURRENT DATE)) + month(CURRENT DATE) = 24097

can anyone help me if iam wrong in syntax. new to DB2

Thanks

View 1 Replies View Related

How To Extract Content From Email

Feb 22, 2012

I want some way to extract email content from email that we send/receive in account.....

This question is in context to Text Analytic

View 4 Replies View Related

Extract Substring In Statement

Feb 12, 2015

I tried to extract the information in a field

Samples of the data:

"Current Date updated ( 12/31/2015 -> 12/31/2016 )"
"Status updated ( Open -> Should be Closed )"
From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";
example 2 --> need outputs of "Open" and "Should be Closed"

These are what all records look like:

1. It contains the word, "updated".
2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)
3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)
4. The last part is to captured everything after the "->" but before the close parenthesis ")"

View 1 Replies View Related

Extract (text To Column)

Apr 30, 2008

I have a column named DATEID in a table DATA that contains the
following format connected to each other(date and name):

20071030Jones
20071020Smith
20070918Rogers
20080122Williams
etc..

What would the syntax be to insert a column to the right of this one,
and extract the first 8 digits from the data in the DATEID column and
insert that into the new column DATE, therefore making it easier for
me to query against an actual date?

Thanks

View 2 Replies View Related

String Extract From Text

Jun 17, 2014

I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance.

Begin Quote Calculation<br />
<br />....<br />
Agent Id: 001<br />
Trip Duration: 5days<br />
Relationship Type: Individual<br />....nDestination: AreaTwo<br />
<br ...../>Resolved Trip Type To: 1 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 6.070000<br />.....Resolved Trip Type To: 2 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 9.070000<br />.....

Result

Trip Duration: 5 days
Destination: AreaTwo
Base Rate: 6.070000

View 3 Replies View Related

How To Extract Data In Two Table

Mar 15, 2007

I've two table.......
One table Name Form1
Ex
----------
Name|F1No
----|-----
23wa|1
xyzx|2
abcd|3
......
......
......
---------
F1No====>primary Key
Second table Name Form2
Ex
--------
F2No
----
3
1
2
2
2
1
......
......
......
--------
F2No===>Foreign Key

i want top 3 Name(field) in first table(Form1).

Result
------
xyzx
23wa
abcd

View 7 Replies View Related

Extract Procedure Code

Apr 2, 2007

hi anyone can tell me how to extract procedure code from database
nisar

View 4 Replies View Related

Extract And Load From And To Oracle

May 3, 2007

is it possible to extract from Oracle, transform (preferably in the buffer) and load data into a Oracle db using SSIS? is there any white paper/ article on the process?

View 1 Replies View Related

Extract From Multiple Sources

May 15, 2007

Hello Friends,
I need suggestion/help
I have to build a package using SSIS and the requirement is like this,
First I have to check the data source type(3 types for now)
then I have to select a query for each different source from a table (basically the connection string and query changes for each query)
and load the data into the dimension and fact tables.

I am not able to get the full picture of how I am going to achieve this, there is little complication to this one of the 3 sources has 15 servers from where i have to extract data
Hope I am clear enough

Appreciate any suggestions
Thank you

View 1 Replies View Related







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