如何从SQL查询中剥离XHTML标记

Question

我有以下查询快速7暴露SQL数据库，并一直试图删除xhtml，

SELECT
dp.policy_id, dp.title as policy_title, dpr.rule_id, dpr.title as policy_rule_title,
dp.benchmark_name, da.ip_address, da.host_name, dpr.description, dp.category,
fapr.date_tested, htmlToText(fapr.proof) as proof, fapr.compliance,
dpr.severity, htmlToText(dpr.rationale) as rationale, htmlToText(dpr.remediation) as remediation
FROM fact_asset_policy_rule fapr
JOIN dim_policy dp on dp.policy_id = fapr.policy_id
JOIN dim_policy_rule dpr on dpr.policy_id = fapr.policy_id and fapr.rule_id = dpr.rule_id
JOIN dim_asset da on da.asset_id = fapr.asset_id
WHERE fapr.compliance = false order by dp.title, dpr.title`

然而，它导出以下内容的基本原理和补救与以下xhtml标签，我想摆脱:

基本原理列示例:

<xhtml:p xmlns="http://checklists.nist.gov/xccdf/1.2" 
xmlns:ae="http://benchmarks.cisecurity.org/ae/0.5" 
xmlns:cc6="http://cisecurity.org/20-cc/v6.1" 
xmlns:cc7="http://cisecurity.org/20-cc/v7.0" 
xmlns:ciscf="https://benchmarks.cisecurity.org/ciscf/1.0" 
xmlns:notes="http://benchmarks.cisecurity.org/notes" 
xmlns:xccdf="http://checklists.nist.gov/xccdf/1.2" 
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Attack surface 
reduction helps prevent actions and apps that are typically used by 
exploit-seeking malware to infect machines.</xhtml:p>

我只想获得带有TEXT的输出"攻击面减少有助于防止通常被寻求利用的恶意软件用于感染机器的操作和应用"并删除其余的数据。

补救的例子:

<xhtml:div xmlns="http://checklists.nist.gov/xccdf/1.2"
xmlns:ae="http://benchmarks.cisecurity.org/ae/0.5" 
xmlns:cc6="http://cisecurity.org/20-cc/v6.1" 
xmlns:cc7="http://cisecurity.org/20-cc/v7.0" 
xmlns:ciscf="https://benchmarks.cisecurity.org/ciscf/1.0" 
xmlns:notes="http://benchmarks.cisecurity.org/notes" 
xmlns:xccdf="http://checklists.nist.gov/xccdf/1.2" 
xmlns:xhtml="http://www.w3.org/1999/xhtml" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <xhtml:p> 
<xhtml:p> To establish the recommended configuration via GP, set the 
following UI path to <xhtml:span 
class="inline_block">Enabled</xhtml:span> : </xhtml:p> <xhtml:code 
class="code_block">Computer ConfigurationPoliciesAdministrative 
TemplatesWindows ComponentsWindows Defender AntivirusWindows Defender 
Exploit GuardAttack Surface ReductionConfigure Attack Surface 
Reduction rules </xhtml:code> <xhtml:p> <xhtml:strong>Note: 
</xhtml:strong> This Group Policy path may not exist by default. It is 
provided by the Group Policy template <xhtml:span 
class="inline_block">WindowsDefender.admx/adml</xhtml:span> that is 
included with the Microsoft Windows 10 Release 1709 Administrative 
Templates (or newer). </xhtml:p> <xhtml:p class="bold">Impact:</xhtml:p> 
<xhtml:p> <xhtml:p>When a rule is triggered, a notification will be 
displayed from the Action Center.</xhtml:p> </xhtml:p> </xhtml:p> 
</xhtml:div>

在没有XHTML和xmlns的情况下，

需要输出干净的修正，因此干净的文本应该如下所示:

To establish the recommended configuration via GP, set the following UI path to Enabled : Computer ConfigurationPoliciesAdministrative TemplatesWindows ComponentsWindows Defender AntivirusWindows Defender Exploit GuardAttack Surface ReductionConfigure Attack Surface Reduction rules, This Group Policy path may not exist by default. It is provided by the Group Policy template WindowsDefender.admx/adml that is included with the Microsoft Windows 10 Release 1709 Administrative Templates (or newer).
Impact: When a rule is triggered, a notification will be displayed from the Action Center.

有一种方法，我可以得到干净的HTML文本?没有我不需要的内容?

Answer 1

是否有办法让我干净的HTML文本?

如果它是完全有效的XHTML，则可以将其转换为XML，然后使用XPath选择感兴趣的节点值。

declare @xhtml varchar(max)
set @xhtml = '<xhtml:div xmlns="http://checklists.nist.gov/xccdf/1.2" xmlns:ae="http://benchmarks.cisecurity.org/ae/0.5" xmlns:cc6="http://cisecurity.org/20-cc/v6.1" xmlns:cc7="http://cisecurity.org/20-cc/v7.0" xmlns:ciscf="https://benchmarks.cisecurity.org/ciscf/1.0" xmlns:notes="http://benchmarks.cisecurity.org/notes" xmlns:xccdf="http://checklists.nist.gov/xccdf/1.2" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">     <xhtml:p>         <xhtml:p>To establish the recommended configuration via GP, set the following UI path to <xhtml:span class="inline_block">Enabled</xhtml:span> : </xhtml:p>         <xhtml:code class="code_block">Computer ConfigurationPoliciesAdministrative TemplatesWindows ComponentsWindows Defender AntivirusWindows Defender Exploit GuardAttack Surface ReductionConfigure Attack Surface Reduction rules </xhtml:code> <xhtml:p> <xhtml:strong>Note:</xhtml:strong> This Group Policy path may not exist by default. It is provided by the Group Policy template <xhtml:span class="inline_block">WindowsDefender.admx/adml</xhtml:span> that is included with the Microsoft Windows 10 Release 1709 Administrative Templates (or newer). </xhtml:p>         <xhtml:p class="bold">Impact:</xhtml:p> <xhtml:p> <xhtml:p>When a rule is triggered, a notification will be displayed from the Action Center.</xhtml:p>     </xhtml:p>     </xhtml:p>     </xhtml:div>'
;with xmlnamespaces('http://www.w3.org/1999/xhtml' as xhtml) 
select (cast(@xhtml as xml)).value('(//xhtml:p)[1]', 'varchar(max)')

看到http://sqlfiddle.com/! 18/9eecb/136834。

如果需要，可以使用更具体的XPath。对于比"单个节点的值"更复杂的需求;你也可以切换到XQuery。