Postgres XML parse nodename

Question

I have some XML like this:

<title>A Closer Look</title>
<pubDate>Thu, 12 Sep 2019 17:46:49 +0000</pubDate>
<language>en</language>
<copyright><![CDATA[]]></copyright>
<category:keywords>business,businessnews</category:keywords>

I am using this query to parse the XML:

SELECT (xpath('//channel/title/text()', x))[1]::varchar AS Title,
       (xpath('//channel/pubDate/text()', x))[1]::varchar AS PublishDate,
       (xpath('//channel/language/text()', x))[1]::varchar AS Language,
       (xpath('//channel/copyright/text()', x))[1]::varchar AS Copyright,
       (xpath('//channel/category:keywords']/text()', x))[1]::varchar AS CategoryKeywords
FROM unnest(xpath('//channel', pg_read_file('test.xml')::xml)) x;

However, I am getting this error doe to the colon:

ERROR:  could not create XPath object
DETAIL:  Undefined namespace prefix

How can I perform this query including the node name with the colon

Thank!

score 0 · Answer 1 · answered Aug 01 '20 at 00:37

0

You need a namespace declaration for the 'category'. Try putting this in the root element of your XML:

<root xmlns:category="tempuri.org">

-You haven't included your whole XML so I have called the element 'root' - but you dont' need to change that.

answered Aug 01 '20 at 00:37

Bryn Lewis

580
1
5
14

Is there no other way? I ask only because, we get this XML file in from an external client, so we must work with whatever they give us. – Landon Statis Aug 01 '20 at 01:05
Can you first test whether this fixes it, then work out how to implement it? – Bryn Lewis Aug 01 '20 at 02:54

Postgres XML parse nodename

1 Answers1