CODEWITHSUNDEEP
sqlsql-serverxml
adros
adros

Reputation: 13

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

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?

Upvotes: 0

Views: 42

Answers (1)

mituw16
mituw16

Reputation: 5250

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

Upvotes: 2

Related Questions