1

I have a text column named big_xml in my table which has the following value:

<?xml version=1.0 encoding=UTF-8?>
<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>
 <DpidOwner SequenceNumber=1>
  <DPID>PADPIDA2006111001O</DPID>
  <CompanyName>234AG</CompanyName>
  <Address>Riedtlistrasse 23, Zürich, 8006, CH</Address>
 </DpidOwner>
 <DpidOwner SequenceNumber=2>
  <DPID>PADPIDA2007011501Q</DPID>
  <CompanyName>OpenIMP</CompanyName>
  <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
 </DpidOwner>
 <DpidOwner SequenceNumber=3>
  <DPID>PADPIDA2007040501K</DPID>
  <CompanyName>The Harry Fox Agency</CompanyName>
  <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>
 </DpidOwner>
</dpid:DpidDatabase>

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
<?xml version=1.0 encoding=UTF-8?>
        ^
1 statement failed.
0

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:

<?xml version=1.0 encoding=UTF-8?>

by

<?xml version="1.0" encoding="UTF-8"?>

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

Change:

<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>

by

<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">

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

Change all occurrences of:

<DpidOwner SequenceNumber=XX>

by double quoting SequenceNumber values

<DpidOwner SequenceNumber="1">

You can check the result db<>fiddle here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.