Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Split string using XML Expand / Collapse
Author
Message
Posted Monday, June 29, 2009 6:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 37,102, Visits: 31,655
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #744095
Posted Wednesday, July 1, 2009 3:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:14 PM
Points: 29, Visits: 85
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
Post #745783
Posted Friday, July 10, 2009 4:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #750951
Posted Friday, July 10, 2009 7:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:16 AM
Points: 478, Visits: 1,415
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.
Post #751032
Posted Friday, July 10, 2009 7:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #751042
Posted Friday, July 10, 2009 7:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:16 AM
Points: 478, Visits: 1,415
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?
Post #751045
Posted Friday, July 10, 2009 8:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #751097
Posted Friday, July 10, 2009 11:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:16 AM
Points: 478, Visits: 1,415
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.
Post #751303
Posted Sunday, July 12, 2009 10:55 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #751794
Posted Sunday, July 12, 2009 11:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:03 PM
Points: 143, Visits: 550
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
Post #751798
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse