Getting data from xml datasource which having xmlns defination.

[akshy_harale]

Hi everyone,

I am trying to read the xml datasource in teiid 8.1.0 Alph2

My xml file as bellow

<employees bldg="10"

     xmlns="http://www.w3.org/TR/REC-xml-names/">

     <employee id="901">

          <name>

               <first>John</first>

               <last>Doe</last>

          </name>

          <office>344</office>

          <salary currency="USD">55000</salary>                     

                      <department>1</department>

     </employee>

     <employee id="902">

          <name>

               <first>Peter</first>

               <last>Pan</last>

          </name>

          <office>216</office>

          <phone>905-416-5004</phone>                     

                        <department>2</department>

     </employee>

</employees>

but it fails. If I remove this    xmlns="http://www.w3.org/TR/REC-xml-names/"   from file then it works fine.

Is there any way to read the xml file which having the xmlns defination in it.

[rareddy]

Akshay,

Try

select * from xmltable(XMLNAMESPACES (DEFAULT 'http://www.w3.org/TR/REC-xml-names/'),  '/employees/employee' PASSING xmlparse(document '<employees bldg="10" xmlns="http://www.w3.org/TR/REC-xml-names/"><employee id="901"><name><first>John</first><last>Doe</last></name><office>344</office><salary currency="USD">55000</salary><department>1</department></employee><employee id="902"><name><first>Peter</first><last>Pan</last></name><office>216</office><phone>905-416-5004</phone><department>2</department></employee></employees>') 
COLUMNS name string PATH 'name/first', lastname string PATH 'name/last') x

You can also define the XML namespaces with URI too see https://docs.jboss.org/author/display/TEIID/FROM+Clause#FROMClause-XMLTABLE

Ramesh..

[akshy_harale]

Ramesh,

Thanks