CODEWITHSUNDEEP
sql-serverxmlxquery
Mike
Mike

Reputation: 109

xquery to return the element name

DECLARE @XML XML
SET @xml = '
<root>
    <a>
        <DATE_OF_BIRTH> 19871224</DATE_OF_BIRTH> 
        <DRIVER> MIKE </DRIVER> 
    </a>
    <a>
        <DATE_OF_BIRTH> 19881124</DATE_OF_BIRTH> 
        <DRIVER> TOM </DRIVER> 
    </a>
    <a>
        <DATE_OF_BIRTH> 19770601</DATE_OF_BIRTH> 
        <DRIVER> DAVID </DRIVER> 
    </a>
</root>'

select x.value('.','varchar(100)')
from @xml.nodes('//a') as T(X)

I want to use xquery to return the element name

The result like this

DATE_OF_BIRTH

DRIVER

How can I make this happen?

Thank you very much.

Upvotes: 2

Views: 1386

Answers (2)

Bensonius
Bensonius

Reputation: 1541

If you try the following:

select X.query('local-name(.)')
from @xml.nodes('//a/*') as T(X)

it will return:

DATE_OF_BIRTH
DRIVER
DATE_OF_BIRTH
DRIVER
DATE_OF_BIRTH
DRIVER

Upvotes: 4

dirkk
dirkk

Reputation: 6218

Use local-name() (or name() if you want to include the namespace), so it should be

//a/*/local-name()

Upvotes: 3

Related Questions