Using XML to Enhance the Performance of String Manipulations

Question

Using XML to Enhance the Performance of String Manipulations

WayneS

SSC Guru

Points: 95409
More actions
August 19, 2008 at 9:23 pm

#197205

Comments posted to this topic are about the item Using XML to Enhance the Performance of String Manipulations
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

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

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

JPJ SSC Enthusiast Points: 146 More actions · Answer 2

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?

jhasbufuidh SSC Eights! Points: 992 More actions · Answer 3

Great article, this may solve the problem I had with this code that would have been considerably faster than Coalesce.

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

BI Guy

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Anipaul

SSC-Insane

Points: 24681

August 20, 2008 at 12:53 am

#860424

Nice Article...:)

anil_mootha SSC Rookie Points: 47 More actions · Answer 5

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 6

WayneS (8/19/2008)
Hey all...
I don't know how it happened, but the code in the article is missing some spaces from what I submitted.

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

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 7

SuperDBA-207096

SSCrazy Eights

Points: 8176

August 20, 2008 at 4:41 am

#860517

Excellent article... thanks!

Paul DB SSC Eights! Points: 850 More actions · Answer 8

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

Paul DB

WayneS SSC Guru Points: 95409 More actions · Answer 9

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

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

JPJ SSC Enthusiast Points: 146 More actions · Answer 10

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.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 11

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

Adrian Hains Ten Centuries Points: 1322 More actions · Answer 12

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-2 varchar(32)

set @v-2 = '<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;]

WayneS SSC Guru Points: 95409 More actions · Answer 13

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 14

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2