Click here to Skip to main content
15,907,497 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I need to get the parent element names from the below sample xml. When i tried the below query, i got all the element name including all the child names too. But I need only the parent element name from the xml.

SQL
DECLARE @xml xml = 
'<root>
	<country>
		<id>1</id>
		<code>CY</code>
		<name>cyprus</name>
		<isocode>CYP</isocode>
	</country>
	<country>
		<id>110</id>
		<code>JP</code>
		<name>Japan</name>
		<isocode>JPN</isocode>
	</country>
	<country>
		<id>155029</id>
		<code>IN</code>
		<name>India</name>
		<isocode>IND</isocode>
	</country>
	<rank>
		<id>3</id>
		<code>C/O TRN</code>
		<rankname>CHIEF OFFICER TRAINEE</rankname>
	</rank>
	<rank>
		<id>105354</id>
		<code>MAS</code>
		<rankname>MASTER</rankname>
	</rank>
	<rank>
		<id>105355</id>
		<code>C/E</code>
		<rankname>CHIEF ENGINEER</rankname>
	</rank>
</root>'

SELECT Nodes.Name.query('local-name(.)') AS 'NAMES' FROM @xml.nodes('//*') As Nodes(Name)

Output for the above query is,
root
country
id
code
name
isocode
country
rank
id
code
rankname
rank
id
code
rankname
rank
id
code
rankname


Required Output:(Only the parent element name from the xml)
country
rank

What I have tried:

This is the query i have tried,
SELECT Nodes.Name.query('local-name(.)') AS 'NAMES' FROM @xml.nodes('//*') As Nodes(Name)
Posted
Updated 25-Apr-23 0:57am

1 solution

Filter out elements that don't have child elements:
SQL
FROM @xml.nodes('//*[*]')
Set selection predicates in location path (SQLXML) - SQL Server | Microsoft Learn[^]

Based on your output, you also want to filter out the root node:
SQL
FROM @xml.nodes('/*/*[*]')

You also only seem to want a distinct list, which means you'll need to switch from .query(...) to .value(...):
SQL
SELECT DISTINCT
    Nodes.Name.value('local-name(.)', 'nvarchar(max)') As NAMES
FROM
    @xml.nodes('/*/*[*]') As Nodes(Name)
;
value() Method (xml Data Type) - SQL Server | Microsoft Learn[^]
 
Share this answer
 
Comments
Rajesh waran 25-Apr-23 7:35am    
It works. Thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900