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


ADVERTISEMENT

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

Extract Column From Second Table

Mar 11, 2008

Hi all

I am trying to teach myself SSIS and need some help, please.

I have SQL table A as my source with column X. I need to add column Z and populate it from SQL table B, column Y where table A column X >= table B column R and <= table B column S.

Thus: Populate A.z with B.y where A.x >= B.r and A.x <=B.s

The interval between B.r and B.s is unique and only one value will be returned.

Which is the best way to do this in SSIS?

Thanks,
NewToETL

View 1 Replies View Related

Fastest Way To Extract Values From An XML Column

Mar 5, 2008

 Hi, I have a table with one XML type column. This column holds custom field information. Its used as a way of storing ad hoc fields and data that don't fit the DB design. <?xml version="1.0"?><contact><Reference>A39390TFH</Reference><Misc>all kinds of stuff go in here</Misc></contact>I want to provide a way of displaying the data stored in this column in the same DataTable as normal relational data from the same table. I have been able to achieve this goal BUT I want to know if the community had any ideas on how I could speed the process. I am using the XML  value() function. It allows me to extract the data I need.    SELECT Name, Number, Reference FROM

(

SELECT Name, Number, xmlvalues.value( ' (contact/Reference)[1] ', ' varchar(40) ' ) as Reference

From MyTable

WHERE Name = 'Some Dude'

AND xmlvalues is not null

) T

GROUP BY Name, Number, Reference anyone know if there are better xml functions to get this data out of the XMLColumn??????  There is no schema, because each xml fragment has different tags and different values.  Regards Niall 

View 1 Replies View Related

SQL Server 2008 :: Extract Value From XML Column

Mar 12, 2015

I have table Called as ‘DC_BIL_ActivityLog’ and XML column name is ‘ActivityDescription’ in SQL Server 2012.

The following information stored on that Column. I want to read cancellation date (12/23/2015) using select statement.

<ActivityDescription>
<text value="PCN was initiated for Policy ^1 on 12/07/2015. Cancellation Date is: 12/23/2015. Amount needed to rescind PCN is: $XX.80." />
<link id="1" linkText="GLXXXP2015 12/02/2015 - 12/02/2016" linkType="policy">
<linkId parm="1" value="1140" />
</link>
</ActivityDescription>

View 8 Replies View Related

Extract The Value Of A Datetime Datatype From A DataSet Column

Nov 4, 2006

Hello,
I'm having trouble extracting the datetime value of a datacolumn in order to compare it's value to another datetime. The error I get is "unable to cast object of type System.Data.DataColumn to type System.DateTime. I'm sure I'm just missing a step or a cast, but nothing has worked. I hope someone can help!
Here's the code:
protected void ddlRooms_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EventBookingConnectionString"].ConnectionString);
SqlDataAdapter da;
DataSet ds;
try
{
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Events.ID as 'Event ID', Name as 'Event Name', Rooms.Room as 'Event Room', AttendeeList as 'Attendee List', EventDate as 'Event Date' from Events INNER JOIN Rooms on Events.Room=Rooms.ID";
ds = new DataSet();
da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "BookedRooms");
if (ds.Tables["BookedRooms"].Columns["Event Room"] != null)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string room = (string)row["Event Room"];
if (ddlRooms.SelectedItem.ToString() == room)
{
System.DateTime date = (System.DateTime)ds.Tables[0].Columns["Event Date"];   //HERE'S WHERE THE ERROR OCCURS
if (Calendar1.SelectedDate == date)
{
lblBookedRooms.Visible = true;
lblBookedRooms.Text = "I'm sorry, that room is booked for that day.<br />Please choose another day or a different room.";
}
}
}
}
}
catch (Exception ex)
{
lblResults.Text = ex.Message;
}
}
Thanks in advance to anyone willing to help out!
Monster

View 8 Replies View Related

How To Extract Part Of A String In Column Results

Jul 21, 2015

I want to extract a number within a column results, that is the number between the first two commas

Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....

How to extract the second number(between the first two commas) from the above results?

The output should be

1158286
1158289

View 2 Replies View Related

Extract Month & Year From Date Column

Feb 6, 2008

hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

View 11 Replies View Related

SQL Server 2012 :: How To Extract One More Piece Of Data From The Column

Dec 17, 2014

I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:

CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,

[Code] ....

All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:

CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));

I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...

I can retrieve all the column names that are in uppercase using this:

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;

but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):

ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade

[code]....

The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.

So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?

View 5 Replies View Related

Transact SQL :: Extract ID And Specific Part Of A String From Column

Jun 10, 2015

I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.

I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)

The row sample looks like the following

I have to extract the Id and a part of the column from mystring.

Id      MyString
1      ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|

[Code] ....

The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following

Id      DesiredString
1      Extracts^This^String1
2      Extracts^This^String2
3      Extracts^This^String3

Is there a way to extract this either using TSQL or SSIS.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,

[Code] ....

View 4 Replies View Related

Extract Strings From Search Path Column In Audit Table

Jul 22, 2013

I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.

Column searchpath has value like:

//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model

I need to extract the folder names and display it in separate columns in a new table this way:

AFR AFRABC CDD Packages Deployment XXX spend name

View 6 Replies View Related

T-SQL (SS2K8) :: Extract Quoted Text Elements From Varchar Column

Nov 18, 2014

I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.

declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values

[Code] ....

Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III

I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.

Unfortunately, CLR is not an option.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Extract Strings Saved In Column Into HTML Files

Oct 12, 2015

I am working in an environment where i inherited bad design. I have a column in a table. This col contains huge HTML files. We are mostly reading these files and very little updates.

I am changing this whole architecture and going to using Azure Blob storage. I am stuck right now. I need to extract these html strings and save them into separate files in year/month/day/filename.html format. I have another column in the table which has create date saved in it.

I am planning to import all these files into BLOB storage and save link in the table.

1) How can i extract these strings from the table and save them in the year/month/day/filename.html directory/sub-directory format

2) How to import these files into BLOB storage.

View 1 Replies View Related

Extract Column Data From Flat File Error Output

Dec 10, 2007

Hi Friend,


I am stuck with a problem and need your help. As we know, all columns that go to error flow of flat file source connection are displayed as a single column e.g. FlatFileSourceErrorOutputColumn, but my requirement is to extract the first column value from this FlatFileSourceErrorOutputColumn, my data is dilimeted by "|" pipe operator. I have created a script component to deal with this. However if we take FlatFileSourceErrorOutputColumn column as input column in script component, it comes as BLOB data. I wrote below code in transformation script component to extract BLOB data from column in string form and then do a Left function search to take first column out.

When I am running this script component I am getting '??????????' question marks as a result in Row.Pname.

Can anyone please help me understand if I am doing anything wrong in this script or suggest a better way to take the data out?

I appreciate your help.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

'

Dim Error_Data As String

Dim Column_1 As String

Dim Len As Integer

Dim Buffer As Byte()


Buffer = Row.FlatFileSourceErrorOutputColumn.GetBlobData(0, CInt(Row.FlatFileSourceErrorOutputColumn.Length))




Error_Data = System.Text.Encoding.Unicode.GetString(Buffer)


Len = Error_Data.IndexOf("|")


Column_1 = Left(Error_Data, Len - 1)



Row.Pname = Column_1

End Sub


Thanks,
Kul

View 5 Replies View Related

Transact SQL :: How To Extract Dates From A Single Column Based On Conditions

Oct 13, 2015

The data I have is as follows -

ID1    ID2     Date            Action
100   500     09/08/14     Open
100   500     09/24/14     Close
101   510     07/10/15     Open
101   510     07/19/15     Close

The output I want in a single result set is -

ID1    ID2     Open_Date   Close_Date
100    500    09/08/14      09/24/14
101    510    07/10/15      07/19/15

Any way to do this in T-SQL .

View 10 Replies View Related

Extract Date Part Of Timestamp Column And Convert It To Char Field

Nov 28, 2012

Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?

The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.

convert(char(15), process_date,112) as process_dt

View 3 Replies View Related

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 View Related

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

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







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