1

in a previous SO post: The argument 1 of the XML data type method "value" must be a string literal I failed to figure out a solution but if anyone can answer this question, I can solve that previous problem.

Question: how do I retrieve the full path of a given node, in the way that I can retrieve the name of a given node?

declare @x xml; set @x='<ROOT><a>111</a></ROOT>'
SELECT @x.value('local-name((/ROOT/a)[1])', 'varchar(256)')

the above will return 'a'. how do I return '/ROOT/a'?

Community
  • 1
  • 1
ekkis
  • 9,804
  • 13
  • 55
  • 105
  • I'm really confused as to what you want to achieve. In this case I don't think it's possible to do what you want. – Saulius Valatka Sep 14 '12 at 13:03
  • @Saulius, with this particular question, what I want to achieve is retrieving the path of a given node. if I can solve that, I'll be able to post a solution to the other question – ekkis Sep 14 '12 at 18:24

1 Answers1

0

there is no solution to this problem at present (as of SQL Server 2008) simply because the SQL engine does not support the entire XQuery language. there are functions, as per the spec, that could be used to get ancestors and using FLWOR the path could be constructed, but the ancestry functions have not been implemented. sadly.

however, as for the issue that got me here to begin with (see my original SO post) - that found an acceptable solution

ekkis
  • 9,804
  • 13
  • 55
  • 105