By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,819 Members | 1,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,819 IT Pros & Developers. It's quick & easy.

Invalid XML content error when Select unnest(xpath... is used

P: 1
I have a text column named bid_xml in my table which has the following value:
Expand|Select|Wrap|Line Numbers
  1. <?xml version=1.0 encoding=UTF-8?>
  2. <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>
  3.  <DpidOwner SequenceNumber=1>
  4.   <DPID>PADPIDA2006111001O</DPID>
  5.   <CompanyName>234AG</CompanyName>
  6.   <Address>Riedtlistrasse 23, Zürich, 8006, CH</Address>
  7.  </DpidOwner>
  8.  <DpidOwner SequenceNumber=2>
  9.   <DPID>PADPIDA2007011501Q</DPID>
  10.   <CompanyName>OpenIMP</CompanyName>
  11.   <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
  12.  </DpidOwner>
  13.  <DpidOwner SequenceNumber=3>
  14.   <DPID>PADPIDA2007040501K</DPID>
  15.   <CompanyName>The Harry Fox Agency</CompanyName>
  16.   <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>
  17.  </DpidOwner>
  18. </dpid:DpidDatabase>
Execute query:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   unnest((xpath('/dpid:DpidDatabase/DpidOwner',  int_config.ddex_dpid_xml_20190828.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS SequenceNumber   
  3.   ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/DPID',  int_config.ddex_dpid_xml_20190828.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS DPID
  4.   ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/CompanyName',  int_config.ddex_dpid_xml_20190828.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS CompanyName 
  5.   ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/Address',  int_config.ddex_dpid_xml_20190828.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS Address
  6. FROM int_config.ddex_dpid_xml_20190828
Error displayed:
Expand|Select|Wrap|Line Numbers
  1. ERROR: invalid XML content
  2.   Detail: line 1: XML declaration allowed only at the start of the document
  3. <?xml version=1.0 encoding=UTF-8?>
  4.         ^
  5. 1 statement failed.
2 Weeks Ago #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.