Complex XML Parsing/Shredding
Jun 20, 2006
Hi,
I have generated a sample XML document from the XSD file using XMLSpy and used these in XML Source stage.
While I was parsing/Shredding the XML file and to write to sequential files I got the following error message.
The XML Source Adapter does not support mixed content model on Complex Types.
Additional Information:
Pipeline component has returned HRESULT error code )xC02092A1 from a method call. [Microsoft.SqlServer.DTSPipelineWrap]
Can anybody give a sample code with Complex XML type.
-----------------------------------
Find attached the XSD format. This is FYI.
View 4 Replies
ADVERTISEMENT
Jan 28, 2015
I have an xml file that I need to read out into columns and I'm having a problem with the section that has the persons address in it, a snippit of the file is shown below:-
DECLARE @xml XML
SET @xml = '
<PersonStructure>
<PersonName>
<GivenName>Mike</GivenName>
<FamilyName>Myers</FamilyName>
[code].....
Now the problem I am getting is, it is pulling out all the fields except it repeats the first part of the 'PersonAddress' field 3 times instead of giving me the whole address?
Is there a way of gaining access to all the parts within the 'PersonAddress' node even though all 3 parts within it are named the same (Address)?
View 2 Replies
View Related
Nov 28, 2007
Hello,
I have a file that looks like this:
Summary
A ABCD
A Category MarketValue Margin
A category1 1.0000000 1.000000
A category2 2.0000000 2.000000
H Totals Total Cash Net
H 2.00000 200000 2000000
Another Summary
B BCDE
B Activity MarketValue Margin
B activity1 3.00000 3.000000
B activity2 4.00000 4.000000
The items in blue are headers. I don't want to capture those. However, I want to capture all the data in black, and put it into 3 separate tables (or maybe the same table, under the appropriate column names)
This situation differs from anything I've done before in that you can't identify what row contains what data by what's in the row itself. That is, what's in the data rows is random and subject to change. So you can't search the row itself to determine which table it goes to.
However, if there's a way to capture all the rows after a certain header before the header changes again, that might work.
That is, get all rows between A Category MarketValue Margin and H Totals Total Cash Net
and
get all rows between H Totals Total Cash Net and Another Summary
and
get all rows after B Activity MarketValue Margin
Any examples of how I might script this?
Thanks
View 2 Replies
View Related
Feb 14, 2006
I have a package that starts by loading a recordset into an object variable. The recordset is then enumerated with a ForEach loop. The loop sets some string variables. Within the loop container I have a Script task that uses a MsgBox to show the results for testing purposes. The package uses checkpoint restart (if that matters?).
The first time I run the package the 1st record is displayed in the MsgBox, then the 2nd, but then the loop is stuck on the 2nd record forever. I break the run, and when I rerun it the 1st record is displayed followed by each subsequent record correctly and the package completes successfully. Now, if I were to run again the same problem would occur on rec 2 and I would have to break the run, and then the next run everything would work fine.
Why does the script get caught in an infinite loop the first time it's run, but works fine when restarting from the checkpoint?
Here's my relevant code:
ForEach: Enumerator=ADO Enumerator, Enumeration Mode=Rows in first table
Public Sub Main()
Dts.Variables("User::SQL1").Value = "SELECT src.* FROM " & Trim(Dts.Variables("User::Src1Tbl").Value.ToString)
MsgBox("sql=" & Dts.Variables("User::SQL1").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
View 8 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Jul 24, 2002
I am trying to process an XML document that contains the attribute 'from_x'. However an openxml query can't seem to find any column with a '_x' suffix. For example if I were to execute the following fragment:
declare @hDoc int, @Message varchar(200)
select @Message = '<BACK_FM from_x="12"></BACK_FM>'
exec sp_xml_preparedocument @hDoc OUTPUT, @Message
select from_x from openxml(@hDoc, 'BACK_FM',1) with (from_x int)
I get back a null value from the openxml query. Attribute names 'fromx' and 'from_y' work ok but nothing I have tried with a trailing '_x' will work.
Does anyone know if this is a known SQL Server bug? Is it a bug at all or something about XML that I don't know about?
Thanks,
Wayne King
View 1 Replies
View Related
Aug 13, 2001
I need to figure out how to parse a comma separated value.
Lets say I have a variable equal to a comma separated list.
SET @Variable = '045, 032, 025, 653'
I need to create a dynamic sql string to look like:
SET @Variable = ''' + '045' + ''' + ',' + ''' + '032' + ''' + ',' etc...
Can someone teach me a optimized query to do this?
Thanks
Greg
View 1 Replies
View Related
Mar 9, 2005
What is parsing?? can someone give me an example please?? this what I got from BOL
Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name.
View 11 Replies
View Related
Feb 8, 2007
Hi -
I am new to SQL server and was wondering if someone can help me with this one. Thanks
My table holds 2 columns (SECTOR and TERM) with following example values
SECTOR TERM
Hybrid 6/18
Hybrid 9/19
Hybrid 10/17
Hybrid 3/13
I would like to find out the rows where my values from SECTOR before '/' does not equal TERM
i.e.
Row 1 where 6<>8
and row 3 where 10<>7
Thanks.
View 5 Replies
View Related
Jul 6, 2007
Can you parse a SQL field? Let's say, FULLNAME field got a TEXT datatype with the following data: <firstname>Norm</firstname><lastname>bercasio</lastname><Color>blue</color>then using a select statement, parse the field to find the lastname then write it to another field called LASTNAME on the same table, same rowID. Can you send a select statement how it can be done? I am using SQL 2003 or 2005. thank you so much.
View 2 Replies
View Related
Jul 22, 2004
Hi All,
I'm using a SQL selection to fill a DataGrid. One of the fields I have is called diagnosis. This field in the database can contain multiple diagnosis. But I use a set of characters to divide each diagnosis.
Example : Sick!@#$%Hurt!@#$%Ill!@#$%
My problem is this is how it looks in my Data Grid. Can someone tell me how to parse out each diagnosis.
Thanks
View 1 Replies
View Related
Jun 7, 2002
How to remove same repeated string in a column per row from a table? Looked at
replace, stuff string functions, but none take a column name as a parameter.
Help is appreciated.
Thanks,
View 1 Replies
View Related
Apr 21, 2000
Do anyone know of any functions I can use to parse the following data eg.
M 3480-7 should be 3480
M 3477-19 should be 3477
M 28-10 should be 28
Thanks in advance,
Vic
View 1 Replies
View Related
Aug 18, 2000
Anybody out there ever take a column containing names and parse it out to salutation, first name, middle initial/name, last name, suffix using Transact-SQL? I think I know how to do it using an array in a procedural language, but using SQL I'm drawing a blank.
Any ideas or help will be appreciated!
View 1 Replies
View Related
Feb 27, 2001
I have a varchar field that contains answers to questions separated by commas. Say there are 4 questions for each user. Here is an example of what the table would look like:
User Answer
1 Good,Fair,Good,Bad
2 Bad,Good,Good,Good
3 Fair,Good,Bad,Fair
I need to write a stored procedure to report off of that separates the Answer field into 4 different columns. How can this be achieved? Any assistance would be greatly appreciated.
View 1 Replies
View Related
Nov 7, 2003
I have a SQL Server table that holds XML documents. Is there a known SQL Server XML parser ?....how can I export XML data into a readable format ?
thanks
View 1 Replies
View Related
Sep 13, 2005
Does anyone know any good URL's for examples on parcing data using SQL?
As an example, i've got First/Middle/Last name of a person inside a single field, I want to turn that into 3 fields.
Thanks!
Caden
View 8 Replies
View Related
Nov 9, 2004
Hi guys!
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
thanks
nelo
View 2 Replies
View Related
Mar 20, 2006
Hello everyone
Heres what it looks like:
I have a large file of over 40k email records. The emails are all mixed up and come in various formats but i noticed that most of them are in this format:
firstname.lastname@email.com
firstname.middlename.lastname@email.com
For all those emails with the period (.) in between, the (.) actually separates an individuals first and last name.
My task is this, to separate all the emails that are in this format into first and last name fields. I'm stimped folks and I'll really appreciate any pointers or ideas on how to go about solving this task.
Thanks
View 1 Replies
View Related
Aug 9, 2012
I have a name field that has many variations and been fighting with it for a few days. The possible name variations are:
Smith, Mike
Smith, Mike K.
Smith, Mike K., JR.
I would like to parse each of the for pieces in a separate field.
View 3 Replies
View Related
Feb 13, 2004
Parsing Address
This is not really a reply, but I saw the problem and the replies look very promissing.
I'm using ss2k, I have a table with an address column.
here is some example of the records under ADDRESS :
WILLOW CREEK PL
RED BARN DR
RED BARN DR
CARRINGTON DR
RENNER RD
EDMONTON CT
SPRINGBRANCH DR
HILLROSE DR
CEDAR RIDGE DR
LARTAN TRL
PRESIDENT GEORGE BUSH HWY
What I want to do is to write script that runs daily and parse the street names (RED BARN) and street types (Dr, PL , etc.. ) to 2 colums. As u can see there is no fixed length or fixed number of words ...etc ...
Any help would be really appreciated.
thnks
View 3 Replies
View Related
Apr 12, 2008
I found an article about string parsing but its done using db2
http://www.ibm.com/developerworks/db2/library/techarticle/0303stolze/0303stolze1.html
can anybody translate to Transact SQL specifically the example of create function elemIdx i didnt understand how he used recursion may b cuz the language is odd to me i didnt get it
Thanks much
View 1 Replies
View Related
Apr 18, 2008
I have a string that is coming from a legacy system
###T1937###U1875###U1960###U3287###U5926###U6388###U4408###T1909###U2620###U5025###U6354###U7072###U7074###U6715###U6714###U4085###U6441###U7067###U7073###U2392###U6348###U7758###U6717###U7755###U7069###U7756###U6350###U6760###U7070###D0002###D0001###U6238###U6349###U6353###U6355###F0005###U7750###U6351###U7757###
How can I convert above string to comma separted values
like one below so that it can be used for IN Clause for my SQL
'T1937','U1875','U1960','U3287','U5926','U6388','U4408','T1909','U2620','U5025','U6354','U7072','U7074','U6715','U6714','U4085','U6441','U7067','U7073','U2392','U6348','U7758','U6717','U7755','U7069','U7756','U6350','U6760','U7070','D0002','D0001','U6238','U6349','U6353','U6355','F0005','U7750','U6351','U7757'
Thanks in advance
View 17 Replies
View Related
Aug 1, 2006
hi, im running into a bit of a problem. i will like to string parse the street name into what you see below using DTS. How can i get around this?
12 north plaza boulevard apt.16
12|N|plaza blvd| apt16
View 10 Replies
View Related
Dec 11, 2006
Hello everyone, I have this SP and can't get it to work on my SQL2000 server. I just can't seem to figure out what syntax error I am making.
(This works on my test SQL2005 server)
Truncate Table [SecurityEvents_Temp]
Insert Into SecurityEvents_Temp (
[BankNumber],
[UserName],
[DomainName],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data]
)
Select
[BankNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3)
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[SecurityEvents].*
FROM [SecurityEvents]
JOIN [EventsToLog] on [SecurityEvents].[EventID] = [EventsToLog].[EventID]
WHERE [SID] NOT LIKE 'S-%'
Query Output:
quote:
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near 'Strings'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'Strings'.
Thank You,
John Fuhrman
http://www.titangs.com
View 16 Replies
View Related
Jun 19, 2007
Hi,
I have a name field that has fullname in it and i need to parse it to firstname, lastname, middle initial and suffix.
I used the below query to parse the Last Name and first name.
SELECT
SUBSTRING([FullName], 1, CHARINDEX(' ',[FullName]) - 1)AS LastName,
SUBSTRING([FullName], CHARINDEX(' ',[FullName]) + 1, LEN(FullName)) AS FirstName
FROM CustomerTbl
There are middel initials and suffix present in the full name. How do i parse the middle initial and suffix.
The Sample Name that appear in the table:
JONAS VICKY ==> Format without Middle Initial or suffix
MAYES MARY T ==> Format with middle initial
MCGEE, III CLIFTON ==> Format with suffix
Pls advise
Thanks
View 6 Replies
View Related
Mar 5, 2008
Table1 includes a column for email address. I'd like to parse everything up to 8 chars before the '@' symbol in this column. My script doesn't appear to be working, and I'd appreciate any assistance in troubleshooting it!
select substring(UPPER(substring(email,1,charindex('@',email)-1)),1,8)
from table1
The error produced is;
Invalid length parameter passed to the substring function.
What am I doing wrong? The logic in the script looks good to me...
View 7 Replies
View Related
Jan 26, 2006
Hi,I have three tables in the following structure (simplified):Table 1: Containing the customers-------------------------------------------------create table Customers([cusID] int identity(1, 1) not null,[cusName] varchar(25) not null)Table 2: Containing the customer data fields---------------------------------------------------------------create table Data([datID] int identity(1, 1) not null,[datName] varchar(25) not null,[datFormula] varchar(1500))Table 3: Containing the customer data values-----------------------------------------------------------------create table Values([cusID] int not null,[datID] int not null,[valValue] sql_variant)In this structure the user can add as many data fields to a customer ashe wants (e.g. Country, City, Email, Phone, ...). I have added triggerswhich create a view similar to a pivot (I am working in SQL 2000) andadd triggers to the view so it is insertable, deletable and updateable.What I would like to do, is allow the user to create new fields wherethe values are based upon a calculation. This calculation would be donethrough a formula similar to what he would do e.g. in excel (thisformula is stored in the dimFormula field then).An example might help. Let's assume the user created a field 'Sales'(containing last year's sales) and 'Invoices' (containing the number ofinvoices that were created for him last year). Now, he wants to createa field 'AvgSales' with the formula '[Sales]/[Invoices]'.(Note that through adding these data fields, the above view was created(let's assume it is called vw_Customers and contains the columns [ID],[Name], [Sales], [Invoices], [AvgSales]).What I am looking for is a function which can parse this formula into at_sql query which runs the calculation. So, the formula'[Sales]/[Invoices]' would be translated into (let's assume there areno records with NULL or zero invoices):update vw_Customersset [AvgSales] = [Sales]/[Invoices]from vw_CustomersI am able to do the above with simple calculations (where you can evenuse sql functions e.g. year, len, ...). Now I would like to take thisone step forward into the possibility of using functions with morevariables.For example. Let's assume, the user wants to add a rating (field called'Rating') to his customers based upon the result of 'AvgSales. Heenters the formula 'if([AvgSales] > 2500, 'A', 'B')'.If anyone could help me on this, I would be very grateful. Thanks.M
View 3 Replies
View Related
May 13, 2007
hi..i need help for my exam, I retrived data from my sms inbok andinsert to table.The record just like this:ID1,12x100,56x12,90x90..,how to pars the table in to:ID First SecondID1 12 100ID1 56 12ID1 90 90Thank for your help...===================
View 3 Replies
View Related
Dec 3, 2007
Hi:
I need to parse an regularly outputted rtf file and was wondering if it is possible in SSIS. I am trying to use the flat file connection manager to do this.
Now, I can't treat tab stops in an rtf like tab stops in a csv, since when you treat an rtf as a text file, you see the format code of the rtf. If I open the rtf in a text editor, the entire file is one line, with lines breaking with:
par}
Columns are tab delimited in the rtf, and they look like this when you treat the rtf as a text file.
plain abfs16f4cf0cb1
(or something like that, the word "tab" is the important part.)
So I use the "plain ab" part to delimit in SSIS, since that is consistent (planning to parse out all the garbage later on). The problem is, sometimes lines don't have a "city" and "state", so it "tabs" right over to the next field. So like this (looking in MS Word):
Phone <tab> City <tab> State <tab> Date <tab> Other fields.....
847-111-2222 <tab> Omaha <tab> NB <tab> 9/14/2007 <tab>
222-222-3333 <tab> 9/14/2007 <tab>
555-121-1212 <tab> Houston <tab> TX <tab> 9/14/2007 <tab>
Now, if you treat an RTF as a text file, it has only one "plain abfs16f4cf0cb1" after the phone number, so even for the missing line there is only one tab, not 3. This is because in the beginning of the row tabs for each row are defined like this:
tql x90 ql x840 ql....etc...
with "tql" and "tx" tags basically saying where all the tab stops are for that row. So for the row above with missing info, it lists fewer tab stops. So the "date" (and associated garbage) ends up under "City" for this row. All of the "Houston" row's data starts appearing in the sql server output table's 2nd last field, as you might expect.
Any suggestions how to pull this in in SSIS during the transformation? I could deal with it after I pull it in, I still have all the data. I'm thinking the logic to do this could be complicated though. I take the data out of the last two fields of the missing row into some other table, use UPDATES to shift the values 2 fields to the right, and then figure out a way to take the data I just put in a temp table back in, but it all sounds a bit complicated.
Let me know if this makes sense--I've almost got it going, I just need to sort this last bit out.
Thanks,
Kayda
View 4 Replies
View Related
Jul 11, 2006
I have a series of strings (field name FullName) in a table (FullNames) that look like this:
FullNames
--------------
macdonald, ronald
Doe, John
I need to extract the first and last names. Here's my code:
select substring (fullname, 1, patindex( '%,%', fullname)-1) + ' ' +
substring (fullname, patindex( '%,%', fullname)+1, len(fullname) ) from FullNames
I hate having to use patindex twice in the same SELECT. Is there any way around this?
TIA,
Barkingdog
View 8 Replies
View Related
Jul 29, 2015
Having never used SQL for anything serious I have been asked to look at importing multiple data files into a database and have come to a dead stop. The XML file is a repeating structure with but the number of <SampleVial>s and the number of <Particles>s in each file is a variable...example shown below
<AcquisitionIntervals>
<SampleVial id="1">
<AcquisitionInterval>
<StartOfIntervalUpdate>
<Id>0</Id>
[code]....
I need to pull all of the particle data associated with each <SampleVial>.
View 3 Replies
View Related
Nov 13, 2006
I want to write a program that works like SQL log rescue or ApexLog or one of the many other programs that are too expensive to buy. I would like to ask the question, does anyone know how the format of the log works? Or how can I parse the binary file into readable text? Can I contact Microsoft about this?
Please do not reply and tell me to buck up and buy one of the 3rd party software packages available.
View 1 Replies
View Related
