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. |
From the docs:
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'); |