XML Column Extract ![CDATA[ ]]

Question

I am running the below query to extract data from an xml column...question is how to extract data that is in between ![CDATA[""]] ?

select        
     CAST(xml as xml).value('(//@nodeName)[1]','nvarchar(20)') as NodeName,       
     CAST(xml as xml).value('(//![CDATA [prdDetDesc]])[1]','nvarchar(225)') as DetDesc,
     CAST(xml as xml).value('(//prdImg)[1]','nvarchar(1000)') as prdImage
from [dbo].[cmsContentXml])

I need to extract data that is present between [[""]]

Thanks in advance

Could you please provide some sample XML Data that shows what your XML looks like and what your desired output should be. — Mikael Eriksson, Aug 04 '11 at 13:08
<![CDATA[Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions but who want to show their breadth of technology experience, such as developers, systems administrators, and others. ]]>/m/967558/40.jpgSlmnoasp - I want to extract data that is present between ![CDATA[]] — Gallop, Aug 04 '11 at 13:14

score 4 · Accepted Answer · edited Jan 18 '21 at 12:30

There is nothing special with CDATA sections.

declare @xml xml = 
'<productDetailsDescription>
   <![CDATA[Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions but who want to show their breadth of technology experience, such as developers, systems administrators, and others. ]]>
 </productDetailsDescription>
 <productImage>/m/967558/40.jpg</productImage>
 <application>Slmnoasp</application>'
 
select @xml.value('/productDetailsDescription[1]', 'nvarchar(225)')

It also handles mixed values.

declare @xml xml = 
'<root>123<![CDATA[ABD]]>456</root>'  
 
select @xml.value('/root[1]', 'nvarchar(10)')

Result:

(No column name)
123ABD456

Edit

From a table instead of a variable with a cast to XML:

select cast(xml as xml).value('/productDetailsDescription[1]', 'nvarchar(max)') as productDetailsDescription
from YourTable

Try it here: https://data.stackexchange.com/stackoverflow/q/108293/

Edit 2

You need to specify the node names in the query. You also have to decide if you should have the different nodes in the same column or if they should be in different columns. Below I show you how you can do both.

declare @T table(xml nvarchar(max))

insert into @T values
('<productDetailsDescription>
   <![CDATA[Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions but who want to show their breadth of technology experience, such as developers, systems administrators, and others. ]]>
 </productDetailsDescription>
 <productImage>/m/967558/40.jpg</productImage>
 <application>Slmnoasp</application>')

insert into @T values
('<detailDescription>
   <![CDATA[Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions but who want to show their breadth of technology experience, such as developers, systems administrators, and others. ]]>
 </detailDescription>
 <productImage>/m/967558/40.jpg</productImage>
 <application>Slmnoasp</application>')

-- Get detailDescription in a column of its own
select 
  cast(xml as xml).value('/productDetailsDescription[1]', 'nvarchar(max)') as productDetailsDescription,
  cast(xml as xml).value('/detailDescription[1]', 'nvarchar(max)') as detailDescription
from @T

-- Get detailDescription in the same column as productDetailsDescription
select 
  cast(xml as xml).value('/*[local-name()=("productDetailsDescription","detailDescription")][1]', 'nvarchar(max)') as detailDescription
from @T

Thanks for you response...I have not declared any variables...so just looking on extracting data through existing query...kindly help editing the CAST(xml as xml).value('(//![CDATA [productDetailsDescription]])[1]','nvarchar(225)') as prDetDesc line in the query since I am getting null as the record.Thanks — Gallop, Aug 04 '11 at 13:33
Data is being returned as NULL for the productDetailsDescription field I am using this statement - CAST(xml as xml).value('/productDetailsDescription[1]','varchar(max)')as productDetailsDescription — Gallop, Aug 04 '11 at 13:46
@user If you get NULL it probably means that your XML does not look like this sample. The first single `/` in the path specifies the root so `productDetailsDescription` has to be a root node. If it is not, the best you could do for performance sake is to add the full path. Next best would be to use two `//` instead of only one to do a search in the XML for the node. — Mikael Eriksson, Aug 04 '11 at 13:59
Thanks Mikael...as you say I am getting records for some nodes....but for some I am not getting <![CDATA[TLStandard description values]]> - this is the format for which data is not being extracted — Gallop, Aug 04 '11 at 14:07
@user - You need to specify the nodes you want in the query. I have updated the answer. — Mikael Eriksson, Aug 04 '11 at 15:15
select CAST(xml as xml).value('(//@nodeName)[1]','nvarchar(20)') as NodeName, CAST(xml as xml).value('(//![CDATA [productDetailsDescription]])[1]','nvarchar(225)') as prDetDesc, CAST(xml as xml).value('(//prdImage)[1]','nvarchar(1000)') as prdImage from [dbo].[cmsContentXml])as hierarchy where child = order by 4 - Also if I use these queries,how do I pass parameters to the @nodeName/productDetailsDescription/prdImage - I may want to set these as filters during runtime?? — Gallop, Aug 05 '11 at 04:42
@user - Have a look at `sql:variable()` function. http://msdn.microsoft.com/en-us/library/ms188254.aspx — Mikael Eriksson, Aug 05 '11 at 05:41
@ Mikael, A.xml.value(''(/' + alias + '/brief)[1]'',''nvarchar(225)'') as briefDescription, A.xml.value(''(/' + alias + '/detail)[1]'',''nvarchar(1000)'') as detailDescription, A.xml.value(''data[alias="Cat"][1]'',''nvarchar(225)'') as Number - Is this how parameters are to be passed?? — Gallop, Aug 18 '11 at 11:40
@user You can have a look at this question about how to use variables. http://stackoverflow.com/questions/7024519/xquery-how-to-use-the-sqlvariable-in-value-function — Mikael Eriksson, Aug 18 '11 at 12:41

XML Column Extract ![CDATA[ ]]

1 Answers1