Process CDATA string

Question

Hi ,

The function receives CDATA string like <![CDATA[deLastName,uSKt133Ii]]>,<![CDATA[deFirstName,hq1t]]>.

I need to read each CDATA section like deLastName,uSKt133Ii separately and implement business process.

Example:

<![CDATA[deLastName,uSKt133Ii]]>,<![CDATA[deFirstName,hq1t]]>

Read deLastName,uSKt133Ii --- Tom after business process
Read deFirstName,hq1t --David after business process

Finally i need to replace above values to original string

Tom,David

Please share your thoughts

Answer 1

CDATA is an XML token. The simplest way to remove it is to CAST(CAST(xxx AS XML) as VARCHAR(MAX)).

Answer 2

In simplest case you can use REPLACE:

declare @received_string as varchar(max) = '<![CDATA[deLastName,uSKt133Ii]]> ,<![CDATA[deFirstName,hq1t]]> test'
declare @result as varchar(max) = replace(replace(@received_string, '<![CDATA[', ''), ']]>', '')

But since SQL includes XML features, check this example too:

declare @result as varchar(max) = cast(concat('<r>', @received_string, '</r>') as xml).value('/*[1]', 'varchar(max)')

The @result will contain “deLastName,uSKt133Ii ,deFirstName,hq1t test”, then you can process it. To split, consider STRING_SPLIT. Show details about your tables if you need something else.

Answer 3

This page does not seem to support the inclusion of cdata data in the statement, please check the CREATE AND INSERT code in the below test.txt attachment.

98172-test.txt

Try:

SELECT   
SUBSTRING(string,CHARINDEX('CDATA[',string)+6,CHARINDEX(',',string)-CHARINDEX('CDATA[',string)-6) result1  
,SUBSTRING(string,CHARINDEX(',',string)+1,CHARINDEX(']',string)-CHARINDEX(',',string)-1) result2  
FROM #test  

OR:

;WITH cte  
as(  
SELECT LEFT(string,CHARINDEX(',',string)-1) result1,  
RIGHT(string,LEN(string)-CHARINDEX(',',string)) result2  
FROM #test)  

SELECT   
REVERSE(LEFT(REVERSE(result1),CHARINDEX('[',REVERSE(result1))-1))result1,  
LEFT(result2,CHARINDEX(']',result2)-1) result2  
FROM cte  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi @Kapil kumar velpuri ，

Welcome to the microsoft tsql Q&A forum!

Sorry, I don't quite understand what you want.
Do you need a function to extract specific values from data similar to <![CDATA[deLastName,uSKt133Ii]]>,<![CDATA[deFirstName,hq1t]]>?Does the business process you mentioned need tsql to implement?

For this type of problem we recommend that you post CREATE TABLE statements
for your tables together with INSERT statements with sample data,enough to illustrate all angles of the problem.
We also need to see the expected result of the sample.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.