SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split string using XML


Split string using XML

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214000 Visits: 41979
peter (6/29/2009)
I have a set of comma delimited strings which have between 1 and 100's of invoice records per string (with each invoice record having the same 10 data fields). Your code turns the data into rows with admirable efficiency. However, I can't work out how to transform the results into columns with one row per invoice record. Can anyone help?


Yes... please see the following.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/cross+tab/65048/

Also, I'm not sure to whom you are speaking but, just to advise, XML splitters are anything but effecient.

I'll also caution that once you've "normalized" a CSV, rotating or pivoting it into columns is usually a bad idea for performance reasons if all the columns are basically the same "thing".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pdross2000
pdross2000
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 86
I did a slight modification

select @xml = cast(( '') as xml)
SELECT N.value('.', 'nvarchar(max)')

so if there are tags in the string it handles them
Eralper
Eralper
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 466
Hello all,

I have placed the XML string split logic into a user defined function and I think running the split within the function results better in performance than keeping the split login inline.

You can check the result with an other example at Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands

Eralper
SQL Programming

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1684 Visits: 1501
Eralper (7/10/2009)
Hello all,

I have placed the XML string split logic into a user defined function and I think running the split within the function results better in performance than keeping the split login inline.

You can check the result with an other example at Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands

Eralper
SQL Programming


Can I ask why you are using this function and passing it along to others?
Performance is pretty horrible compared to other ways to split a string.
Eralper
Eralper
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 466
Hello Goldie,
Do you mean using the XML logic or placing it into a udf causes the performance decrease?

Using the XML inline is a little bit difficult for whom do not use XML features of the SQL2005.
This format is not easy to implement.

Placing the logic in a udf makes the implementation easier.

Actually, I made a simple test to see that the xml function is quicker. But I do not say that this is always works in better performance. May be slower, and since you disagree, it is probably slower perhaps while working with high number of rows.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1684 Visits: 1501
Eralper (7/10/2009)
Hello Goldie,
Do you mean using the XML logic or placing it into a udf causes the performance decrease?

Using the XML inline is a little bit difficult for whom do not use XML features of the SQL2005.
This format is not easy to implement.

Placing the logic in a udf makes the implementation easier.

Actually, I made a simple test to see that the xml function is quicker. But I do not say that this is always works in better performance. May be slower, and since you disagree, it is probably slower perhaps while working with high number of rows.


I am talking about the XML logic. Not the UDF.
Using a loop is faster, and using the Tally method is fastest.
Why use XML if there are much faster ways?
Eralper
Eralper
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 466
Hi,
You know this discussion is originally started on an article which is describing this method. Because of that I had given an example on splitting by XML.

Eralper
http://www.kodyaz.com

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1684 Visits: 1501
Eralper (7/10/2009)
Hi,
You know this discussion is originally started on an article which is describing this method. Because of that I had given an example on splitting by XML.

Eralper
http://www.kodyaz.com


My objection is to the article your site which states:

...splitting and parsing can also be implemented by using the new XML improvements in t-sql...So this is somehow a little bit more advanced than the regular parsing methods we used to code during our t-sql splitting tasks...


In fact this method is not better, and you do not state that anywhere in your article.
Eralper
Eralper
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 466
Hi Goldie,
Thanks for your concern that you have read my article.
I do not want to make the conversation too long about my article here since we should discuss the article by Divya here. I'd like to talk about your objections in detail at kodyaz.com.

Since I love the new t-sql improvements like XML improvements, CTEs, Window functions, Merge, Hierarchy, etc and I got impressed by the style and the variety in code the t-sql provides I like to work with the new methods in tsql solutions.

So you are right that it may have performance issues, the developers would take the performance of their solution while implementing it on a system. But my main motivation is just providing an additional way of solution to an existing problem.

But I'd take your objection into account since you are right at performance. And I'll alter the article by adding a note on that topic.

Thanks again Goldie,
Eralper

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Divya Agrawal
Divya Agrawal
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 604
Thanks very much for discussing on the article regarding performance issues. I have just shown a new approach to split strings with the help of XML and a very good use of new T-sql feature 'CROSS APPLY'.

Good to know that XML should not be preferred when performance come into picture.

--Divya
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search