CODEWITHSUNDEEP
xmlpostgresqlparsingnodename
Landon Statis
Landon Statis

Reputation: 841

Postgres XML parse nodename

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!

Upvotes: 0

Views: 274

Answers (1)

Bryn Lewis
Bryn Lewis

Reputation: 598

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.

Upvotes: 0

Related Questions