How to retrieve data from SQL Server from XML file

Question

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

marc_s · Accepted Answer · 2015-03-05 18:03:19Z

1

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';

answered Mar 5, 2015 at 18:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

How to retrieve data from SQL Server from XML file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related