Forum Discussion
johnjbolduc
Helper II
1 year agoSkip parsing blank XML
I have several XML columns in a table, but they can sometimes have no content. I either need to be able to:
1. Transform the XML column, but have Power Query be smart enough to skip blanks or at least not error out on a blank value.
OR
2. Be able to create a blank or dummy XML string that can be successfully parsed by Power. I've tried many variations such as:
- '<metadata></metadata>'
- '<metadata><e /></metadata>'
- '<?xml version="1.0" encoding="UTF-8"?>'
- '<?xml version="1.0" encoding="UTF-8"?><metadata></metadata>'
- '<?xml version="1.0" encoding="UTF-8"?><metadata><e /></metadata>'
- '<?xml version="1.0" encoding="UTF-8"?><dcjis:DataExchange xmlns:dcjis="http://www.xfact.com/schemas/eopss/dcjis/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dcjis-ext="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:dot-ec="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:j="http://niem.gov/niem/domains/jxdm/4.0"><dcjis:EventTransaction></dcjis:EventTransaction></dcjis:DataExchange>'
The last variation is to try to match the XML cells with data.
All variations pass mutiple XML validators as valid XML, but all throw this same error when parsed in Power Query:
DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: Root element is missing.)
How am I missing a root element? How do I fix this? Thanks.