Can I use ../@mp:localname?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Julie - 12 Nov 2008 22:11 GMT

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

Reply to this Message

Bob - 12 Nov 2008 23:12 GMT

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)

> Hi,
>
[quoted text clipped - 11 lines]
>
> Julie

Reply to this Message

Julie - 13 Nov 2008 17:57 GMT

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

> 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
[quoted text clipped - 27 lines]
> >
> > Julie

Reply to this Message

Bob - 13 Nov 2008 23:38 GMT

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)

> 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
[quoted text clipped - 72 lines]
> > >
> > > Julie

Reply to this Message

Julie - 20 Nov 2008 19:12 GMT

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.

> 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:
[quoted text clipped - 82 lines]
> > > >
> > > > Julie

Reply to this Message