Select all elements regardless of level in XML 2


We have already published a post on “Read XML Data as a table in SQL Server” and “Read and compare XML nodes dynamically with unknown elements“. In this post, we will see that how we can skip levels in XML string to read all the elements at a given level regardless of the level name. A wildcard character * (asterisk) can be used to extract all the elements of a XML string regardless of the level name. Lets have a demo to understand this.

Suppose, we have below XML data:

<Department>
<HR>
<EmpDetail>
<EmpName>Employee 1</EmpName>
<EmpId>1</EmpId>
<JoiningDate>20-Jan-2015</JoiningDate>
</EmpDetail>
<EmpDetail>
<EmpName>Employee 2</EmpName>
<EmpId>2</EmpId>
<JoiningDate>15-Jul-2015</JoiningDate>
</EmpDetail>
<EmpDetail>
<EmpName>Employee 3</EmpName>
<EmpId>3</EmpId>
<JoiningDate>18-Mar-2015</JoiningDate>
</EmpDetail>
</HR>
<Admin>
<EmpDetail>
<EmpName>Employee 4</EmpName>
<EmpId>4</EmpId>
<JoiningDate>22-Jan-2015</JoiningDate>
</EmpDetail>
</Admin>
<Engineering>
<EmpDetail>
<EmpName>Employee 5</EmpName>
<EmpId>5</EmpId>
<JoiningDate>26-Feb-2015</JoiningDate>
</EmpDetail>
</Engineering>
</Department>

In this data, the root level node is <Department> which has <HR>, <Admin>, and <Engineering> as their children nodes. Below image displays the above XML data separated by department names to make it more clear:

Select all elements regardless of level in XML - Sample data

Select all elements regardless of level in XML – Sample data

Now, if we want to read the name of all the employees regardless of their parent department names, we can use asterisk (*) as a wildcard character to skip the level names for the department names which are <HR>, <Admin>, and <Engineering>. Below is the query which can be used to read all the employee names regardless of their department names:


SELECT
tbl.col.value('EmpName[1]', 'VARCHAR(100)') AS EmpName
FROM @XMLString.nodes('Department/*/EmpDetail') AS tbl(col)

In the above query, we can see that in the given expression “Department/*/EmpDetail“, we have used * (asterisk) to skip the name of the second level elements which are <HR>, <Admin>, and <Engineering>. We can use the asterisk (*) to skip the name of the elements at any level.

In “Department/*/EmpDetail” expression, we are reading the EmpName using value function (tbl.col.value(‘EmpName[1]’, ‘VARCHAR(100)’)) of all the employees that are grandchildren of the <Department> element.

Thank you for the reading. Please share, rate and comment on this post.

Rate This
[Total: 4    Average: 5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Select all elements regardless of level in XML