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: 3    Average: 5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

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