SyntaxHighlighter

Tuesday, June 19, 2012

T-SQL: Get XML element names

The following little code snippet retrieves the XML element names of the nodes at the respective levels. The first query returns the root node name, and the second the root child node name.

DECLARE @xml XML,
        @rawXml VARCHAR(MAX)
SET @rawXml = '<parent><child /><parent>'
SET @xml = CONVERT(XML, @rawXml)

-- Get the root element name
SELECT DISTINCT c.value('local-name(.)', 'nvarchar(50)') RootName
FROM @xml.nodes('/*') AS r(c)

-- Get the root child element name
SELECT DISTINCT c.value('local-name(.)', 'nvarchar(50)') RootChild
FROM @xml.nodes('/*/*') AS r(c)