Forum Discussion
Skip 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.
32 Replies
use try ... otherwise ... to provide an empty table interpretation of your xml's data in case of missing payload. It must be an empty table so it won't mess up the combine step later.
I'm fairly new to Power BI and I have no idea what that means. I don't know how "to provide an empty table interpretation of your xml's data." I'm talking about cases where there is no XML data at all (a blank or NULL column value). If I knew how to create some valid XML with no content I could do that in my SQL script, but no matter what I try I get the XML parse error.
Please provide examples of both XML scenarios.
The problem is that I don't know how to create that empty XML or table for the OTHERWISE clause.
That is a secondary problem. Your primary problem is to define the layout of the flattened table you want to receive if data is present. Once you haver that you can grab the column headers and create an empty table from these.
When I look across at the transformed/expanded columns, nothing after the ViolatorUnlicensedIndicator data point shows up. It's only picking up 119 out of 243 data points.
You mean the stuff after "Person"?
It sits in a different branch.
How do I get Power Query to parse a different branch? Thanks.
