Postgres use xpath_table parsing with xmlnamespaces

Question

Can I use xpath_table parsing with xmlnamespaces

drop table if exists _xml;
create temporary table _xml  (fbf_xml_id serial,str_Xml xml);

insert into _xml(str_Xml)
select  '<DataSet1  xmlns="http://tempuri.org/DataSet_LocalMaMC.xsd">
  <Stations>
    <ID>5</ID>
  </Stations>
  <Stations>
    <ID>1</ID>
  </Stations>
  <Stations>
    <ID>2</ID>
  </Stations>
  <Stations>
    <ID>10</ID>
  </Stations>
  <Stations>
    <ID/>
  </Stations>
  </DataSet1>' ;


drop table if exists _y;
create temporary table _y as
SELECT * 
FROM xpath_table('FBF_xml_id','str_Xml','_xml',
              '/DataSet1/Stations/ID',
              'true') AS t(FBF_xml_id int,ID text);
select * from _y

If I take of the xmlnamespaces it works fine. I thought to work with Xpath, but when there is null, it gives me wrong results.

user330315 · Accepted Answer · 2019-12-09 10:17:18Z

3

With Postgres 10 or later, xmltable() is the preferred way to do this.

You can easily specify a list of namespaces with that.

SELECT fbf_xml_id, xt.id
FROM _xml
  cross join 
      xmltable(xmlnamespaces ('http://tempuri.org/DataSet_LocalMaMC.xsd' as x),
               '/x:DataSet1/x:Stations'
               passing str_xml
               columns 
                  id text path 'x:ID') as xt

Note that in the XPath expression used for the xmltable() function, the tags are prefixed with the namespace alias defined in the xmlnamespaces option even though they are not prefixed in the input XML.

Online example

answered Dec 9, 2019 at 10:17

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

O.Z. Over a year ago

Sorry, Works Great!

Collectives™ on Stack Overflow

Postgres use xpath_table parsing with xmlnamespaces

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related