Hmm, not sure about that syntax. In SQL 2005 you can use XQuery to query
XML, although I'm not entirely sure what your expected results are:

SELECT
x.y.value('(cachedTermInformation/termName)[1]', 'VARCHAR(MAX)' ) AS Term,
x.y.value('@termId', 'BIGINT' ) AS TermId,
'???' AS NodeId,
x.y.value('local-name(.)', 'VARCHAR(MAX)' ) AS NodeType
FROM @xml.nodes('controlledVocabulary/category/validate dTerm') x(y)



"Julie" wrote:

> Thank you Bob. I can use ".", "/", etc. xpath expressions with my xml but I
> cannot use these expressions with @mp:localname or other syntax that retrieve
> meta data of the XML. An example could be:
>
> declare @xml XML, @idoc int
>
> set @xml='<controlledVocabulary>
> <category databaseId="AWB_CV" thesaurusId="6">
> <cachedCategoryInformation date="2007-08-06T16:03:49-04:00">
> <categoryName>Subject</categoryName>
> </cachedCategoryInformation>
> <validatedTerm termId="1667">
> <cachedTermInformation date="2008-10-31T11:48:13-04:00">
> <termName>Accounting standards</termName>
> <authority>Collins Dictionary of Business</authority>
> </cachedTermInformation>
> </validatedTerm>
> </category>
> </controlledVocabulary>'
>
> EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
> select * from openxml (@idoc, '//validatedTerm', 2)
> with (Term nvarchar(500) 'cachedTermInformation/termName',
> TermID bigint './@termId',
> NodeID nvarchar(100) '../../../@id',
> NodeType varchar(50) '@mp:localname')
>
> Works fine and I got:
> TermID NodeID Term NodeType
> 1667 NULL Accounting standards validatedTerm
>
> However, if I change the last line of the code to:
> NodeType varchar(50) '../../@mp:localname')
>
> I got error:
>
> Msg 6603, Level 16, State 2, Line 21
> XML parsing error: Reference to undeclared namespace prefix: 'mp'.
>
> The namespace "mp" is no longer recognized.
>
> Thanks for your help.
>
> Jie
> "Bob" wrote:
>
> > Could you post some sample XML and the SQL you are running? There's no
> > reason you can't do something like this with the local-name(.) function. Is
> > that what you mean?
> >
> > DECLARE @xml XML
> >
> > SET @xml = '<root>
> > <Person>Julie</Person>
> > <Person>wBob</Person>
> > </root>'
> >
> > SELECT
> > x.y.value('local-name(.)', 'VARCHAR(50)'),
> > x.y.value('.', 'VARCHAR(50)')
> > FROM @xml.nodes('//*') AS x(y)
> >
> > "Julie" wrote:
> >
> > > Hi,
> > >
> > > I need to get the name of the parent node and used "../@mp:localname" in the
> > > openxml statement. I got an error when trying to execute the query:
> > >
> > > XML parsing error: Reference to undeclared namespace prefix: 'mp'
> > >
> > > However, the query execute just fine if I use @mp:localname. It seems that I
> > > cannot use the xpath expression like "/" or "..". However, I do see some
> > > people using it on some online articles. I wonder whether there are any
> > > server settings that need to be changed. I am using SQL Server 2005.
> > >
> > > Thanks for any suggestions!
> > >
> > > Julie