CODEWITHSUNDEEP
sql-serverxmlt-sql
Jojo Peter
Jojo Peter

Reputation: 151

SQL Server : How to parse xml having this particular format

I am trying to store XML with following format in SQL server XML type column.

<item Color="Green" Size="10" Category="test" />

Can anyone help with the SQL query to parse this. For example, I need to extract the value of key 'Color'.

Thanks in advance.

Upvotes: 0

Views: 39

Answers (1)

marc_s
marc_s

Reputation: 754488

You can try this:

DECLARE @tblXml TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @tblXml (ID, XmlContent)
VALUES (1, '<item Color="Green" Size="10" Category="test" />')

SELECT
    XmlContent.value('(/item/@Color)[1]', 'varchar(50)')
FROM 
    @tblXml x
WHERE
    ID = 1

This returns Green

Upvotes: 1

Related Questions