SQL query from XML Column stored as nvarchar

Question

i need help with sql query. I have table with column, where is stored xml as nvarchar.

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

All XML looks like:

   <?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>

And i need built SQL query, which select data in first tag FloatPoints: <FloatPoints>0;4.965425|0.01004791;5.438642|0.01076508;5.50529|0.01112366;5.485298</Floatpoint>

For explanation:

<FloatPoints>x1;y1|x2;y2|x3;y3|x4;y4</FloatPoints>

Therefore, I would like this result:

x1	y1	x2	y2	x3	y3	x4	y4
0	4.965425	0.01004791	5.438642	0.01076508	5.50529	0.01112366	5.485298

If at all possible.

Answer 1

Leaving encoding apart, the problem is preserving item positions while parsing a FloatPoints tag. Unfortunately string_split is not guaranteed to preserve item positions so you may need a custom splitter ( DelimitedSplit8K_LEAD here) which returns item position as well.

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<>fiddle

Includes DelimitedSplit8K_LEAD.

Answer 2

This answer has a few assumptions:

• The XML is always a valid xml value.
• There are always 4 xy coordinates
• That the lengths of x & y's values cannot be longer than 127 characters.
• The value wanted it in the node ActualCurve
• You are on a version that supports TRANSLATE (if not, use nested REPLACEs)

With this assumptions in place you can do the following:

1. Convert the nvarchar to a valid xml value
2. Extract the value of Curve/ActualCurve/FloatPoints
3. Replace the characters to create a value that can use PARSENAME to separate the separate parts into
4. Use CHARINDEX to get the correct left/right part of the string
5. CONVERT back to a 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<>fiddle