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:

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.