xquery - Informatica : Query XML data in SQL Server through ODBC

Question

We were using Informatica mapping to extract some XML data(some elements form xml) using lookup.
Issue is SQL returns data in SQL Server client tool or in WinSQL tool. But same SQL in informatica session is failing with lookup query failed error.
Anyone worked in such scenario and can give me some idea?
Version details below -

• ODBC driver for MS SQL Server - Data direct - 8.x (comes with infa)
• Infa - 10.2
• SQL Server - not sure may be 2012/2016.

Error - Lookup LKP_SQL_SERV failed, please contact informatica support.
Here is a sample SQL-

SELECT CONVERT(VARCHAR(50),XML_PAYLOAD.query('declare namespace s="http://www.example.com/external/s/2012/1/1";declare namespace g="http://www.example.com/external/g/2012/1/1";data(/s:SmaxRoot/g:Group/g:Categories/g:Category[1]/@CategoryValue)')) AS CategoryValue1
FROM
table1

input XML

<ns:SRoot
    xmlns:ns="http://www.example.com/external/s/2012/1/1">
    <ns1:Group
        xmlns:ns1="http://www.example.com/external/g/2012/1/1">
        <ns1:Categories>
            <ns1:Category CategoryType="EVERYBODY" Required="true" CategoryValue="EVERYBODY" CategoryDisplayName="EVERYBODY" Active="true" DefaultValue="false"/>
            <ns1:Category CategoryType="Subgroups" Required="false" CategoryValue="Active" CategoryDisplayName="Active" Active="true" DefaultValue="false"/>
            <ns1:Category CategoryType="Subgroups" Required="false" CategoryValue="PYTHON" CategoryDisplayName="PYTHON" Active="true" DefaultValue="false"/>
        </ns1:Categories>
    </ns1:Group>
</ns:SRoot>

question from:https://stackoverflow.com/questions/65898532/informatica-query-xml-data-in-sql-server-through-odbc

Answer 1

Waitting for answers