SQLTeam.com | Weblogs | Forums

Retrieving a string of Text from XML

Hello All,

I have an nvarchar(max) column that contains the following xml code:

I need my query to retrieve the text in square brackets after the last CDATA. "Insufficient verification" in the example above. I am inexperienced with XML so I am not sure how to handle this in my query.

The column is called Comments so I have

SELECT n.comments

FROM Notes n

I don't think I can use a substring because the text will not always be "Insufficient Verification" but the result I want is always the text in square brackets after the last CDATA.

Any help would be much appreciated.

Thanks

Vinnie

Please provide sample xml data

Sorry,

Sorry some problems posting the xml. Here is the code

declare @data varchar(max) = 
'<xmlPanelData>
	<field tagname="Chicken"><val1><![CDATA[false]]></val1></field>
	<field tagname="Chicken"><val1><![CDATA[Dark Web]]></val1></field>
	<field tagname="Chicken"><val1><![CDATA[Seahawks SB Champs 2020]]></val1></field>
</xmlPanelData>'



DECLARE @r TABLE (jamonit XML)
INSERT INTO @r 
select @data


SELECT c.query('data(val1)')
  FROM @r r 
 CROSS APPLY jamonit.nodes('xmlPanelData/field') x(c)


--OR

;with src
as
(
select cast(@data as xml) as xmldata
)
select cast(c.query('data(val1)') as varchar(max)) as shimsham
  From src
  CROSS APPLY xmldata.nodes('xmlPanelData/field') x(c)

image

Hi yosiasz. Thank you for your response. I can see this works when you create a table from scratch.

I am using other tables in the query. How would you incorporate your code and variables into the query below?

SELECT

n.comments,

r.code,

p.user

FROM notes n

INNER JOIN reason r

ON n.code = r.code

INNER JOIN person p

ON p.user = n.user

WHERE r.code LIKE ‘QC%’

n.comments is the column where I want to bring back the text from the xml code.

Thanks

Vinnie

;with src 
as
(
	select code, user, cast(comments as xml) as comments
	  from notes
)
SELECT c.query('data(val1)') as comments,
       r.code,
       p.user
  FROM src n
 INNER JOIN reason r ON n.code = r.code
 INNER JOIN person p ON p.user = n.user
 CROSS APPLY comments.nodes('xmlPanelData/field') x(c)

 WHERE r.code LIKE 'QC%'

Do I still need to declare variables at the start of the query?

Thanks

Vinnie

:face_with_raised_eyebrow: try the last answer boss