Using XML to Enhance the Performance of String Manipulations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Using XML to Enhance the Performance of...

40 posts, Page 2 of 4

««1 2 3 4 »»»

Using XML to Enhance the Performance of String Manipulations

Rate Topic

Display Mode

Topic Options

Author

Message

JPJ

Posted Wednesday, August 20, 2008 7:33 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, August 29, 2011 8:12 PM
Points: 20, Visits: 27

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

Steve Jones - SSC Editor

Posted Wednesday, August 20, 2008 7:42 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

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

Adrian Hains

Posted Wednesday, August 20, 2008 7:45 AM

SSC-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

WayneS

Posted Wednesday, August 20, 2008 8:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 6,368, Visits: 8,231

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

WayneS

Posted Wednesday, August 20, 2008 8:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 6,368, Visits: 8,231

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. Cool

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!

Post #555807

Paul DB

Posted Wednesday, August 20, 2008 8:39 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:07 PM
Points: 60, Visits: 257

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. Cool

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

WayneS

Posted Wednesday, August 20, 2008 12:31 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 6,368, Visits: 8,231

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. Cool

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!

Post #556019

anil_mootha

Posted Wednesday, August 20, 2008 10:17 PM

Forum 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

nvanesch

Posted Thursday, August 21, 2008 12:35 AM

Forum 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 Sad

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

Cheetah

Posted Thursday, August 21, 2008 1:18 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:26 AM
Points: 23, Visits: 246

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