A sample query from SQL Anywhere documentation with added @mp:xmltext:

SELECT * FROM OPENXML( '<Employee  xmlns="http://www.sap.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/prefix:Employee', 1, '<r xmlns:prefix="http://www.sap.com/EmployeeDemo"/>' )
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone',
       xmltext      XML      '@mp:xmltext');

Error message:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
XPath parser error: use of undeclared namespace prefix
SQLCODE=-892, ODBC 3 State="HY000"
Line 1, column 1

Have no idea how to declare namespace prefix, please help.

asked 14 Dec '21, 04:30

Valdas's gravatar image

Valdas
381141829
accept rate: 83%


From the docs:

Metaproperties can only be specified in WITH clause xpath arguments. A metaproperty is accessed within an XPath query as if it was an attribute. If a namespaces is not specified, then by default the prefix mp is bound to the Uniform Resource Identifier (URI) urn:sap-com:sa-xpath-metaprop (*). If a namespaces is specified, this URI must be bound to mp or some other prefix to access metaproperties in the query.

So you either have to add the mentioned mp namespace to the namespace declaration in the OpenXML clause - or just omit any namespace declaration and use the "*" wildcard to allow any namespace in your XPath query, such as

SELECT * FROM OPENXML( '<Employee  xmlns="http://www.sap.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/*:Employee', 1 )
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone',
       xmltext      XML      '@mp:xmltext');

(*): In my tests with v16 and v17.0.11, the mentioned URI does not work whereas the older URI urn:ianywhere-com:sa-xpath-metaprop returns the expected xmltext value - I reported that to the doc team:

SELECT * FROM OPENXML( '<Employee  xmlns="http://www.sap.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/prefix:Employee', 1, '<r xmlns:prefix="http://www.sap.com/EmployeeDemo" xmlns:mp="urn:ianywhere-com:sa-xpath-metaprop"/>')
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone',
       xmltext      XML      '@mp:xmltext');
permanent link

answered 15 Dec '21, 04:35

Volker%20Barth's gravatar image

Volker Barth
39.1k353534804
accept rate: 34%

edited 15 Dec '21, 04:50

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×20

question asked: 14 Dec '21, 04:30

question was seen: 404 times

last updated: 15 Dec '21, 04:50