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