CODEWITHSUNDEEP
sql-server-2008openxml
Kanwal Sarwara
Kanwal Sarwara

Reputation: 413

Getting ancestor tags name using openxml

I am trying to get ancestor tags name using open xml in sql server 2008. Here is what i have tried so far:

SELECT * FROM OPENXML(@idoc, '/Envelope/Body/*/TagParent/Tag')
WITH (
    [Name]    varchar(max) '../@mp:parentLocalName'
)

I need to get the name of the 3rd tag,*. I can successfully run parentlocalname and localname, but nothing works for parent's parent. I have tried many varients of above, it just errors out saying namespace mp is not defined. Is it even possible to fetch that name.

Upvotes: 0

Views: 502

Answers (1)

Bert
Bert

Reputation: 82489

This is a little hacky, but should work, where ../../. is the path to whichever parent you're looking for.

SELECT Name.value('local-name((*)[1])','nvarchar(100)') 
FROM OPENXML(@idoc, '/Envelope/Body/*/TagParent/Tag')
WITH (
    [Name]    xml '../../.'
)

Working example.

Upvotes: 1

Related Questions