Search string in xml

How to search for string within xml column in SQL table?

As you can see here we have a sample table having categories and products associated with each category in xml column named products.

CREATE TABLE ProductDetails 
(CategoryId INT, Category VARCHAR(20),Products XML)

INSERT INTO ProductDetails (CategoryId, Category, Products)
SELECT 1,'Phones',
'<Products>
<Product>
<Name>IPhone 5s</Name>
<Quantity>70</Quantity>
</Product>
<Product>
<Name>IPhone 11</Name>
<Quantity>300</Quantity>
</Product>
</Products>'

INSERT INTO ProductDetails (CategoryId, Category, Products)
SELECT 2,'Furniture',
'<Products>
<Product>
<Name>Sofa set</Name>
<Quantity>178</Quantity>
</Product>
<Product>
<Name>Dinning table</Name>
<Quantity>1300</Quantity>
</Product>
<Product>
<Name>Almirah</Name>
<Quantity>1000</Quantity>
</Product>
</Products>' 
INSERT INTO ProductDetails (CategoryId, Category, Products)

SELECT 3,'Computers',
'<Products>
<Product>
<Name>Dell</Name>
<Quantity>1678</Quantity>
</Product>
<Product>
<Name>Samsung</Name>
<Quantity>1800</Quantity>
</Product>
<Product>
<Name>Lenevo</Name>
<Quantity>1940</Quantity>
</Product>
</Products>'

SELECT * FROM ProductDetails

Lets run the above query and see the output.

You can see it returns a category and products details associated with category in xml column.

Lets search for products having quantity 1300 within xml column products.

Following query returns only those products having quantity = 1300.

SELECT *
FROM ProductDetails
WHERE Products.exist('Products/Product[Quantity= 1300]') = 1

 

Lets search for products having name “IPhone 5s” within xml column products.

Following query returns only those products having name as “IPhone 5s”.

SELECT *
FROM ProductDetails
WHERE Products.exist('Products/Product[Name = "IPhone 5s"]') = 1

Lets search for product name which contains string “Sofa”.

Following query returns only those products whose name contains string “Sofa”.

SELECT *
FROM ProductDetails
WHERE Products.exist('Products/Product/Name[contains(.,"Sofa")]') = 1

Lets search for product name “Dell” and quantity is 200.

You can see does not return any row that means there is no record for this search criteria.

Lets change the quantity >100 in above query then see the output.

You can see this time it returns product having name “Dell” and quantity value > 100.

SELECT *
FROM ProductDetails
WHERE Products.exist('Products/Product[Name = "Dell"][Quantity > 100]')
 = 1

Also Read..

FOR XML

 3,580 total views,  2 views today