时间戳

首页 > 解决方案 > 如何在 SQL Server 中查询所有出现的 XML 列的标题

sql - 如何在 SQL Server 中查询所有出现的 XML 列的标题

问题描述

我有一个表，其中有一列名为 [xml] 的类型为 varchar(max) 列，每行都有 HTML 表，例如：

<table>
<tr>
<th type="System.DateTime">Date</th>
<th type="System.String">Name</th>
<th type="System.String">Address</th>
<th type="System.Double">Age</th>
</tr>
<tr>
<td>26/04/2018 00:00:00</td>
<td></td><td>Alice</td>
<td>NYC</td>
<td>10</td>
</tr>
<tr>
<td>26/04/2018 00:00:00</td>
<td></td><td>Bob</td>
<td>LA</td>
<td>21</td>
</tr>
</table>

在 Microsoft SQL Server 2008 R2 中。

我想知道表头出现了多少次。我有不一致之处，因为所有标题都应该是相同的，但事实并非如此。我必须发现在哪里看。预期的结果将类似于：

HEADER|COUNTER
Name|52
Age|52
Date|50
Address|50
DT|2
City|2

我正在尝试使用问题（SQL）识别字段中多次出现的字符串格式的位置 ，使用此正则表达式：<th[^>]+>[^<]+<\/th>。但即使我尝试select * where [XML] like '%<th[^>]+>[^<]+<\/th>%'它也不起作用。我使用Regex101创建了这个模式。

标签： sqlsql-server

解决方案

如果对辅助功能感兴趣。

例子

Declare @YourTable table (ID int,[XML] varchar(max))
Insert Into @YourTable values
 (1,'<table><tr><th type="System.DateTime">Date</th><th type="System.String">Name</th><th type="System.String">Address</th><th type="System.String">Additional Info</th><th type="System.Double">Age</th></tr><tr>...</tr></table>')
,(2,'<table><tr><th type="System.DateTime">Date</th><th type="System.String">Name</th></tr><tr>...</tr></table>')

Select Header = RetVal
      ,Counter = sum(1)
 From  @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract]([XML],'>','</th') 
 Group By RetVal

退货

Header           Counter
Additional Info  1
Address          1
Age              1
Date             2
Name             2

感兴趣的功能

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  ( values (convert(xml,'<x>' + replace((Select replace(@String,@Delim1,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>').query('.'))) as A(XMLData)
            Cross Apply XMLData.nodes('x') AS B(i)
          ) C1
    Where charindex(@Delim2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/

推荐阅读