May 31, 2011 at 1:38 pm
I am trying to extract the date between <trcdatebeg> and </trcdatebeg> from a string type column called wfdbody.
I am using following code to do this:
cast(substring(wfdbody,(charindex('<trcdatebeg>',wfdbody)+12),((charindex('<trcdatebeg>',wfdbody)+((charindex('</trcdatebeg>',substring(wfdBody,charindex('<trcdatebeg>',wfdbody),35)))-1))-(charindex('<trcdatebeg>',wfdbody)+12)))as datetime)
when i run this code it is giving me error message 'Invalid length parameter passed to the SUBSTRING function'.
I am unable to find out what is wrong with my code. Please help!! Could some one let me know what's wrong with my code?
Below is the sample data for the column :
<rule> <date>1/7/2005 10:12:43 AM</date> <properties> <RuleName>TrainingEnrollmentSave</RuleName> <DebugLevel>3</DebugLevel> <DBName>BNA</DBName> <DBServerName>sa3sql03bv.sa.ito.ceridian.com</DBServerName> <username>005846</username> <CompanyName>bna</CompanyName> </properties> <params> <row><ConfirmSave>N</ConfirmSave><SessionID>04BB3BD44B0242FCA644FF45AD73A19D</SessionID><lEbFlxID>86606</lEbFlxID><lDkjFlxID>12668603</lDkjFlxID></row> </params> <data> <row><RuleName>TrainingEnrollmentValidateSave</RuleName><OldTrbName>RSS Tecnologies</OldTrbName><OldRequestDate>1/7/2005</OldRequestDate><ReturnTo>frmTrainingClass</ReturnTo><FormName>FrmTrainingClassInfo</FormName><QryString>FrmTrainingClassInfo&CompanyName=bna&TrcFlxID=2440002</QryString><MultiRow>Y</MultiRow><TrcFlxID>2440002</TrcFlxID><OldTrcPerSeatFee>0</OldTrcPerSeatFee><EditMode>Y</EditMode><TrsDate0>2/24/2005</TrsDate0><TrsTimeEnd0>02:00 PM</TrsTimeEnd0><TrcDateBeg>2/24/2005</TrcDateBeg><TrcDateLateFee>2/24/2005</TrcDateLateFee><TrcPerSeatFee>0</TrcPerSeatFee><RequestDate>1/7/2005</RequestDate><lEmpFlxID></lEmpFlxID><Oldprimaryheader1></Oldprimaryheader1><Oldprimaryheader2></Oldprimaryheader2><TrbCode>EDT525</TrbCode><ConfirmSave>N</ConfirmSave><TrsTimeStart0>01:00 PM</TrsTimeStart0><InstName0>Laura Gordon-Murnane</InstName0><Rows>1</Rows><InstName>Laura Gordon-Murnane</InstName><TrcDateEnd>2/24/2005</TrcDateEnd><Location0>North-397</Location0><TrbName>RSS Tecnologies</TrbName><Location>North-397</Location><RowStart>0</RowStart><OldInstName>Laura Gordon-Murnane</OldInstName><TrcLateFee>0</TrcLateFee><OldTrbCode>EDT525</OldTrbCode><OldTrcLateFee>0</OldTrcLateFee><OldReturnTo></OldReturnTo><OldLocation>North-397</OldLocation><OldTrcFlxID>2440002</OldTrcFlxID><OldTrcDateBeg>2/24/2005</OldTrcDateBeg><OldTrcDateEnd>2/24/2005</OldTrcDateEnd><OldTrcDateLateFee>2/24/2005</OldTrcDateLateFee><SaveRedirect></SaveRedirect></row> </data> </rule>
May 31, 2011 at 1:58 pm
OK, it's bending my head trying to actually work out which parameter is responsible (given the length of the strings being manipulated!), but essentially one or both of your Start position and length values that you're passing to substring() are working out to be negative. It's probably the length, I'd say, since that seems to be doing the most computation on the string data.
May 31, 2011 at 2:21 pm
I have to ask, why bother with this complex method in the first place.
This seems easier:
DECLARE @XML XML = '<rule> <date>1/7/2005 10:12:43 AM</date> <properties> <RuleName>TrainingEnrollmentSave</RuleName> <DebugLevel>3</DebugLevel> <DBName>BNA</DBName> <DBServerName>sa3sql03bv.sa.ito.ceridian.com</DBServerName> <username>005846</username> <CompanyName>bna</CompanyName> </properties> <params> <row><ConfirmSave>N</ConfirmSave><SessionID>04BB3BD44B0242FCA644FF45AD73A19D</SessionID><lEbFlxID>86606</lEbFlxID><lDkjFlxID>12668603</lDkjFlxID></row> </params> <data> <row><RuleName>TrainingEnrollmentValidateSave</RuleName><OldTrbName>RSS Tecnologies</OldTrbName><OldRequestDate>1/7/2005</OldRequestDate><ReturnTo>frmTrainingClass</ReturnTo><FormName>FrmTrainingClassInfo</FormName><QryString>FrmTrainingClassInfo&CompanyName=bna&TrcFlxID=2440002</QryString><MultiRow>Y</MultiRow><TrcFlxID>2440002</TrcFlxID><OldTrcPerSeatFee>0</OldTrcPerSeatFee><EditMode>Y</EditMode><TrsDate0>2/24/2005</TrsDate0><TrsTimeEnd0>02:00 PM</TrsTimeEnd0><TrcDateBeg>2/24/2005</TrcDateBeg><TrcDateLateFee>2/24/2005</TrcDateLateFee><TrcPerSeatFee>0</TrcPerSeatFee><RequestDate>1/7/2005</RequestDate><lEmpFlxID></lEmpFlxID><Oldprimaryheader1></Oldprimaryheader1><Oldprimaryheader2></Oldprimaryheader2><TrbCode>EDT525</TrbCode><ConfirmSave>N</ConfirmSave><TrsTimeStart0>01:00 PM</TrsTimeStart0><InstName0>Laura Gordon-Murnane</InstName0><Rows>1</Rows><InstName>Laura Gordon-Murnane</InstName><TrcDateEnd>2/24/2005</TrcDateEnd><Location0>North-397</Location0><TrbName>RSS Tecnologies</TrbName><Location>North-397</Location><RowStart>0</RowStart><OldInstName>Laura Gordon-Murnane</OldInstName><TrcLateFee>0</TrcLateFee><OldTrbCode>EDT525</OldTrbCode><OldTrcLateFee>0</OldTrcLateFee><OldReturnTo></OldReturnTo><OldLocation>North-397</OldLocation><OldTrcFlxID>2440002</OldTrcFlxID><OldTrcDateBeg>2/24/2005</OldTrcDateBeg><OldTrcDateEnd>2/24/2005</OldTrcDateEnd><OldTrcDateLateFee>2/24/2005</OldTrcDateLateFee><SaveRedirect></SaveRedirect></row> </data> </rule>'
SELECT @XML, @XML.value('(/rule/data/row/TrcDateBeg/text())[1]','varchar(100)');
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 2, 2011 at 6:02 am
Thanks all for the reply. I was able to pin point where problem is. In the query belowe if i remove Cast() part the query works without any errors. ANy ideas? If i put Cast() back in the query it again gives me same error : 'Invalid length parameter passed'. ANy ideas on what is causing this?
Below is the query again:
cast(
substring(wfdbody,(charindex('<trcdatebeg>',wfdbody)+12),((charindex('<trcdatebeg>',wfdbody)+((charindex('</trcdatebeg>',substring(wfdBody,charindex('<trcdatebeg>',wfdbody),35)))-1))-(charindex('<trcdatebeg>',wfdbody)+12)))
as datetime)
If I comment out cast as datetime, it works fine. Any help please.
Thanks
June 2, 2011 at 6:17 am
Do it in two steps.
Parse out the value, into a temp table or a variable, then convert it to datetime.
Or skip all that and use XQuery as per my example. Simpler, easier, and designed to do what you're trying to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 2, 2011 at 7:37 am
Hi GSquared,
I was going to suggest a minor change to your code to: (change the varchar(100) to datetime)
DECLARE @XML XML = '<rule> <date>1/7/2005 10:12:43 AM</date> <properties> <RuleName>TrainingEnrollmentSave</RuleName> <DebugLevel>3</DebugLevel> <DBName>BNA</DBName> <DBServerName>sa3sql03bv.sa.ito.ceridian.com</DBServerName> <username>005846</username> <CompanyName>bna</CompanyName> </properties> <params> <row><ConfirmSave>N</ConfirmSave><SessionID>04BB3BD44B0242FCA644FF45AD73A19D</SessionID><lEbFlxID>86606</lEbFlxID><lDkjFlxID>12668603</lDkjFlxID></row> </params> <data> <row><RuleName>TrainingEnrollmentValidateSave</RuleName><OldTrbName>RSS Tecnologies</OldTrbName><OldRequestDate>1/7/2005</OldRequestDate><ReturnTo>frmTrainingClass</ReturnTo><FormName>FrmTrainingClassInfo</FormName><QryString>FrmTrainingClassInfo&CompanyName=bna&TrcFlxID=2440002</QryString><MultiRow>Y</MultiRow><TrcFlxID>2440002</TrcFlxID><OldTrcPerSeatFee>0</OldTrcPerSeatFee><EditMode>Y</EditMode><TrsDate0>2/24/2005</TrsDate0><TrsTimeEnd0>02:00 PM</TrsTimeEnd0><TrcDateBeg>2/24/2005</TrcDateBeg><TrcDateLateFee>2/24/2005</TrcDateLateFee><TrcPerSeatFee>0</TrcPerSeatFee><RequestDate>1/7/2005</RequestDate><lEmpFlxID></lEmpFlxID><Oldprimaryheader1></Oldprimaryheader1><Oldprimaryheader2></Oldprimaryheader2><TrbCode>EDT525</TrbCode><ConfirmSave>N</ConfirmSave><TrsTimeStart0>01:00 PM</TrsTimeStart0><InstName0>Laura Gordon-Murnane</InstName0><Rows>1</Rows><InstName>Laura Gordon-Murnane</InstName><TrcDateEnd>2/24/2005</TrcDateEnd><Location0>North-397</Location0><TrbName>RSS Tecnologies</TrbName><Location>North-397</Location><RowStart>0</RowStart><OldInstName>Laura Gordon-Murnane</OldInstName><TrcLateFee>0</TrcLateFee><OldTrbCode>EDT525</OldTrbCode><OldTrcLateFee>0</OldTrcLateFee><OldReturnTo></OldReturnTo><OldLocation>North-397</OldLocation><OldTrcFlxID>2440002</OldTrcFlxID><OldTrcDateBeg>2/24/2005</OldTrcDateBeg><OldTrcDateEnd>2/24/2005</OldTrcDateEnd><OldTrcDateLateFee>2/24/2005</OldTrcDateLateFee><SaveRedirect></SaveRedirect></row> </data> </rule>'
SELECT @XML, @XML.value('(/rule/data/row/TrcDateBeg/text())[1]','datetime');
... but I can't test it... I'm getting an XML parse error Msg 9411, Level 16, State 1, Line 1
XML parsing: line 1, character 716, semicolon expected
Maybe this is why the OP is intent on doing the parsing...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2011 at 7:49 am
It has ampersands in the data. There needs to be a replace step to clean it into real XML.
Whomever designed the database he's using, they made a number of mistakes. First, storing XML as a string. Then using non-standard XML (escape characters incorrectly stored). Those can be cleaned up using some simple string functions.
Even with that, it's nowhere near so complex as parsing the string using substring methods.
Imagine having to review this code a year from now! It'll be a major pain to have to maintain or extend it, if the substring methods are used. If an XML clean-up and conversion is used, then XQuery is used to parse it, it'll be very easy to maintain/extend.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply