Using XQuery to Transform XML Data in SQL

Transform XML Data Using XQuery

Write a SQL query to extract and transform XML data into a tabular format using XQuery.

Solution:

-- Extract and transform XML data into a tabular format.
SELECT 
    T.c.value('(Name/text())[1]', 'NVARCHAR(100)') AS Name,
    T.c.value('(Age/text())[1]', 'INT') AS Age,
    T.c.value('(Department/text())[1]', 'NVARCHAR(100)') AS Department
FROM Employees
CROSS APPLY Profile.nodes('/Employee') AS T(c);

Explanation:

1. Purpose of the Query :

1. The goal is to extract and transform XML data stored in the Profile column into a tabular format.

2. Key Components :

1. nodes() : Shreds XML data into rows.
2. value() : Extracts scalar values from XML nodes.
3. /Employee : Specifies the root node of the XML structure.

3. Why use XQuery for XML? :

1. XQuery allows you to parse and transform hierarchical XML data into a relational format for easier querying and reporting.

4. Real-World Application :

1. For example, in legacy systems, you might use this query to extract and analyze data stored in XML format.

Additional Notes:

• Ensure that the XML structure is well-formed and consistent.
• Use this exercise to teach how to transform hierarchical data into a tabular format.

For more Practice: Solve these Related Problems:

• Write a SQL query to extract product names and prices from an XML column containing inventory data.
• Write a SQL query to transform XML-based customer feedback into a tabular format for analysis.
• Write a SQL query to extract all attributes from an XML node representing employee details.
• Write a SQL query to shred an XML document containing order details into rows and columns.

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

Previous SQL Exercise: Parse and Modify JSON Data.
Next SQL Exercise: Use PERCENT_RANK and CUME_DIST Functions.