Using XML to Enhance the Performance of String Manipulations

  • 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

  • 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

  • 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?

  • 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

  • Nice Article...:)

  • 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

  • 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

  • Excellent article... thanks!

  • 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

  • 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

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

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

  • 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;]

  • 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

  • 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

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

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