March 12, 2015 at 4:17 pm
Hi
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 .Can some body help on this?
<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>
Thanks
March 12, 2015 at 6:10 pm
Here it is extracted as a character string:
DECLARE @XML XML =
'<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>';
SELECT CancellationDate=SUBSTRING(s, CHARINDEX('Cancellation Date', s)+20, 12)
FROM
(
SELECT s=a.TextOfActivityDescription.value('@value[1]', 'VARCHAR(8000)')
FROM @XML.nodes('ActivityDescription/text') a (TextOfActivityDescription)
) a;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 13, 2015 at 1:26 am
Thanks for your reply.
The above solution will extract Cancellation date from Static XML. But I need to retrieve Cancellation date from Table which contains more than one row.So I have modified the above query as below. But got error as
'Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'
DECLARE @XML XML =(Select ActivityDescription from DC_Bil_ActivityLog Where ActivityTypeCode='PCN')
SELECT CancellationDate=SUBSTRING(s, CHARINDEX('Cancellation Date', s)+21, 11)
FROM
(
SELECT s=a.TextOfActivityDescription.value('@value[1]', 'VARCHAR(8000)')
FROM @XML.nodes('ActivityDescription/text') a (TextOfActivityDescription)
) a;
Any Input , What to correct on above query to retrieve Cancellation date from more than one row?
March 13, 2015 at 1:37 am
DECLARE @XML TABLE
(
XML1 XML
);
INSERT INTO @XML
SELECT CAST(
'<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>' AS XML);
SELECT CancellationDate=SUBSTRING(s, CHARINDEX('Cancellation Date', s)+20, 12)
FROM
(
SELECT s=b.TextOfActivityDescription.value('@value[1]', 'VARCHAR(8000)')
FROM @XML a
CROSS APPLY XML1.nodes('ActivityDescription/text') b (TextOfActivityDescription)
) a;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 13, 2015 at 2:05 am
Still I am getting the same error as below
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.
I have replaced Static XML with Sub Query which select more than one rows.
DECLARE @XML TABLE
(
XML1 XML
);
INSERT INTO @XML
/* Please refer next line where I have selected value from Table instead Static XML */
SELECT (Select ActivityDescription from DC_Bil_ActivityLog );
SELECT CancellationDate=SUBSTRING(s, CHARINDEX('Cancellation Date', s)+20, 12)
FROM
(
SELECT s=b.TextOfActivityDescription.value('@value[1]', 'VARCHAR(8000)')
FROM @XML a
CROSS APPLY XML1.nodes('ActivityDescription/text') b (TextOfActivityDescription)
) a;
Any input ?
March 15, 2015 at 5:35 pm
maruthasalam (3/13/2015)
Still I am getting the same error as belowMsg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.
I have replaced Static XML with Sub Query which select more than one rows.
DECLARE @XML TABLE
(
XML1 XML
);
INSERT INTO @XML
/* Please refer next line where I have selected value from Table instead Static XML */
SELECT (Select ActivityDescription from DC_Bil_ActivityLog );
SELECT CancellationDate=SUBSTRING(s, CHARINDEX('Cancellation Date', s)+20, 12)
FROM
(
SELECT s=b.TextOfActivityDescription.value('@value[1]', 'VARCHAR(8000)')
FROM @XML a
CROSS APPLY XML1.nodes('ActivityDescription/text') b (TextOfActivityDescription)
) a;
Any input ?
The table variable is just an example. You need to use your table where the @XML table is referenced in my example.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 16, 2015 at 11:33 am
Thanks for your Reply.
I have modified the SQL query and the below SQL query done what i need exactly.
DECLARE @MyCursor CURSOR
DECLARE @txtValue varchar(MAX)
BEGIN
SET @MyCursor = CURSOR FOR
select ActivityDescription.value('(//text/@value)[1]','varchar(MAX)') as val from DC_Bil_ActivityLog
where ActivityTypeCode='PCN'
CREATE TABLE #daResults ( theDate varchar(MAX))
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @txtValue
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #daResults
Values(SUBSTRING(@txtValue, CHARINDEX('Cancellation Date', @txtValue)+21, 11))
FETCH NEXT FROM @MyCursor
INTO @txtValue
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
select * from #daResults
drop table #daResults
END
March 16, 2015 at 5:40 pm
This is more what I had in mind.
SELECT CancellationDate=SUBSTRING(s, CHARINDEX('Cancellation Date', s)+20, 12)
FROM
(
SELECT s=b.TextOfActivityDescription.value('@value[1]', 'VARCHAR(8000)')
FROM DC_Bil_ActivityLog a
CROSS APPLY ActivityDescription.nodes('ActivityDescription/text') b (TextOfActivityDescription)
) a;
No need for a CURSOR or a temp table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 16, 2015 at 7:13 pm
maruthasalam (3/16/2015)
I have modified the SQL query and the below SQL query done what i need exactly.
Except run fast/efficiently. The code that Dwain posted will do fine without the overhead of a cursor and WHILE loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply