How to get XML subtree @mp:xmltext when XML has a namespace declaration

[cigaras]

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.

[VolkerBarth]

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');