SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Using XML to Enhance the Performance of String Manipulations / Latest Posts

RE: Using XML to Enhance the Performance of String Manipulations

Cheetah — Tue, 26 Aug 2008 00:16:30 GMT

[quote][b]bhovious (8/25/2008)[/b][hr]Hey lucianHere is some code for doing parsing using a tally table. We usually have a pre-generated one in each db, but this code will run on any 2005 machine (it generates the tally table on the fly using CTE's). I've often generated comma- or pipe-separated lists using a simple select like the one below, but I agree that the XML method is CPU faster.Regards,Bob[/quote]Thanks Bob. I never had a case where I need to build a CSV string, the programmers do that in their code on the client side. I just process/parse the CSV string, but we never knows what lies ahead, so I will hold onto this idea.

RE: Using XML to Enhance the Performance of String Manipulations

The Dixie Flatline — Mon, 25 Aug 2008 15:44:32 GMT

Hey lucianHere is some code for doing parsing using a tally table. We usually have a pre-generated one in each db, but this code will run on any 2005 machine (it generates the tally table on the fly using CTE's). I've often generated comma- or pipe-separated lists using a simple select like the one below, but I agree that the XML method is CPU faster.Regards,Bob-------------------------------------------------------------------------------------------------------------------- parse string using a tally tabledeclare @inputString varchar(7900)declare @sepChar varchar(50)declare @element varchar(4)declare @input varchar(8000)set @inputString = '1/2/3/a/b/c/delta/bravo/#/@'set @sepchar = '/'set @element = 7set @input = @sepChar+@inputString+@sepChar select @input;WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows "L0 as A, L0 as B" is just shorthand way to code a cross join L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsTally AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4),Array AS(select Row_Number() over (order by N) as E,substring(@input,N+1,charindex(@sepChar,@input,N+1)-(N+1)) as element from tally where substring(@input,N,1) = @sepChar and N < len(@input))select element from Array where E = @element-------------------------------------------------------------------------------------------------------------------- generate CSV stringdeclare @csv as varchar(max)set @csv = '';WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows "L0 as A, L0 as B" is just shorthand way to code a cross join L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsTally AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)select @csv = @csv+cast(N as varchar(6))+','from tallyset @csv = stuff(@csv,len(@csv),1,'')print @csv

RE: Using XML to Enhance the Performance of String Manipulations

dfalso — Mon, 25 Aug 2008 12:43:53 GMT

Hi, thanks for the article. In reading this and other articles that show how to use XML for string tasks, they all provide examples using variables. I'm looking to parse a comma-separated string, but from a table, not just one row. I'm working through the XQuery help files, but it's a little tricky to me. Anyone do anything like this? So for clarification, I'd like to turn this:[code]insert into #myexample ( id, liststring ) VALUES ( 1, '100,101' ) insert into #myexample ( id, liststring ) VALUES ( 2, '200,301,401' ) insert into #myexample ( id, liststring ) VALUES ( 3, '100,110' ) insert into #myexample ( id, liststring ) VALUES ( 4, '70' ) [/code]into this:1 1001 1012 2002 3012 401 3 1003 1014 70but as a result set that I could use in a view.I do realize I could do this with a tally table (and currently am doing it that way), but I'd like to expand myself and use/learn about XML. Thanks much.

RE: Using XML to Enhance the Performance of String Manipulations

Cheetah — Mon, 25 Aug 2008 00:23:57 GMT

[quote][b]bhovious (8/22/2008)[/b][hr]My existing parser function uses a tally table like that last example and runs pretty damn quick. The huge CPU performance difference I saw was in building the CSV string, not taking it apart. I don't see how a tally table could help out there.[/quote];)So how do you build the string, The select for xml is very fast?

RE: Using XML to Enhance the Performance of String Manipulations

anil_mootha — Mon, 25 Aug 2008 00:23:10 GMT

Thanks for the reply WayneThanks and RegardsAnil

RE: Using XML to Enhance the Performance of String Manipulations

The Dixie Flatline — Fri, 22 Aug 2008 13:23:11 GMT

My existing parser function uses a tally table like that last example and runs pretty damn quick. The huge CPU performance difference I saw was in building the CSV string, not taking it apart. I don't see how a tally table could help out there.

RE: Using XML to Enhance the Performance of String Manipulations

Cheetah — Fri, 22 Aug 2008 07:35:57 GMT

[quote][b]WayneS (8/22/2008)[/b][hr][quote][b]lucian (8/22/2008)[/b][hr]I would like to think that both these methods have a place in a code library and can be used where applicable[/quote]I agree. And that's a pretty interesting use of the tally table to get the results. Easily modified for any delimiter.It looks to me like the string substitution part to put in the XML tags is taking about 40% of that time.FYI, I'm consistantly getting a longer time for the XML, and a faster time for the tally table, than what you posted.[/quote]Strange:unsure: I first thought that it could be a Hardware difference. Just goes to show that testing is important.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Fri, 22 Aug 2008 07:16:41 GMT

[quote][b]lucian (8/22/2008)[/b][hr]I would like to think that both these methods have a place in a code library and can be used where applicable[/quote]I agree. And that's a pretty interesting use of the tally table to get the results. Easily modified for any delimiter.It looks to me like the string substitution part to put in the XML tags is taking about 40% of that time.FYI, I'm consistantly getting a longer time for the XML, and a faster time for the tally table, than what you posted.

RE: Using XML to Enhance the Performance of String Manipulations

Cheetah — Fri, 22 Aug 2008 00:47:15 GMT

For what it's worth try this method.--Create a Tally table. Just a table that hold a big number of integer numbers (100000 in this case)use AdventureWorksDECLARE @StartTime DATETIME --Timer to measure total duration SET @StartTime = GETDATE() --Start the timer--=============================================================================-- Create and populate a Tally table--=============================================================================--===== Conditionally drop IF OBJECT_ID('dbo.Tally') IS NOT NULL DROP TABLE dbo.Tally--===== Create and populate the Tally table on the fly SELECT TOP 100 000 IDENTITY(int,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100--===== Let the public use it GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC--===== Display the total duration SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'Use XML method and note the Time (I got 1750 ms)use AdventureWorksGO-- create comma-delimted string with above XML methoddeclare @CSV varchar(max), @StartTime datetime, @EndTime datetimeselect @CSV = (select',' + AccountNumber from Sales.SalesOrderHeader FOR XML PATH('') )set @CSV = substring(@CSV, 2, len(@CSV)-1)-- convert the CSV string into a valid XML stringset @StartTime = CURRENT_TIMESTAMPdeclare @MyXMLData XML-- replace special XML characters that cause issues in SQLset @CSV = replace(replace(@CSV,'&', '&'),'<', '<')set @MyXMLData = ' '+ replace(@CSV,',',' ')+ ' 'select x.item.value('AccountNumber[1]','nvarchar(15)') from @MyXMLData.nodes('/Rows/Row')AS x(item)set @EndTime = CURRENT_TIMESTAMPselect @StartTime, @EndTime, DateDiff(ms, @StartTime, @EndTime)GONow use the tally table and check time again (I got 390 ms)DECLARE @Parameter VARCHAR(max), @StartTime datetime, @EndTime datetimeselect @Parameter = (select',' + AccountNumber from Sales.SalesOrderHeader FOR XML PATH('') ) + ','set @StartTime = CURRENT_TIMESTAMP--===== Join the Tally table to the string at the character level and -- when we find a comma, insert what's between that command and -- the next comma into the Elements table SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) FROM dbo.Tally WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the commaset @EndTime = CURRENT_TIMESTAMPselect @StartTime, @EndTime, DateDiff(ms, @StartTime, @EndTime)I would like to think that both these methods have a place in a code library and can be used where applicable

RE: Using XML to Enhance the Performance of String Manipulations

The Dixie Flatline — Thu, 21 Aug 2008 15:59:38 GMT

Hey Wayne, I must be on drugs today. Or maybe I'm not and ought to be because of all the typos I'm making today. "Seconds" in my last post should be MS. Both techniques are subsecond queries at 20000 rows, but the "FOR XML" version is still 50 times faster. That gets my attention.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Thu, 21 Aug 2008 15:24:26 GMT

[quote][b]bhovious (8/21/2008)[/b][hr]Just to answer the rhetorical questions about building xml by wrapping tag constants around data, rather than use a "For XML" statement, we should remember that a lot of code was generated before SQL Server had native XML support. Code that isn't broken isn't revisted every time a new capability is introduced.[/quote]Excellent point.[quote] That said, I just had to test out "For XML" against simple string concatenation to see the performance difference. I was stunned by the difference in CPU efficiency.Using the following:select @CSV = (select ',' + cast(n as varchar(6)) from dbo.Tally where N <=20000 FOR XML PATH('') )CPU Time was 20 secondsUsing the "traditional"set @csv = ''select @CSV = @csv+',' + cast(n as varchar(6)) from dbo.Tally where N <=20000CPU Time was 1219 seconds. [/quote]A whole 20 minutes faster! Fantastic! (well, 1 second short of 20 minutes...)[quote]set @csv = ''select @CSV = ',' + cast(n as varchar(6)) +@cssfrom dbo.Tally where N <=20000CPU time was 580 MS, but the numbers are backwards[/quote]I assume the @css is @csv...Is your cpu time so short the second time around because you had just cached it from the first time? 20,000 numbers aren't going to take up a lot of memory.[quote]I'm sold. A couple of our core functions used to generate parsed strings are about to be modified. Good article. Good to know.[/quote]I'm glad it helped someone out.Wayne

RE: Using XML to Enhance the Performance of String Manipulations

The Dixie Flatline — Thu, 21 Aug 2008 14:58:31 GMT

Just to answer the rhetorical questions about building xml by wrapping tag constants around data, rather than use a "For XML" statement, we should remember that a lot of code was generated before SQL Server had native XML support. Code that isn't broken isn't revisted every time a new capability is introduced. That said, I just had to test out "For XML" against simple string concatenation to see the performance difference. I was stunned by the difference in CPU efficiency.Using the following:select @CSV = (select ',' + cast(n as varchar(6)) from dbo.Tally where N <=20000 FOR XML PATH('') )CPU Time was 20 secondsUsing the "traditional"set @csv = ''select @CSV = @csv+',' + cast(n as varchar(6)) from dbo.Tally where N <=20000CPU Time was 1219 seconds. set @csv = ''select @CSV = ',' + cast(n as varchar(6)) +@cssfrom dbo.Tally where N <=20000CPU time was 580 MS, but the numbers are backwardsI'm sold. A couple of our core functions used to generate parsed strings are about to be modified. Good article. Good to know.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Thu, 21 Aug 2008 10:13:43 GMT

[quote][b]lucian (8/21/2008)[/b][hr]But I want to return a table that I can use in a inner join with other tables, or in my where clause.[/quote]I understand. I mistakenly thought that you could just return the output from the select statement directly without having to go through the @table variable. (For a very large delimited string, this may cause additional disk activity.)Wayne

RE: Using XML to Enhance the Performance of String Manipulations

Argneka — Thu, 21 Aug 2008 06:56:52 GMT

Hi,The XML string is not properly displaying on my previous post. If you would like full example, just send me an email.Thanks,Argneka

RE: Using XML to Enhance the Performance of String Manipulations

Argneka — Thu, 21 Aug 2008 06:49:15 GMT

Hi,Here is a simple example of how I implement XML in sql2005:declare @XmlList XMLDeclare @Property varchar(50)Declare @ObjectName varchar(50)Declare @ColumnName varchar(50)Declare @Value varchar(50)Declare @rowcount intDeclare @count intDeclare @XmlCount intset @Count=1select @XmlList= ' ' select @XmlCount=CONVERT(varchar,@XmlList.query('count(//Rows/Properties/.)'))set @rowcount=CONVERT(int,@XmlCount)WHILE(@count<=@rowcount)BEGINSelect @ObjectName=data.value('@ObjectName[1]','varchar(50)'), @ColumnName=data.value('@ColumnName[1]','varchar(50)'), @Property=data.value('@Property[1]','varchar(50)'), @Value=data.value('@Value[1]','varchar(50)') from @XmlList.nodes('//Rows/Properties[position()=sql:variable("@count")]') XmlList(data) set @count=@count+1select @ObjectName as Objectselect @ColumnName as [Column]select @Property as [Property]Select @Value as [Value]ENDselect @rowcount as countI usually pass the XML object @XmlList as parameter from app that builds up the XML string. In this case .NET.Hope this helps,ArgnekaPS: I have not tested on huge data sets, but it works very well for what I use it for.

RE: Using XML to Enhance the Performance of String Manipulations

Cheetah — Thu, 21 Aug 2008 06:36:44 GMT

[quote][b]WayneS (8/21/2008)[/b][hr][quote][b]lucian (8/21/2008)[/b][hr]Very useful.I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.My function to split the string up and return a table(that can be used in joins etc) now looks like this ::angry: See attachment cos I cannot get xml tags to display correctly :blush:[/quote]If you change ItemVal to StrVal and remove the insert into the temp table, you should be able to just return the data directly from the select clause. This would avoid having to go through an intermediate temp table.[/quote]But I want to return a table that I can use in a inner join with other tables, or in my where clause.Ex --somewhere in my spselect * from tblCustomer C inner join dbo.delimstrtoTable(@SelctedCustIdList) T on C.Customerid = T.StrValor alt. select * from tblCustomer where CustomerId in(select strval from dbo.DelimStrToTable(@SelctedCustIdList))

RE: Using XML to Enhance the Performance of String Manipulations

Paul DB — Thu, 21 Aug 2008 06:19:14 GMT

[quote][b]WayneS (8/21/2008)[/b][hr][quote][b]anil_mootha (8/20/2008)[/b][hr]Hi Paul,It looks like nobody's bothered to answer our question. To me till I don't get a satisfactory answer, this topic doesn't make any sense!Have a wonderful day![/quote]I thought I had answered the question... I'm sorry if I haven't.I had seen two questions:1. Why would anyone build an XML string manually instead of using FOR XML?2. Could anyone show us how one of Wayne's queries would look using FOR XML instead?My answers:1. FOR XML is definitely better to use, as the article demonstrates. I had only used the manual method to build an XML string so that I wouldn't have to read the help for how to use FOR XML for all of the optional parameters. Also, note that if using SQL 2000, I don't think that all of the parameters in the FOR XML can be used, so to get the string in that format I believe that you have to build it manually.2. In the article, the queries that build the strings for making a comma-delimited list, or the XML string for the 30,000+ items DO use the FOR XML clause. Look at those for the example.If you still have an unanswered question, can you please restate it so that it can be addressed?Thanks,Wayne[/quote]I am so much of a noob at using XML in queries that I did not recognize the FOR XML in the statements you posted. :blush: Sorry about that. Glad for your explanations. :)

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Thu, 21 Aug 2008 06:18:44 GMT

[quote][b]lucian (8/21/2008)[/b][hr]Very useful.I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.My function to split the string up and return a table(that can be used in joins etc) now looks like this ::angry: See attachment cos I cannot get xml tags to display correctly :blush:[/quote]If you change ItemVal to StrVal and remove the insert into the temp table, you should be able to just return the data directly from the select clause. This would avoid having to go through an intermediate temp table.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Thu, 21 Aug 2008 06:08:19 GMT

[quote][b]nvanesch (8/21/2008)[/b][hr]I really good performance improvement on string concatenation. I guess these problems weren't present in SQL 2000 so much as we didn't have 2GB varchar variables to work with and had to find other solutions :(However there are still merits in looking into other options of rowset manipulation as string concatenation and csv strings should really be a last resort and not part of the main toolset as we've had programmers utilise csv strings to have their code break when commas are included in the data.As a note I think it should be stated that this code is for SQL 2005 and up.Thanks for a good article.[/quote]I agree with you about being careful when using csv strings. However, sometimes you still have to. For instance, if you are trying to pass a reporting services multi-valued parameter to a stored procedure, you need to do so either by converting it into XML (see article [url]http://www.sqlservercentral.com/articles/Reporting+Services/62731/[/url] ), or by a delimited string. A delimited string should use as the delimiter something that wouldn't appear in the data, such as char(255). What I've done in the article can easily be modified to handle whatever delimiter you desire to use. I just used csv as an example that all would easily understand.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Thu, 21 Aug 2008 05:58:03 GMT

[quote][b]anil_mootha (8/20/2008)[/b][hr]Hi Paul,It looks like nobodys bothered to answer our question. To me till I dont get a satisfactory answer, this topic doesnt make any sense!Have a wonderful day![/quote]I thought I had answered the question... I'm sorry if I haven't.I had seen two questions:1. Why would anyone build an XML string manually instead of using FOR XML?2. Could anyone show us how one of Wayne's queries would look using FOR XML instead?My answers:1. FOR XML is definitely better to use, as the article demonstrates. I had only used the manual method to build an XML string so that I wouldn't have to read the help for how to use FOR XML for all of the optional parameters. Also, note that if using SQL 2000, I don't think that all of the parameters in the FOR XML can be used, so to get the string in that format I believe that you have to build it manually.2. In the article, the queries that build the strings for making a comma-delimited list, or the XML string for the 30,000+ items DO use the FOR XML clause. Look at those for the example.If you still have an unanswered question, can you please restate it so that it can be addressed?Thanks,Wayne

RE: Using XML to Enhance the Performance of String Manipulations

Cheetah — Thu, 21 Aug 2008 01:18:29 GMT

Very useful.I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.My function to split the string up and return a table(that can be used in joins etc) now looks like this ::angry: See attachment cos I cannot get xml tags to display correctly :blush:

RE: Using XML to Enhance the Performance of String Manipulations

nvanesch — Thu, 21 Aug 2008 00:35:24 GMT

I really good performance improvement on string concatenation. I guess these problems weren't present in SQL 2000 so much as we didn't have 2GB varchar variables to work with and had to find other solutions :(However there are still merits in looking into other options of rowset manipulation as string concatenation and csv strings should really be a last resort and not part of the main toolset as we've had programmers utilise csv strings to have their code break when commas are included in the data.As a note I think it should be stated that this code is for SQL 2005 and up.Thanks for a good article.

RE: Using XML to Enhance the Performance of String Manipulations

anil_mootha — Wed, 20 Aug 2008 22:17:26 GMT

Hi Paul,It looks like nobodys bothered to answer our question. To me till I dont get a satisfactory answer, this topic doesnt make any sense!Have a wonderful day!

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Wed, 20 Aug 2008 12:31:10 GMT

[quote][b]Paul DB (8/20/2008)[/b][hr][quote][b]WayneS (8/20/2008)[/b][hr][quote][b]Paul DB (8/20/2008)[/b][hr][quote][b]anil_mootha (8/20/2008)[/b][hr]Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.The very purpose of FOR XML statement would be void otherwise.Thanks and regardsAnil[/quote]I'm also interested in knowing the answer to Anil's question. Have a great day. :cool:[/quote]Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.[/quote]I am also not familiar with FOR XML. :) Could anyone show us how one of Wayne's queries would look using FOR XML instead? I'd offer 1 point, if I could. ;)[/quote]Paul,Re-read the article. The first two examples show an XML method that use the FOR XML clause.Wayne

RE: Using XML to Enhance the Performance of String Manipulations

Paul DB — Wed, 20 Aug 2008 08:39:58 GMT

[quote][b]WayneS (8/20/2008)[/b][hr][quote][b]Paul DB (8/20/2008)[/b][hr][quote][b]anil_mootha (8/20/2008)[/b][hr]Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.The very purpose of FOR XML statement would be void otherwise.Thanks and regardsAnil[/quote]I'm also interested in knowing the answer to Anil's question. Have a great day. :cool:[/quote]Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.[/quote]I am also not familiar with FOR XML. :) Could anyone show us how one of Wayne's queries would look using FOR XML instead? I'd offer 1 point, if I could. ;)

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Wed, 20 Aug 2008 08:30:08 GMT

[quote][b]Paul DB (8/20/2008)[/b][hr][quote][b]anil_mootha (8/20/2008)[/b][hr]Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.The very purpose of FOR XML statement would be void otherwise.Thanks and regardsAnil[/quote]I'm also interested in knowing the answer to Anil's question. Have a great day. :cool:[/quote]Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Wed, 20 Aug 2008 08:21:50 GMT

[quote][b]Steve Jones - Editor (8/20/2008)[/b][hr]The code edits have been made, so things should be OK now.[/quote]Thanks Steve!

RE: Using XML to Enhance the Performance of String Manipulations

Adrian Hains — Wed, 20 Aug 2008 07:45:10 GMT

Note that there are a few characters that are not legal in xml.From http://www.w3.org/TR/2006/REC-xml-20060816/:[2] Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF] /* any Unicode character, excluding the surrogate blocks, FFFE, and FFFF. */If you run:[code]declare @v varchar(32)set @v = '<a>' + cast(0x08 as varchar) + '</a>'select cast (@v as xml)[/code]You will get this error:Msg 9420, Level 16, State 1, Line 3XML parsing: line 1, character 4, illegal xml characterIf your csv input list is going to have essentially ascii data this will not be a problem.[edit - my query was getting mangled, had to replace < with & lt;]

RE: Using XML to Enhance the Performance of String Manipulations

Steve Jones - SSC Editor — Wed, 20 Aug 2008 07:42:30 GMT

The code edits have been made, so things should be OK now.

RE: Using XML to Enhance the Performance of String Manipulations

JPJ — Wed, 20 Aug 2008 07:33:00 GMT

[quote][b]WayneS (8/20/2008)[/b][hr][quote][b]JPJ (8/19/2008)[/b][hr]I agree with you wayne.I have a small issue in addition to that, this line gave me error:select @MyXMLString = @MyXMLString + ' '+ AccountNumber + ' ' This is the error:Msg 102, Level 15, State 1, Line 6Incorrect syntax near 'AccountNumber'.Any idea?[/quote](I do wish that this site's forum board would support the display of XML tags easier!)For that select command, there are two extraneous quote marks. The first is on the second line, after the "Row" xml tag immediately prior to the "AccountNumber" tag. The second is on the third line, immediately after the backslash character and immediately prior to the "AccountNumber" tag.So, it should look like:select @MyXMLString = @MyXMLString + '<Row><AccountNumber>'+ AccountNumber + '</AccountNumber></Row>' [/quote] That works! Thanks.

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Wed, 20 Aug 2008 06:28:52 GMT

[quote][b]JPJ (8/19/2008)[/b][hr]I agree with you wayne.I have a small issue in addition to that, this line gave me error:select @MyXMLString = @MyXMLString + ' '+ AccountNumber + ' ' This is the error:Msg 102, Level 15, State 1, Line 6Incorrect syntax near 'AccountNumber'.Any idea?[/quote](I do wish that this site's forum board would support the display of XML tags easier!)For that select command, there are two extraneous quote marks. The first is on the second line, after the "Row" xml tag immediately prior to the "AccountNumber" tag. The second is on the third line, immediately after the backslash character and immediately prior to the "AccountNumber" tag.So, it should look like:select @MyXMLString = @MyXMLString + '<Row><AccountNumber>'+ AccountNumber + '</AccountNumber></Row>'

RE: Using XML to Enhance the Performance of String Manipulations

Paul DB — Wed, 20 Aug 2008 06:12:53 GMT

[quote][b]anil_mootha (8/20/2008)[/b][hr]Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.The very purpose of FOR XML statement would be void otherwise.Thanks and regardsAnil[/quote]I'm also interested in knowing the answer to Anil's question. Have a great day. :cool:

RE: Using XML to Enhance the Performance of String Manipulations

SuperDBA-207096 — Wed, 20 Aug 2008 04:41:16 GMT

Excellent article... thanks!

RE: Using XML to Enhance the Performance of String Manipulations

Samuel Vella — Wed, 20 Aug 2008 04:37:22 GMT

[quote][b]WayneS (8/19/2008)[/b][hr]Hey all...I don't know how it happened, but the code in the article is missing some spaces from what I submitted. [/quote]Seems to be happening on other articles too, I noticed it on this one yesterday:http://www.sqlservercentral.com/articles/Integration+Services/63623/although that one also suffers from the code sections being strangely fragmented

RE: Using XML to Enhance the Performance of String Manipulations

anil_mootha — Wed, 20 Aug 2008 03:46:11 GMT

Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.The very purpose of FOR XML statement would be void otherwise.Thanks and regardsAnil

RE: Using XML to Enhance the Performance of String Manipulations

Anipaul — Wed, 20 Aug 2008 00:53:50 GMT

Nice Article...:)

RE: Using XML to Enhance the Performance of String Manipulations

Philippe Cand — Tue, 19 Aug 2008 23:40:33 GMT

Great article, this may solve the problem I had with this code that would have been considerably faster than Coalesce.[code]-- create list to pivot hist data SELECT @DistList = COALESCE(@DistList + ',[', '[') + CAST(Dist AS VarCHAR(85)) + ']'FROM ( SELECT top 1000 Disty FROM @t1 Order by Disty ) t(Dist) -- try for XML to speed up the coalesce -- may bug on Shangai P&S--Select @DistList = (--SELECT Disty + ', ' As [text()]-- FROM @t1 -- ORDER BY Disty-- FOR XML PATH('')--)--Set @DistList = LEFT(@DistList, Len (@DistList) -1) [/code]

RE: Using XML to Enhance the Performance of String Manipulations

JPJ — Tue, 19 Aug 2008 23:23:51 GMT

I agree with you wayne.I have a small issue in addition to that, this line gave me error:select @MyXMLString = @MyXMLString + ' '+ AccountNumber + ' ' This is the error:Msg 102, Level 15, State 1, Line 6Incorrect syntax near 'AccountNumber'.Any idea?

RE: Using XML to Enhance the Performance of String Manipulations

WayneS — Tue, 19 Aug 2008 21:33:21 GMT

Hey all...I don't know how it happened, but the code in the article is missing some spaces from what I submitted. They should be pretty obvious where that is, but if you are having problems let me know and I'll post how it should be (or get Steve or someone to add them to the article).The biggest offenders:space needed between "use" and "AdventureWorks"space needed between "set" / "select" / "declare" / "print" / "if" and the rest of the command.space needed between "FOR" and "XML" space needed between "XML" and "PATH"Wayne

Using XML to Enhance the Performance of String Manipulations

WayneS — Tue, 19 Aug 2008 21:23:28 GMT

Comments posted to this topic are about the item [B]Using XML to Enhance the Performance of String Manipulations[/B]