Can I use ../@mp:localname?

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
 >> Stay informed about: Can I use ../@mp:localname? 

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/validatedTerm') 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
 >> Stay informed about: Can I use ../@mp:localname? 

Thank you Bob. I basically want to get the metadata of the xml nodes, i.e. I
want to know what is the name of a particular node. I'll try XQuery and see
whether it is helpful.

"Bob" wrote:

> 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/validatedTerm') 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
 >> Stay informed about: Can I use ../@mp:localname?