Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Using XML to Enhance the Performance of String Manipulations Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2008 7:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:52 PM
Points: 20, Visits: 28
WayneS (8/20/2008)
JPJ (8/19/2008)
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 6
Incorrect syntax near 'AccountNumber'.

Any idea?


(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>'




That works!


Thanks.

Post #555766
Posted Wednesday, August 20, 2008 7:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
The code edits have been made, so things should be OK now.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #555774
Posted Wednesday, August 20, 2008 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 01, 2012 1:18 PM
Points: 110, Visits: 261
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:
declare @v varchar(32)
set @v = '<a>' + cast(0x08 as varchar) + '</a>'
select cast (@v as xml)


You will get this error:
Msg 9420, Level 16, State 1, Line 3
XML parsing: line 1, character 4, illegal xml character

If 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;]
Post #555777
Posted Wednesday, August 20, 2008 8:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 6,544, Visits: 8,753
Steve Jones - Editor (8/20/2008)
The code edits have been made, so things should be OK now.


Thanks Steve!


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #555796
Posted Wednesday, August 20, 2008 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 6,544, Visits: 8,753
Paul DB (8/20/2008)
anil_mootha (8/20/2008)
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 regards
Anil


I'm also interested in knowing the answer to Anil's question. Have a great day.


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.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #555807
Posted Wednesday, August 20, 2008 8:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 09, 2013 1:26 PM
Points: 60, Visits: 258
WayneS (8/20/2008)
Paul DB (8/20/2008)
anil_mootha (8/20/2008)
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 regards
Anil


I'm also interested in knowing the answer to Anil's question. Have a great day.


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.


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. ;)


Paul DB
Post #555824
Posted Wednesday, August 20, 2008 12:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 6,544, Visits: 8,753
Paul DB (8/20/2008)
WayneS (8/20/2008)
Paul DB (8/20/2008)
anil_mootha (8/20/2008)
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 regards
Anil


I'm also interested in knowing the answer to Anil's question. Have a great day.


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.


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. ;)


Paul,

Re-read the article. The first two examples show an XML method that use the FOR XML clause.

Wayne


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #556019
Posted Wednesday, August 20, 2008 10:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 12:17 AM
Points: 3, Visits: 8
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!
Post #556260
Posted Thursday, August 21, 2008 12:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 08, 2012 1:26 AM
Points: 3, Visits: 17
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.
Post #556301
Posted Thursday, August 21, 2008 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 3:40 AM
Points: 24, Visits: 266
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 :

See attachment cos I cannot get xml tags to display correctly


  Post Attachments 
DelimStrToTable.txt (7 views, 3.56 KB)
Post #556317
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse