Querying XML Data in SQL Using XPath

Handle XML Data with XPath Queries

Write a SQL query to extract specific fields from an XML column using XPath expressions.

Solution:

-- Extract fields from an XML column.
SELECT 
    EmployeeID,
    Profile.value('(/Employee/Name)[1]', 'NVARCHAR(100)') AS Name,
    Profile.value('(/Employee/Age)[1]', 'INT') AS Age,
    Profile.query('/Employee/Skills') AS Skills
FROM Employees;

Explanation:

• Purpose of the Query :
• The goal is to extract specific fields (Name, Age, Skills) from an XML column (Profile) in the Employees table.
• Key Components :
• value() : Extracts scalar values using XPath expressions.
• query() : Extracts XML fragments or substructures.
• (/Employee/FieldName)[1] : Specifies the XPath path to the desired field.
• Why use XML Handling? :
• XML support allows flexible storage and querying of hierarchical or semi-structured data.
• Real-World Application :
• For example, in legacy systems, you might use this query to retrieve configuration or profile details stored in XML format.

Additional Notes:

• XML handling is supported in many relational databases (e.g., SQL Server, Oracle).
• Use this exercise to teach how to work with hierarchical data in relational databases.

For more Practice: Solve these Related Problems:

• Write a SQL query to extract the title of books from an XML column containing library catalog data.
• Write a SQL query to retrieve the author names from an XML structure representing book details.
• Write a SQL query to extract the ISBN number from an XML column storing publication metadata.
• Write a SQL query to parse and display all chapters of a book stored as XML fragments in a database.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Create a Non-Recursive Common Table Expression.
Next SQL Exercise: Combine PIVOT and UNPIVOT Operators.