XML 列提取 ![CDATA[ ]]

Question

我正在运行以下查询以从 xml 列中提取数据...问题是如何提取 ![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])

我需要提取 [[""]] 之间存在的数据

提前致谢

Answer 1

CDATA 部分没有什么特别的。

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)')

它还处理混合值。

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

结果：

(No column name)
123ABD456

编辑

从表而不是转换为 XML 的变量：

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

在此处尝试：https://data.stackexchange.com/stackoverflow/q/108293/

编辑2

您需要在查询中指定节点名称。您还必须决定是否应该将不同的节点放在同一列中，或者它们是否应该放在不同的列中。下面我将向您展示如何做到这两点。

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