Retrieving a string of Text from XML

1

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

2

Please provide sample xml data

3

Sorry,

4

XML
XML3766×688 127 KB

Sorry some problems posting the xml. Here is the code

5 
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

6

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

7 
;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%'
8

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

Thanks

Vinnie

9

:face_with_raised_eyebrow: try the last answer boss