SELECT tagnames (not attributes) from xml code

Question

SELECT tagnames (not attributes) from xml code

jaya

SSCrazy

Points: 2109
More actions
November 7, 2011 at 1:06 pm

#251197

I have the following 2 xml codes and I want to do a search on tag name
DECLARE @X xml
SET @X = '<root>
<resource-list>
<Item Name="CODE"/>
</resource-list>
</root>'
How can I write a query that give me 'Item' as a result.
2nd instance @X1=
'<root>
<resource-list>
<Specimen Name="CODE"/>
</resource-list>
</root>'
In the above xml I need to get Specimen as a result.
I have to write a query such that
If(tagname = 'Item')
(
do this
)
else if (tagname = 'specimen')
(
do this
)
Iam able to do a search on attribute but how can I get tag name???

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

If you only have two tags names, do you really need to get the actual tag name or will just knowing it's in there be enough? If the latter is true, have a look at the PATINDEX function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 2

Use the local-name function:

DECLARE @XML XML =

N'<root>

<resource-list>

<Specimen Name="CODE"/>

</resource-list>

</root>'

SELECT

@XML.value

(

'fn:local-name((/root/resource-list/node())[1])',

'VARCHAR(50)'

)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi