SQL Server 2012 - Using XQuery to Query XML Data

Using XQuery to query XML data in SQL Server 2012 involves leveraging the built-in XML data type and its associated methods. Here’s a step-by-step guide to help you get started.

Step 1: Creating an XML Data Type Column

First, let’s create a table that includes an XML column:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductInfo XML
);

Step 2: Inserting XML Data

You can insert XML data into the ProductInfo column like this:

INSERT INTO Products (ProductID, ProductInfo)
VALUES (1, '<product><name>Apple</name><price>1.20</price><category>Fruit</category></product>'),
       (2, '<product><name>Banana</name><price>0.50</price><category>Fruit</category></product>'),
       (3, '<product><name>Carrot</name><price>0.70</price><category>Vegetable</category></product>');

Step 3: Querying XML Data with XQuery

You can use XQuery to extract specific data from the XML column. Here are some common examples:

Example 1: Extracting Product Names

To retrieve all product names:

SELECT ProductInfo.value('(/product/name)[1]', 'VARCHAR(50)') AS ProductName
FROM Products;

Example 2: Extracting Prices

To get the prices of all products:

SELECT ProductInfo.value('(/product/price)[1]', 'DECIMAL(10,2)') AS Price
FROM Products;

Example 3: Filtering Products by Category

If you want to filter products by category, you can use the nodes() method to shred the XML:

SELECT 
    p.ProductID,
    prod.value('(name)[1]', 'VARCHAR(50)') AS ProductName,
    prod.value('(price)[1]', 'DECIMAL(10,2)') AS Price
FROM Products p
CROSS APPLY ProductInfo.nodes('/product') AS prod(prod);

Step 4: Advanced Querying

Example: Querying with Conditions

To find products in a specific category (e.g., "Fruit"), you can use the following query:

SELECT 
    p.ProductID,
    prod.value('(name)[1]', 'VARCHAR(50)') AS ProductName,
    prod.value('(price)[1]', 'DECIMAL(10,2)') AS Price
FROM Products p
CROSS APPLY ProductInfo.nodes('/product') AS prod(prod)
WHERE prod.value('(category)[1]', 'VARCHAR(50)') = 'Fruit';

Conclusion

Using XQuery within SQL Server 2012 to query XML data is powerful and flexible. You can extract, filter, and manipulate XML data stored in your database efficiently. Experiment with different queries to understand better how to leverage XML data types in SQL Server.