Select all elements regardless of level in XML


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


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions. I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have 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 *