SQL Server & XML: how can I get an xml attribute from sql table?

Question

I have a table like this:

create table product 
(
    id integer, 
    category varchar(50), 
    quantity integer, 
    techdata varchar(100), 
    cost_price float
);

insert into product 
values (1, 'window', 2, '<Data w="1000" h="1000"/>', 100.56),
       (2, 'door', 1, '<Data w="900" h="1800"/>', 96.12),
       (3, 'window', 20, '<Data w="750" h="300"/>', 152.5),
       (4, 'door', 100, '<Data w="1046" h="2046"/>', 46.74),
       (5, 'window', 1, null, null);

I have to select all 'w' and 'h' attribute values from those rows. I've tried this solution but it doesn't work.

SELECT 
    t.p.value('(@w)[1]', 'VARCHAR(50)') AS width,
    t.p.value('(@h)[1]', 'VARCHAR(50)') AS height
FROM 
    product 
CROSS APPLY 
    techdata.nodes('/Data') t(p)

Any hint for proper solution?

"it doesn't work" is not a good description of the problem. You get error or bad results ? What results do you expect ? — VBoka
– VBoka, Commented Mar 5, 2020 at 21:19
"The XMLDT method 'nodes' can only be invoked on columns of type xml." - that's the error which I got. — adros
– adros, Commented Mar 5, 2020 at 21:34

mituw16 · Accepted Answer · 2020-03-05 20:56:50Z

2

I think your issue is that the column techdata is not XML type.

Try this

select
    CONVERT(xml, techdata).value('(/Data/@w)[1]', 'int') as Width,
    CONVERT(xml, techdata).value('(/Data/@h)[1]', 'int') as Height
from product

answered Mar 5, 2020 at 20:56

mituw16

5,2784 gold badges31 silver badges56 bronze badges

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

1 Comment

adros Over a year ago

It works - You're right, column is varchar not xml, so it must be converted to proper data type. Thank's a lot!

Collectives™ on Stack Overflow

SQL Server & XML: how can I get an xml attribute from sql table?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related