Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
260 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
Waitting for answers

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...