1

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.

Improve this question
12
  • 1
    "where is stored xml as nvarchar." Why are you storing XML in an nvarchar when there's an xml data type. Commented Jun 7, 2021 at 12:37
  • Also, do you really just want 8 columns with the varchar value 'number' in them? If so, I don't see why you need to read the XML do that that, you could just do SELECT 'number' AS x1, 'number' AS y1, ..., 'number' AS x4, 'number' AS y4; Commented Jun 7, 2021 at 12:38
  • @Larnu This is due to the origin of this data from the software that generates the data. Unfortunately, I am not able to influence this property. Maximum using integration services, but there will still be a problem with parsing. I need the given values from that column(XmlCurves) in the given tag(FloatPoints). I don't care which way. But these values are stored only in this column, which is XML. But it is preserved as an nvarchar :-( I need the given values from that column(XmlCurves) in the given tag(FloatPoints). I don't care which way. Commented Jun 7, 2021 at 12:44
  • So what is your actual expected results here? Commented Jun 7, 2021 at 12:47
  • 1
    @Charlieface db<>fiddle Commented Jun 7, 2021 at 13:13

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.