March 28, 2013 at 3:22 pm
Hi,
I'm using a neat query to concatenate strings. It is using XML to do.
I had 200.000 rows to be concatenated and the standard SQL was stalling.
This statement is doing it in 0.4 seconds!! Amazing!
One downside is dat the carriage return CHAR(13) is added as a tag being .
I'm looking for a way to have it as a real CR.
Anyone knows a way to do this?
This is my test code:
DECLARE @TABLE TABLE (ID INT IDENTITY(1,1),
LINE VARCHAR(100))
DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)
DECLARE @OUTPUT VARCHAR(MAX) = ''
INSERT @TABLE
VALUES
('ONE'),
('TWO'),
('THREE'),
('FOUR'),
('FIVE'),
('SIX')
SET @OUTPUT = (SELECT CAST( LINE AS VARCHAR(MAX) ) + @CRLF FROM @TABLE ORDER BY ID FOR XML PATH( '' ))
SELECT @OUTPUT
March 28, 2013 at 3:26 pm
Be sure to display the output to text, not grid:
DECLARE @TABLE TABLE (ID INT IDENTITY(1,1),
LINE VARCHAR(100))
DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)
DECLARE @OUTPUT VARCHAR(MAX) = ''
INSERT @TABLE
VALUES
('ONE'),
('TWO'),
('THREE'),
('FOUR'),
('FIVE'),
('SIX')
select @OUTPUT = stuff((select LINE + char(13) + char(10)
from @TABLE
order by ID
for xml path(''),TYPE).value('.','varchar(max)'),1,0,'')
--SET @OUTPUT = (SELECT CAST( LINE AS VARCHAR(MAX) ) + @CRLF FROM @TABLE ORDER BY ID FOR XML PATH( '' ))
SELECT @OUTPUT
March 28, 2013 at 5:07 pm
The output is:
ONE
 ; TWO
 ; THREE
 ; FOUR
 ; FIVE
 ; SIX
 ;
I want to have the following output:
ONE
TWO
THREE
FOUR
FIVE
Best,
Harry
March 28, 2013 at 5:09 pm
Harry Drenth (3/28/2013)
The output is:ONE TWO THREE FOUR FIVE SIX
I want to have the following output:
ONE
TWO
THREE
FOUR
FIVE
Best,
Harry
Once again, do not display to a GRID, display to TEXT. It works.
March 28, 2013 at 5:12 pm
Harry Drenth (3/28/2013)
The output is:ONE TWO THREE FOUR FIVE SIX
I want to have the following output:
ONE
TWO
THREE
FOUR
FIVE
Best,
Harry
Or, replace SELECT @OUTPUT with PRINT @OUTPUT
March 28, 2013 at 5:16 pm
Unfortunately it doesn't
It looks like this:
(6 row(s) affected)
-------------------------------------------------------------------
ONE
 ;
TWO
 ;
THREE
 ;
FOUR
 ;
FIVE
 ;
SIX
 ;
(1 row(s) affected)
The output is delivered to a VB.Net app which is saving the output to a file. The tag is in the output instead of the CHAR(13).
In this Forum Post the tag is replace by a carriage. Therefore I added a space before the semi column (;).
So the question remains how to prevend the XML statement to add the tag instead of CHAR(13).
The easiest way is to replace it in the string with a replace statement but in some cases I concatenate over 200.000 lines so I have a lot to replace which costs time.
March 28, 2013 at 5:20 pm
Harry Drenth (3/28/2013)
Unfortunately it doesn'tIt looks like this:
(6 row(s) affected)
-------------------------------------------------------------------
ONE
 ;
TWO
 ;
THREE
 ;
FOUR
 ;
FIVE
 ;
SIX
 ;
(1 row(s) affected)
Post the code you are running. When I use what I posted I get this:
ONE
TWO
THREE
FOUR
FIVE
SIX
March 28, 2013 at 5:38 pm
Harry Drenth (3/28/2013)
Unfortunately it doesn'tIt looks like this:
(6 row(s) affected)
-------------------------------------------------------------------
ONE
 ;
TWO
 ;
THREE
 ;
FOUR
 ;
FIVE
 ;
SIX
 ;
(1 row(s) affected)
The output is delivered to a VB.Net app which is saving the output to a file. The tag is in the output instead of the CHAR(13).
In this Forum Post the tag is replace by a carriage. Therefore I added a space before the semi column (;).
So the question remains how to prevend the XML statement to add the tag instead of CHAR(13).
The easiest way is to replace it in the string with a replace statement but in some cases I concatenate over 200.000 lines so I have a lot to replace which costs time.
The tags I see in the post above are not from the output of my query but a result of posting on this forum.
March 28, 2013 at 5:42 pm
Is the VB.NET app running the query itself, or is something else running it and passing the results to the application?
If it is the second option, how is it passing the results?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
March 28, 2013 at 10:33 pm
Once again, please show us how you are using this. I use this method to build executable dynamic sql so I know it works.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy