SQL Server script convert xml attributes and text value into table

Question

I have a table testA which contains 4 columns, and column Dv_Data is an xml string.

testA

ID   X  Y  Dv_Data
---------------------------------------------------------------------------
101  4  5  <U><V N="TTF">1</V><V N="MF">0</V><V N="ResultA">383.501</V></U>
102  4  6  <U><V N="TTF">0</V><V N="MF">1</V><V N="ResultA">383.601</V></U>

The question is, how can I achieve this result with a SQL script?

ID  X  Y  TTF  MF  ResultA
---------------------------
101 4  5  1    0   383.501
102 4  6  0    1   383.601

Table testA has hundreds of rows.

Appreciate your valuable input.

ZLK · Accepted Answer · 2017-06-08 22:09:12Z

1

You could use XML to derive the column names / values and pivot the results. For example:

DECLARE @TestA TABLE (ID INT, X INT, Y INT, Dv_Data XML);
INSERT @TestA VALUES 
(101, 4, 5, '<U><V N="TTF">1</V><V N="MF">0</V><V N="ResultA">383.501</V></U>'),
(102, 4, 6, '<U><V N="TTF">0</V><V N="MF">1</V><V N="ResultA">383.601</V></U>');

SELECT *
FROM
(
    SELECT T.ID,
           T.X, 
           T.Y, 
           ColName = A.B.value('@N', 'VARCHAR(MAX)'), 
           ColVal = A.B.value('text()[1]', 'VARCHAR(MAX)')
    FROM @TestA AS T
    CROSS APPLY T.Dv_Data.nodes('U/V') AS A(B)
) AS T
PIVOT
(
    MAX(ColVal) FOR ColName IN ([TTF], [MF], [ResultA])
) AS P;

answered Jun 8, 2017 at 22:09

ZLK

2,8941 gold badge13 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

SQL Server script convert xml attributes and text value into table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related