Need Help on This Script [Split Column through XML & Function]

Question

Post reply

Need Help on This Script [Split Column through XML & Function]

ashish.sys

Grasshopper

Points: 12
More actions
July 18, 2012 at 3:19 am

#261395

Pls Explain
------------------------------------------------------------------------------
;WITH Cte AS
(
SELECT
ClaimNO,
CAST('<M>' + REPLACE( REPLACE( replace(CLMHPROVKEY1Control3,'.',''),'"',''), ',' , '</M><M>') + '</M>' AS XML) AS Names
from ADVENTIST_MEDICAL_PAID_CLAIMS
)
SELECT
ClaimNO,
Split.a.value('.', 'VARCHAR(100)') AS Names into #TEMP
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)[/font]

Viewing 4 posts - 1 through 4 (of 4 total)

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

SQL006 SSCertifiable Points: 5071 More actions · Answer 1

For Xquery u should refer jacob sebastian blog

http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

ashish.sys (7/18/2012)
Pls Explain
------------------------------------------------------------------------------
;WITH Cte AS
(
SELECT
ClaimNO,
CAST('<M>' + REPLACE( REPLACE( replace(CLMHPROVKEY1Control3,'.',''),'"',''), ',' , '</M><M>') + '</M>' AS XML) AS Names
from ADVENTIST_MEDICAL_PAID_CLAIMS
)
SELECT
ClaimNO,
Split.a.value('.', 'VARCHAR(100)') AS Names into #TEMP
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)[/font]

Looks like Jeff Moden's string splitter (Split function). A quick Google will show you the article, please read it for an explanation of how this function works.

Anyway it appears that the procedure is drawing data from a source table, string-bashing it into an XML format, then shredding the XML back into #TEMP.

There's probably a better way of achieving this without using XML at all - please post the source table definition and what you are trying to achieve.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Yeah it is pretty hard to tell exactly what you are doing here but it appears you have a scalar function called split to parse your strings. You should check out the article in my signature about splitting strings.

If you want help to put this all together please read the first link in my signature about best practices when posting questions. Then post some ddl, sample data and desired output. You will be rewarded with tested, fast and accurate code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/