ERROR: invalid XML content, when Select unnest(xpath(... on a text column I have a text column named big_xml in my table which has the following value:

&lt;?xml version=1.0 encoding=UTF-8?&gt;
&lt;dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation=http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd&gt;
 &lt;DpidOwner SequenceNumber=1&gt;
  &lt;DPID&gt;PADPIDA2006111001O&lt;/DPID&gt;
  &lt;CompanyName&gt;234AG&lt;/CompanyName&gt;
  &lt;Address&gt;Riedtlistrasse 23, Zürich, 8006, CH&lt;/Address&gt;
 &lt;/DpidOwner&gt;
 &lt;DpidOwner SequenceNumber=2&gt;
  &lt;DPID&gt;PADPIDA2007011501Q&lt;/DPID&gt;
  &lt;CompanyName&gt;OpenIMP&lt;/CompanyName&gt;
  &lt;Address&gt;8-10 Rhoda Street, London, E2 7EF, UK&lt;/Address&gt;
 &lt;/DpidOwner&gt;
 &lt;DpidOwner SequenceNumber=3&gt;
  &lt;DPID&gt;PADPIDA2007040501K&lt;/DPID&gt;
  &lt;CompanyName&gt;The Harry Fox Agency&lt;/CompanyName&gt;
  &lt;Address&gt;711 Third Avenue, 8th Floor, New York, 10017, USA&lt;/Address&gt;
 &lt;/DpidOwner&gt;
&lt;/dpid:DpidDatabase&gt;


Execute query:

SELECT 
  unnest((xpath('/dpid:DpidDatabase/DpidOwner',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS SequenceNumber   
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/DPID',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS DPID
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/CompanyName',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS CompanyName 
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/Address',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS Address
FROM table1_20190828


Error displayed:

ERROR: invalid XML content
 Detail: line 1: XML declaration allowed only at the start of the document
&lt;?xml version=1.0 encoding=UTF-8?&gt;
        ^
1 statement failed.
Basically double-quote all attribute values.

You can read about it in this tutorial.

I used an on-line xml validator and this is the result:


  The value following "version" in the XML declaration must be a quoted string.


Change:

&lt;?xml version=1.0 encoding=UTF-8?&gt;


by

&lt;?xml version="1.0" encoding="UTF-8"?&gt;



  Open quote is expected for attribute "xmlns:dpid" associated with an element type "dpid:DpidDatabase".


Change:

&lt;dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation=http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd&gt;


by

&lt;dpid:DpidDatabase xmlns:dpid="http://ddex.net/xml/dpid/11" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd"&gt;



  Open quote is expected for attribute "SequenceNumber" associated with an element type "DpidOwner".


Change all occurrences of:

&lt;DpidOwner SequenceNumber=XX&gt;


by double quoting SequenceNumber values

&lt;DpidOwner SequenceNumber="1"&gt;


You can check the result db&lt;>fiddle here



sequencenumber                                                                                                                                                                                                                                                     | dpid                            | companyname                                     | address                                                                                                                           
:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------ | :---------------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------
&lt;DpidOwner SequenceNumber=&quot;1&quot;&gt;&lt;br&gt;  &lt;DPID&gt;PADPIDA2006111001O&lt;/DPID&gt;&lt;br&gt;  &lt;CompanyName&gt;234AG&lt;/CompanyName&gt;&lt;br&gt;  &lt;Address&gt;Riedtlistrasse 23, ZÃƒ????????Ãƒ???????Ãƒ??????Ãƒ?????Ãƒ????Ãƒ???Ãƒ??Ãƒ?Ã‚Â¼rich, 8006, CH&lt;/Address&gt;&lt;br&gt; &lt;/DpidOwner&gt; | &lt;DPID&gt;PADPIDA2006111001O&lt;/DPID&gt; | &lt;CompanyName&gt;234AG&lt;/CompanyName&gt;                | &lt;Address&gt;Riedtlistrasse 23, ZÃƒ????????Ãƒ???????Ãƒ??????Ãƒ?????Ãƒ????Ãƒ???Ãƒ??Ãƒ?Ã‚Â¼rich, 8006, CH&lt;/Address&gt;
&lt;DpidOwner SequenceNumber=&quot;2&quot;&gt;&lt;br&gt;  &lt;DPID&gt;PADPIDA2007011501Q&lt;/DPID&gt;&lt;br&gt;  &lt;CompanyName&gt;OpenIMP&lt;/CompanyName&gt;&lt;br&gt;  &lt;Address&gt;8-10 Rhoda Street, London, E2 7EF, UK&lt;/Address&gt;&lt;br&gt; &lt;/DpidOwner&gt;                                                                         | &lt;DPID&gt;PADPIDA2007011501Q&lt;/DPID&gt; | &lt;CompanyName&gt;OpenIMP&lt;/CompanyName&gt;              | &lt;Address&gt;8-10 Rhoda Street, London, E2 7EF, UK&lt;/Address&gt;                                                                          
&lt;DpidOwner SequenceNumber=&quot;3&quot;&gt;&lt;br&gt;  &lt;DPID&gt;PADPIDA2007040501K&lt;/DPID&gt;&lt;br&gt;  &lt;CompanyName&gt;The Harry Fox Agency&lt;/CompanyName&gt;&lt;br&gt;  &lt;Address&gt;711 Third Avenue, 8th Floor, New York, 10017, USA&lt;/Address&gt;&lt;br&gt; &lt;/DpidOwner&gt;                                                | &lt;DPID&gt;PADPIDA2007040501K&lt;/DPID&gt; | &lt;CompanyName&gt;The Harry Fox Agency&lt;/CompanyName&gt; | &lt;Address&gt;711 Third Avenue, 8th Floor, New York, 10017, USA&lt;/Address&gt;