存储为nvarchar的XML列中的SQL查询

Question

我需要sql查询方面的帮助。我有一个带列的表，其中存储xml作为nvarchar。

XmlCurves<?xml version="1.0" encoding="Windows-1252"?>

Answer 1

这个答案有几个假设：

• XML始终是有效的xml值
• 总有4个xy坐标
• x&y的值不能超过127个字符
• 节点ActualCurve中需要它的值
• 您所在的版本支持TRANSLATE(如果不支持，请使用嵌套的REPLACE(

有了这些假设，您可以执行以下操作：

1. 将nvarchar转换为有效的xml值
2. 提取Curve/ActualCurve/FloatPoints的值
3. 替换字符以创建一个值，该值可以使用PARSENAME将单独的部分分隔为
4. 使用CHARINDEX获取字符串的正确左/右部分
5. CONVERT返回到float

DECLARE @XMLLikeString nvarchar(MAX) = '<?xml version="1.0" encoding="Windows-1252"?>
<Curve xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Anchor>Begin</Anchor>
<LoadOnlyNOKCurves>false</LoadOnlyNOKCurves>
<Teachmode>false</Teachmode>
<ActualCurve>
<FloatPoints>0;4.965425|0.01004791;5.438642|0.01076508;5.50529|0.01112366;5.485298</FloatPoints>
</ActualCurve>
<AbsOffset>
<X>88.21842</X>
<Y>0</Y>
</AbsOffset>
<UpperLimit>
<FloatPoints>9.456265E-05;5.023251|0.008893617;5.502031</FloatPoints>
</UpperLimit>
<LowerLimit>
<FloatPoints>0.0008510638;4.906321|0.01408511;5.495711</FloatPoints>
</LowerLimit>
<ULViolationCurveIdx>-1</ULViolationCurveIdx>
<ULViolationULIdx>-1</ULViolationULIdx>
<LLViolationCurveIdx>-1</LLViolationCurveIdx>
<LLViolationLLIdx>-1</LLViolationLLIdx>
<SIO_X>
<Id>1</Id>
<Alias>Position</Alias>
<Unit>mm</Unit>
</SIO_X>
<SIO_Y>
<Id>2</Id>
<Alias>Force</Alias>
<Unit>kN</Unit>
</SIO_Y>
</Curve>';
SELECT CONVERT(float,LEFT(xy1,CHARINDEX(';',xy1)-1)) AS x1, 
CONVERT(float,STUFF(xy1,1,CHARINDEX(';',xy1),'')) AS y1,
CONVERT(float,LEFT(xy2,CHARINDEX(';',xy2)-1)) AS x2, 
CONVERT(float,STUFF(xy2,1,CHARINDEX(';',xy2),'')) AS y2,
CONVERT(float,LEFT(xy3,CHARINDEX(';',xy3)-1)) AS x3, 
CONVERT(float,STUFF(xy3,1,CHARINDEX(';',xy3),'')) AS y3,
CONVERT(float,LEFT(xy4,CHARINDEX(';',xy4)-1)) AS x4, 
CONVERT(float,STUFF(xy4,1,CHARINDEX(';',xy4),'')) AS y4
FROM (VALUES(@XMLLikeString))V(XMLLikeString)
CROSS APPLY (VALUES(TRY_CONVERT(xml,CONVERT(varchar(MAX),V.XMLLikeString))))TC(XML)
CROSS APPLY (VALUES(TC.XML.value('(Curve/ActualCurve/FloatPoints/text())[1]','varchar(4000)')))AC(FloatPoints)
CROSS APPLY (VALUES(TRANSLATE(AC.FloatPoints,'.|',',.')))T(FloatPoints)
CROSS APPLY (VALUES(REPLACE(PARSENAME(T.FloatPoints,4),',','.'),REPLACE(PARSENAME(T.FloatPoints,3),',','.'),REPLACE(PARSENAME(T.FloatPoints,2),',','.'),REPLACE(PARSENAME(T.FloatPoints,1),',','.')))PN(xy1,xy2,xy3,xy4);

db<gt；小提琴

Answer 2

抛开编码不谈，问题是在解析FloatPoints标记时保留项位置。不幸的是，string_split不能保证保留项目位置，因此您可能需要一个自定义拆分器(此处为DelimitedSplit8K_LEAD(，它也会返回项目位置。

select id,
Max(case t2.itemnumber*100 + t3.itemnumber when 101 then cast(t3.Item as decimal(10,6)) end) x1,
Max(case t2.itemnumber*100 + t3.itemnumber when 102 then cast(t3.Item as decimal(10,6)) end) y1,
Max(case t2.itemnumber*100 + t3.itemnumber when 201 then cast(t3.Item as decimal(10,6)) end) x2,
Max(case t2.itemnumber*100 + t3.itemnumber when 202 then cast(t3.Item as decimal(10,6)) end) y2
...
from (select id, cast(txn_message as xml) x
from tbl) a
cross apply a.x.nodes('Curve/ActualCurve/FloatPoints') t(n)
cross apply DelimitedSplit8K_LEAD(t.n.value('.[1]', 'varchar(200)') , '|') t2
cross apply DelimitedSplit8K_LEAD(t2.Item, ';') t3
group by id;

db<gt；小提琴

包括DelimitedSplit8K_LEAD。