CODEWITHSUNDEEP
sqlsql-serversql-server-2008
siva
siva

Reputation: 1481

How to retrieve data from SQL Server from XML file

XML file

<?xml version="1.0" encoding="utf-8"?>
<Data>
  <ProductID>
    <Producer value="SomeName" />
    <Locality value="California" />
    <Chamber value="ATK-01B" />
    <System value="CAL-ATK-01B-01" />
  </ProductID>     
</Data>

The above is the XML file gets saved in the column content of a SQL Server table. I am using the below query to select the records from DB with value CAL-ATK-01B-01.

Query:

SELECT * 
FROM [Table name] 
WHERE [xmlBody].value('(/Data/ProductID/System)[1]', 'varchar(max)') LIKE 'CAL-ATK-01B-01';

I need to select all records with matching value in xml file as "CAL-ATK-01B-01". But the query I tried is returning empty value.

Can any one help me do this?

Thanks

Upvotes: 0

Views: 1109

Answers (1)

marc_s
marc_s

Reputation: 754438

You need to use this query here to actually select the value of the XML attribute value:

SELECT * 
FROM [Table Name]
WHERE [xmlBody].value('(/Data/ProductID/System/@value)[1]', 'varchar(100)') LIKE 'CAL-ATK-01B-01';

Upvotes: 1

Related Questions