August 19, 2008 at 9:23 pm
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
August 19, 2008 at 9:33 pm
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
August 19, 2008 at 11:23 pm
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?
August 19, 2008 at 11:40 pm
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
August 20, 2008 at 12:53 am
Nice Article...:)
August 20, 2008 at 3:46 am
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
August 20, 2008 at 4:37 am
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
August 20, 2008 at 4:41 am
Excellent article... thanks!
August 20, 2008 at 6:12 am
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
August 20, 2008 at 6:28 am
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
August 20, 2008 at 7:33 am
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.
August 20, 2008 at 7:42 am
The code edits have been made, so things should be OK now.
August 20, 2008 at 7:45 am
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;]
August 20, 2008 at 8:21 am
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
August 20, 2008 at 8:30 am
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
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply