试图从乱码的XML数据中获取不同数量的值

Question

我在一个表中有一个列'rawresponse‘，它的xml输出非常混乱。我试图从输出中提取值，在输出中，值的前面总是有相同的文本字符串，但是值可以多次出现。

例如，假设我想获得strengthValue的值，我可能有一条记录，其中'rawresponse‘列将读到：

{“头”：{“to”：{“限定符”：“ZZZ”，"text":"P00000000022805"}"strength":{"strengthValue":"80.0"，“strengthForm”：{“代码”：“包”}}，“数量”：{“值”：“3.0”}

另一种可能是：

{"header":{"to":{"qualifier":"ZZZ"}"strength":{"strengthValue":"80.0"，“strengthForm”：{“strengthForm”：“包”}}，"quantity":{"value":"3.0"}"strength":{"strengthValue":"1.46"，“strengthForm”：{“代码”：“包”}}，“强度”：{“strengthValue”：“245.0”，“strengthForm”：{“代码”：“包”}}，"quantity":{"value":"3.0"}"strength":{"strengthValue":"80.0"}

因此，在第一个例子中，强度值只发生一次，该值在十进制之前有两位数，在后面有一个数字。在第二种情况下，它发生了四次(每一次都不同--这是完全不标准的)，小数点前后的数字数都不一样。

我试图用我在这里找到的另一个解决方案来解决这个问题，使用patindex和substring，但是无法让它工作。

实际上，我想要的输出(CTE或任何最终需要做的事情，例如获取最大值，或计算某些值发生的次数)将是RecordID (另一列)，而strengthValue的每个值用于该recordID，例如

RecordID    Value
2-AAf-9     22.4
23-T-00     1.4
23-T-00     80.0
23-T-00     146.98
23-T-00     22.001

有什么建议吗？

Answer 1

如果向TVF开放

厌倦了提取字符串(左、右、字符等)，我修改了一个解析/拆分函数，以接受两个不类似的分隔符。在你的例子中，'strengthValue":"'和'"'

不完全确定您想要的结果如何同步示例数据。

示例或在 dbFiddle上看到它

Declare @YourTable table (RecordID varchar(50),rawresponse varchar(max))
Insert Into @YourTable values
 ('2-AAf-9','{"header":{"to":{"qualifier":"ZZZ","text":"P00000000022805"}"strength":{"strengthValue":"80.0","strengthForm":{"code":"package"}}},"quantity":{"value":"3.0"}')
,('23-T-00','{"header":{"to":{"qualifier":"ZZZ"}"strength":{"strengthValue":"80.0","strengthForm":{"code":"package"}}},"quantity":{"value":"3.0"}"strength":{"strengthValue":"1.46","strengthForm":{"code":"package"}}},"strength":{"strengthValue":"245.0","strengthForm":{"code":"package"}}},"quantity":{"value":"3.0"}"strength":{"strengthValue":"80.0"}')

Select A.RecordID
      ,Value = try_convert(decimal(10,2),B.RetVal)
 From  @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract](rawresponse,'strengthValue":"','"') B
 Where try_convert(money,B.RetVal) is not null

返回

RecordID    Value
2-AAf-9     80.00
23-T-00     80.00
23-T-00     1.46
23-T-00     245.00
23-T-00     80.00

如果感兴趣的话，可以使用。。

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

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1

)
/*
Max Length of String 1MM characters

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

BTW -我删除了关于的第一条评论，而不是XML

Answer 2

这里有一个使用NGrams8K的解决方案。

-- sample data
DECLARE @yourTable TABLE (recordID VARCHAR(20), rawresponse VARCHAR(8000));
INSERT @yourTable(recordID, rawresponse)
VALUES
('2-AAf-9','{"header":{"to":{"qualifier":"ZZZ","text":"P00000000022805"}"strength":{"strengthValue":"80.0","strengthForm":{"code":"package"}}},"quantity":{"value":"3.0"}'),
('23-T-00','{"header":{"to":{"qualifier":"ZZZ"}"strength":{"strengthValue":"80.0","strengthForm":{"code":"package"}}},"quantity":{"value":"3.0"}"strength":{"strengthValue":"1.46","strengthForm":{"code":"package"}}},"strength":{"strengthValue":"245.0","strengthForm":{"code":"package"}}},"quantity":{"value":"3.0"}"strength":{"strengthValue":"80.0"}');

-- solution
SELECT 
  t.recordID, 
  strengthValue = SUBSTRING(string.part,1,CHARINDEX('"', string.part)-1)
FROM @yourTable t
CROSS JOIN (VALUES ('"strengthValue":"')) f(searchTxt)
CROSS APPLY dbo.ngrams8k(t.rawresponse, LEN(f.searchTxt)) ng
CROSS APPLY (VALUES (SUBSTRING(t.rawresponse, ng.position+LEN(f.searchTxt),30))) string(part)
WHERE ng.token = f.searchTxt;

结果：

recordID             strengthValue
-------------------- ------------------------------
2-AAf-9              80.0
23-T-00              80.0
23-T-00              1.46
23-T-00              245.0
23-T-00              80.0