For some reasons you may need store information in your database as XML format, SQL offers this data-type and even when this is a native data-type and offers advanced data handling, it has some differences and constraints at the time that you want to know/see/query your information. With this kinf of columns you cannot perform the same “normal” queries for the other relation data.
So let’s examine which options we have to see what’s in our database.
Here is an example of our table that contains XML column:
CREATE TABLE ProductReviews (
ProductId INT,
Reviews XML
)
Now that we have the table we can add some information to it. Run the following queries
INSERT INTO ProductReviews
VALUES (1,
'<Product>
<ProductId>1</ProductId>
<Reviews>
<Review>
<CustomerId>1</CustomerId>
<Desc>This is awesome</Desc>
</Review>
</Reviews>
</Product>')
INSERT INTO ProductReviews
VALUES (2,
'<Product>
<ProductId>2</ProductId>
<Reviews>
<Review>
<CustomerId>2</CustomerId>
<Desc>Dont purchase this product, is waste of money</Desc>
</Review>
</Reviews>
</Product>')
INSERT INTO ProductReviews
VALUES (3,
'<Product>
<ProductId>3</ProductId>
<Reviews>
<Review>
<CustomerId>1</CustomerId>
<Desc>This product is ok, 4 out of 5</Desc>
</Review>
<Review>
<CustomerId>2</CustomerId>
<Desc>Best product ever</Desc>
</Review>
<Review>
<CustomerId>3</CustomerId>
<Desc>The quality of this product is amazing but can offer more</Desc>
</Review>
</Reviews>
</Product>')
AS you can see we have three rows, if you get all the content for that table, you can get the next results:

SELECT * FROM ProductReviews
Let’s start with XQuery.
One of the most simple approach to know what is stored in that field is the value function in which only we need specify the hierarchic path to get the desired value. This function receives two parameters, one is the path and the other is the data-type.
SELECT
PR.ProductId
,PR.Reviews.value('(/Product/Reviews/Review/CustomerId)[1]','int') AS CustomerId
,PR.Reviews.value('(/Product/Reviews/Review/Desc)[1]','varchar(255)') AS Review
FROM ProductReviews PR
Here is the result:

use of value function
Another function is query where you can see in XML result what is inside of the current XML or in a specified path. This receives one parameter that is the xml hierarchic path, if you want to see the subsequent nodes from the root node you can write (‘/./’)
SELECT
PR.ProductId
,PR.Reviews.query('(/Product/Reviews/Review/CustomerId)') AS CustomerId
,PR.Reviews.query('/Product') AS Review
FROM ProductReviews PR
Here is the result:

use of XML query function in SQL
Use of exist function. It will evaluate a path (similar to query() and it will return true in case it exist. The format is the following:
SELECT
PR.ProductId
,PR.Reviews.query('(/Product/Reviews/Review/CustomerId)') AS CustomerId
,PR.Reviews.query('/Product') AS Review
FROM ProductReviews PR
WHERE PR.Reviews.exist('(/Product/Reviews/Review/CustomerId)[2]') = 1
Here is the result:

use of XML Exist function in SQL Server
What to do with multiple child nodes?
This is a good question, so far we have seen how to get unique values or one value per row, one of the reasons to store information as XML is because you can store multiple values related to one item as is the case of the row number three in this example where it contains for one product two reviews.
Using “CROSS APPLY“ operator and nodes function you can resolve this mess, this will allow you to create a “JOIN“ with every child node and get the information as an independent row. Let’s see one example.
Suppose you want to display all the reviews, but the reviews should be in a different rows even or not are related to the same product, the query should be something like this:
SELECT
PR.ProductId
,Review.value('CustomerId[1]','int') AS CustomerId
,Review.value('Desc[1]','varchar(255)') AS Review
FROM ProductReviews PR
CROSS APPLY Reviews.nodes('(/Product/Reviews/Review)') AS R(Review)
Here you go the result:

use of Cross Apply with nodes to iterate to all child nodes in XML SQL Server
Now you have learned how to take advantage of the XML columns and write some queries with this kind of column, based on that I hope that from now work with XML columns in SQL Server will be more fun than annoying.
Enjoy it!
Chris