July 18, 2012 at 3:19 am
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]
July 18, 2012 at 6:51 am
For Xquery u should refer jacob sebastian blog
July 18, 2012 at 7:11 am
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: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.
July 18, 2012 at 7:32 am
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply