Parse xml data using T-SQL-CodePudding

DECLARE @xml xml SET @xml = '<row Col1='abc' Col2='def' Col3='123' /> <row Col1='aaa' Col2='bbb' Col3='111'/>

I need SQL Query to extract Col1, Col2, Col3 values. I don't know how to do that since the data is not in traditional format as below

<row> <col1>'abc'</col1> <col2>'def'</col2> <col3>'123'</col3> </row>

Also, I apologize but I'm having hard time posting the details due to formatting issues with body of the message. StackOverflow is not simple for first time user and people who are new coding.

MS SQL Server 2012 or later.

Thanks.

CodePudding user response：

Please try the following solution.

A minimal reproducible example is not provided. So, I am shooting from the hip.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
('<row Col1="abc" Col2="def" Col3="123"/>'),
('<row Col1="aaa" Col2="bbb" Col3="111"/>');
-- DDL and sample data population, end

SELECT t.*
    , c.value('@Col1', 'VARCHAR(20)') AS Col1
    , c.value('@Col2', 'VARCHAR(20)') AS Col2
    , c.value('@Col3', 'INT') AS Col3
FROM @tbl AS t
CROSS APPLY xmldata.nodes('/row') AS t1(c);

Output

 ---- ------------------------------------------ ------ ------ ------ 
| ID |                 xmldata                  | Col1 | Col2 | Col3 |
 ---- ------------------------------------------ ------ ------ ------ 
|  1 | <row Col1="abc" Col2="def" Col3="123" /> | abc  | def  |  123 |
|  2 | <row Col1="aaa" Col2="bbb" Col3="111" /> | aaa  | bbb  |  111 |
 ---- ------------------------------------------ ------ ------ ------