正在解析SQL中的自由文本字段

Question

我的表中有一个字段，其中包含不同类型的数据，这些数据可以转换为不同的列或行。

自由文本字段	TypeID
{就诊信息}[<就诊日期>2022-01-01</就诊日期<就诊类型>临床</就诊类型<]{/就诊信息}{费用}[lt；实验室>30.91<实验室><邂逅>15.00<相遇>]{费用}	1
｛索引事件｝[lt；日期>2022-03-04</日期><诊断>I10</Diagnosis>]｛/索引事件｝
{就诊信息}[<就诊日期>2022-1012</就诊日期><就诊类型>行政管理</就诊类型>]{/就诊信息}{费用}[lt；咨询>25.00</Consultion>]{/费用}	1

如果您的数据看起来像这样，那么它将是有效的XML，并且很容易使用XML解析器进行解析

自由文本字段	TypeID
<Visit Info><Visit Date>2022-01-01</Visit Date><Visit Type>Clinical</Visit Type></Visit Info><Costs><Laboratory>30.91</Laboratory><Encounter>15.00</Encounter></Costs>	1
<Index Events><Date>2022-03-04</Date><Diagnosis>I10</Diagnosis></Index Events>	2
<Visit Info><Visit Date>2022-10-12</Visit Date><Visit Type>Administrative</Visit Type></Visit Info><Costs><Consultation>25.00</Consultation></Costs>

您可以使用子字符串做很多事情，比如：

WITH cte AS (
SELECt 'Visit Info' as category, 'Visit Date' as subcategory  UNION
SELECt 'Costs' as category, 'Laboratory' as subcategory 
)
SELECT 
typeid,
category,
subcategory,
SUBSTRING(ss,y1+len(subcategory)+2,y2-(y1+len(subcategory)+2)) as value
FROM (  
SELECT 
typeid,
category,
subcategory,
SUBSTRING(free,x1+len(category)+2,x2-(x1+len(category)+2)) as ss,
CHARINDEX(CONCAT('<',subcategory,'>'),SUBSTRING(free,x1+(len(category)+2),x2-x1+len(category)+2+1)) as y1,
CHARINDEX(CONCAT('</',subcategory,'>'),SUBSTRING(free,x1+(len(category)+2),x2-x1+len(category)+2+1)) as y2
FROM (
SELECT 
typeid,
category,
free,
subcategory,
CHARINDEX(CONCAT('{',category,'}'),free) as x1,
CHARINDEX(CONCAT('{/',category,'}'),free) as x2
FROM test
CROSS APPLY cte )x
WHERE x1<>0 and x2>x1
) y
WHERE y1<>0 and y2>y1 ;

其他类别和子类别可以以相同的方式进行

输出：

实验室就诊日期就诊日期

typeid	类别	子类别	值
1	成本	30.91
1	就诊信息	2022-01-01
1	就诊信息	2022-10-12