In SQL Server, XQuery is a powerful feature that allows users to query and extract data from XML documents. With XQuery, you can apply data filters or WHERE clauses on XML elements, insert, update, and delete XML nodes and node values in an XML column, and use XML data typed input parameters in stored procedures.

Let’s explore how to query XML data type using XQuery with some examples. We will use a sample table with XML data for better explanation.

Fetching Value from Target XML Node

To retrieve XML node values in a tabular format, we can use XQuery with the nodes() and value() functions. The nodes() function specifies the XPATH location of the XML node, and the value() function returns the XML node or attribute values based on the specified XPATH.

Here’s an example:

SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle,
       X.Y.value('(BankName)[1]', 'VARCHAR(20)') as BankName,
       X.Y.value('(AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue,
       X.Y.value('(BusinessType)[1]', 'VARCHAR(256)') as BusinessType,
       X.Y.value('(Specialty)[1]', 'VARCHAR(128)') as Specialty
FROM HumanResources.Employee e
OUTER APPLY e.emp_xml.nodes('EmployeeDetails/StoreDetail/Store') as X(Y)

In the above query, we are retrieving values from the XML nodes and displaying them in a tabular format. The XPATH specifies the location of the XML nodes, and the value() function retrieves the values from those nodes.

Applying Filters on XML Nodes

We can also apply filters on XML nodes using XQuery. For example, we can retrieve rows from a table where employees are using “International Bank” as the bank name.

SELECT *
FROM (
    SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle,
           X.Y.value('(BankName)[1]', 'VARCHAR(20)') as BankName,
           X.Y.value('(AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue,
           X.Y.value('(BusinessType)[1]', 'VARCHAR(256)') as BusinessType,
           X.Y.value('(Specialty)[1]', 'VARCHAR(128)') as Specialty
    FROM HumanResources.Employee e
    CROSS APPLY e.emp_xml.nodes('EmployeeDetails/StoreDetail/Store') as X(Y)
) T
WHERE BankName = 'International Bank'

In the above query, we are applying a filter on the XML node value in the WHERE clause. We use a subquery to retrieve all values from the XML node and then apply the filter on the result set.

Handling Namespaces in XQuery

If an XML document has namespaces, we can handle them in XQuery by specifying the namespace handle in the XPATH or using the * wildcard to ignore the namespace.

SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle,
       X.Y.value('(*:BankName)[1]', 'VARCHAR(20)') as BankName,
       X.Y.value('(*:AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue,
       X.Y.value('(*:BusinessType)[1]', 'VARCHAR(256)') as BusinessType,
       X.Y.value('(*:Specialty)[1]', 'VARCHAR(128)') as Specialty
FROM HumanResources.Employee e
OUTER APPLY e.emp_xml.nodes('/*:EmployeeDetails/*:StoreDetail/*:Store') as X(Y)
WHERE X.Y.value('(AnnualRevenue)[1]', 'BIGINT') > 100000

In the above query, we are using the *: notation to handle namespaces in the XPATH. This allows us to retrieve values from XML nodes even if they have namespaces.

Using EXIST() with XQuery

The exist() function in XQuery returns a BIT value (0 or 1) indicating whether a value exists or not based on the specified XPATH. We can use this function to check if a specific XML element value exists.

SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle
FROM HumanResources.Employee e
WHERE e.emp_xml.exist('(/*:EmployeeDetails/*:StoreDetail/*:Store/*:Specialty[.="Mountain"])') = 1

In the above query, we are using the exist() function to check if any rows exist where the Specialty XML node value is “Mountain”. This function improves query performance when dealing with larger XML data.

Combining XQuery Functions

We can combine multiple XQuery functions like exist(), nodes(), value(), and query() in a single SQL query to achieve better performance and implement complex business logic.

SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle,
       X.Y.value('(*:BankName)[1]', 'VARCHAR(30)') as BankName,
       X.Y.value('(*:AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue,
       X.Y.value('(*:BusinessType)[1]', 'VARCHAR(256)') as BusinessType,
       X.Y.value('(*:Specialty)[1]', 'VARCHAR(128)') as Specialty
FROM HumanResources.Employee e
CROSS APPLY e.emp_xml.nodes('/*:EmployeeDetails/*:StoreDetail/*:Store') as X(Y)
WHERE e.emp_xml.exist('(/*:EmployeeDetails/*:StoreDetail/*:Store/*:Specialty[.="Mountain"])') = 1

In the above query, we are combining multiple XQuery functions to retrieve values from XML nodes and apply a filter on the Specialty XML node value.

By using XQuery in SQL Server, you can efficiently query and manipulate XML data, extract specific information from XML nodes, and apply filters on XML elements. This powerful feature enhances the capabilities of SQL Server when working with XML data.