Skip parsing blank XML

[johnjbolduc]

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.

[lbendlin]

What's the "make and model"  of your SQL Server?  Do you know the db admin?

[johnjbolduc]

And SSMS v20.

[lbendlin]

SQL Server 2012 ?  I'm impressed that this thing is still alive.  See if you can negotiate an update to something a little more contemporary - newer version have much better XML support.

[johnjbolduc]

I'm just a contractor for Commonwealth of Massachusetts; there's no way I can make that happen.

[lbendlin]

I figured as much.  Make sure to manage your client's expectations. 

Greetings from around the block.

[johnjbolduc]

Thanks so much!

[Anonymous]

Hi @johnjbolduc 

Did the solution lbendlin  offered help you solve the problem, if it helps you can consider to acceot it as a solutio so that more user can refer to.

Best Regards!

Yolo Zhu

[johnjbolduc]

Unfortunately, no. My XML is much too complicated for anything offered here.

[johnjbolduc]

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.

[lbendlin]

Please provide examples of both XML scenarios.

[johnjbolduc]

This a sample of the full XML column. The problem with this is that Power Query doesn't parse anything beyond ViolatorUnlicensedIndicator (all of the ContactInfo, Vehicle, CitationOffense1 through CitationOffense4 and Court data are missing after parsing and expanding everything).

<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" xmlns:s="http://niem.gov/niem/structures/2.0"><dcjis:EventTransaction><dcjis:EventHeader><dcjis:UUID>MSPPS_eieio</dcjis:UUID><dcjis:CreateDateTime>2021-10-04T07:41:16</dcjis:CreateDateTime><dcjis:TransactionSource>CJISWeb3MSP</dcjis:TransactionSource><dcjis:MessageIdentifier>1633347753678-3232352e313238-mspps@b7733c2e-1462-4ccb-b0e5-e311699f2894</dcjis:MessageIdentifier><dcjis:TransactionMessageKey>MSPPS</dcjis:TransactionMessageKey><dcjis:UserID>MSP2876</dcjis:UserID><dcjis:OrganizationORIID><dcjis:ID>MAMSP0077</dcjis:ID></dcjis:OrganizationORIID><dcjis:DeviceAddress>170.154.225.92</dcjis:DeviceAddress><dcjis:UserContexts /><dcjis:SchemaVersion>1.0</dcjis:SchemaVersion><dcjis:ApplicationVersion>2.0</dcjis:ApplicationVersion><dcjis:ResponseMechanism>NONE</dcjis:ResponseMechanism><dcjis:CJISUserID><dcjis:ID>MSP2876</dcjis:ID></dcjis:CJISUserID><dcjis:CJISUserPassword>DCJIS</dcjis:CJISUserPassword><dcjis:CJISQueryAuthorizer><dcjis:PersonFullName>KEENEY, ROBERT J</dcjis:PersonFullName></dcjis:CJISQueryAuthorizer><dcjis:CJISQueryOperator><dcjis:PersonFullName>KEENEY, ROBERT J</dcjis:PersonFullName></dcjis:CJISQueryOperator><dcjis:CJISQueryRequester><dcjis:PersonFullName>KEENEY, ROBERT J</dcjis:PersonFullName></dcjis:CJISQueryRequester><dcjis:CJISQueryPurposeCode>C</dcjis:CJISQueryPurposeCode></dcjis:EventHeader><dcjis:EventBody><dcjis-ext:BodyExtension><dcjis-ext:ContentTypeCode>PAYSTATION</dcjis-ext:ContentTypeCode><dcjis-ext:ContentTypeText>MSP Paystation</dcjis-ext:ContentTypeText><dcjis-ext:DataElements><dcjis-ext:DataElement dcjis-ext:elementName="citationNo">142011AB</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="citationDate">2021-09-21</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="resultOfStopCode">C</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="resultOfStopText">Criminal</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="violatorTypeCode">1</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="violatorTypeText">Operator</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="citationVoidedInd">false</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="plateReaderInd">false</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="licenseStateCode">MA</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="licenseStateText">Massachusetts, USA</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="birthYear">1991</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="sexCode">M</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="sexText">Male</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="raceCode">B</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="raceText">BLACK</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateStateCode">MA</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateStateText">Massachusetts, USA</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateTypeCode">PAN</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateTypeText">PASSENGER NORMAL</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="locationCityCode">226</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="locationCityText">Harvard</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="locationRoadwayTypeCode">2</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="locationRoadwayTypeText">State/Numbered Routes</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="locationRouteNumberCode">2</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="locationRouteNumberText">2</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="searchInd">false</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="issuingAgencyCode">C4</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="issuingAgencyText">State Police Troop C-4</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerFirstName">RANDY</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerLastName">MORALES</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerIdNo">4182</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="offenseSpeedingInd">true</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="activityCode">P</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="activityText">Patrol</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="verbalWarningInd">false</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="organizationInd">false</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="electronicCitationInd">true</dcjis-ext:DataElement><dcjis-ext:DataElement dcjis-ext:elementName="transactionType">ADD</dcjis-ext:DataElement></dcjis-ext:DataElements></dcjis-ext:BodyExtension><dot-ec:ECitation><dot-ec:Citation s:id="citation1"><nc:ActivityIdentification><nc:IdentificationID>142011AB</nc:IdentificationID></nc:ActivityIdentification><nc:ActivityDate><nc:Date>2021-09-21</nc:Date></nc:ActivityDate><j:CitationAgency><nc:OrganizationIdentification><nc:IdentificationID>C4</nc:IdentificationID></nc:OrganizationIdentification><nc:OrganizationName>State Police Troop C-4</nc:OrganizationName></j:CitationAgency><j:CitationIssuingOfficial><nc:RoleOfPersonReference s:ref="person1" /><j:EnforcementOfficialBadgeIdentification><nc:IdentificationID>4182</nc:IdentificationID></j:EnforcementOfficialBadgeIdentification></j:CitationIssuingOfficial><dot-ec:CitationSubject><nc:RoleOfPersonReference s:ref="person2" /><dot-ec:SubjectArrestIndicator>false</dot-ec:SubjectArrestIndicator></dot-ec:CitationSubject><dot-ec:CitationViolation s:id="violation1"><nc:IncidentEvent><nc:ActivityDate><nc:DateTime>2021-09-21T19:55:00</nc:DateTime></nc:ActivityDate></nc:IncidentEvent><dot-ec:IncidentLocation><nc:LocationAddress><dot-ec:StructuredAddress><nc:LocationCityName>Harvard</nc:LocationCityName><dot-ec:LocationStateANSID20Code>MA</dot-ec:LocationStateANSID20Code><nc:LocationStateName>Massachusetts, USA</nc:LocationStateName><dot-ec:LocationCityCode>226</dot-ec:LocationCityCode></dot-ec:StructuredAddress></nc:LocationAddress><nc:LocationHighway><nc:HighwayFullText>RT. 2 EAST MM 108.6</nc:HighwayFullText></nc:LocationHighway></dot-ec:IncidentLocation></dot-ec:CitationViolation><dot-ec:ViolatorCitationTypeCode>1</dot-ec:ViolatorCitationTypeCode><dot-ec:ViolatorCitationTypeText>Operator</dot-ec:ViolatorCitationTypeText><dot-ec:VehicleSearchNonInventoryIndicator>false</dot-ec:VehicleSearchNonInventoryIndicator><dot-ec:CitationTypeCode>C</dot-ec:CitationTypeCode><dot-ec:CitationTypeText>Criminal</dot-ec:CitationTypeText><dot-ec:AcknowledgementTypeCode>VIOL</dot-ec:AcknowledgementTypeCode><dot-ec:AcknowledgementTypeText>Violator</dot-ec:AcknowledgementTypeText><dot-ec:OfficerSignatureIndicator>true</dot-ec:OfficerSignatureIndicator><dot-ec:OfficerSignatureModifiedIndicator>true</dot-ec:OfficerSignatureModifiedIndicator><dot-ec:OfficerSignatureText>4182</dot-ec:OfficerSignatureText><dot-ec:CertificationTypeCode>M</dot-ec:CertificationTypeCode><dot-ec:CertificationTypeText>Mailed to violator</dot-ec:CertificationTypeText><dot-ec:ViolatorOrganizationIndicator>false</dot-ec:ViolatorOrganizationIndicator><dot-ec:TrafficCrashInvolvedIndicator>true</dot-ec:TrafficCrashInvolvedIndicator><dot-ec:IssuingAgencyUniqueCitationID><nc:IdentificationID>1142932</nc:IdentificationID></dot-ec:IssuingAgencyUniqueCitationID><dot-ec:CrashReportID><nc:IdentificationID>2021-0C4-003213</nc:IdentificationID></dot-ec:CrashReportID><dot-ec:CitationPaperOriginationIndicator>false</dot-ec:CitationPaperOriginationIndicator><dot-ec:MACCSCitationID><nc:IdentificationID>CIT21E0265726</nc:IdentificationID></dot-ec:MACCSCitationID></dot-ec:Citation><dot-ec:Person s:id="person1"><nc:PersonName><nc:PersonGivenName>RANDY</nc:PersonGivenName><nc:PersonSurName>MORALES</nc:PersonSurName></nc:PersonName></dot-ec:Person><dot-ec:Person s:id="person2"><nc:PersonBirthDate><nc:Date>1991-07-18</nc:Date></nc:PersonBirthDate><nc:PersonName><nc:PersonGivenName>DAVID</nc:PersonGivenName><nc:PersonMiddleName>R</nc:PersonMiddleName><nc:PersonSurName>WALSH-SIRIANNI</nc:PersonSurName></nc:PersonName><dot-ec:PersonRaceCode>B</dot-ec:PersonRaceCode><nc:PersonRaceText>BLACK</nc:PersonRaceText><dot-ec:PersonSexCode>M</dot-ec:PersonSexCode><nc:PersonSexText>Male</nc:PersonSexText><dot-ec:PersonAugmentation><dot-ec:DriverLicense><dot-ec:DriverLicenseIdentification><nc:IdentificationID>S77996700</nc:IdentificationID><nc:IdentificationExpirationDate><nc:Date>2025-07-18</nc:Date></nc:IdentificationExpirationDate><j:DrivingJurisdictionAuthorityANSID20Code>MA</j:DrivingJurisdictionAuthorityANSID20Code><nc:IdentificationJurisdictionText>Massachusetts, USA</nc:IdentificationJurisdictionText><nc:IdentificationJurisdictionFIPS10-4Code>US</nc:IdentificationJurisdictionFIPS10-4Code><dot-ec:IdentificationJurisdictionFIPS10-4Text>UNITED STATES</dot-ec:IdentificationJurisdictionFIPS10-4Text><dot-ec:IdentificationClassCode>D</dot-ec:IdentificationClassCode><dot-ec:IdentificationClassText>Class D</dot-ec:IdentificationClassText></dot-ec:DriverLicenseIdentification><dot-ec:DriverLicenseCommercialIndicator>false</dot-ec:DriverLicenseCommercialIndicator></dot-ec:DriverLicense></dot-ec:PersonAugmentation><dot-ec:ViolatorUnlicensedIndicator>false</dot-ec:ViolatorUnlicensedIndicator></dot-ec:Person><nc:ContactInformation s:id="contactInformation1"><nc:ContactMailingAddress><dot-ec:StructuredAddress><dot-ec:LocationStreet><dot-ec:Street1FullText>123 SESAME ST</dot-ec:Street1FullText></dot-ec:LocationStreet><nc:LocationCityName>SOMERVILLE</nc:LocationCityName><dot-ec:LocationStateANSID20Code>MA</dot-ec:LocationStateANSID20Code><nc:LocationStateName>Massachusetts, USA</nc:LocationStateName><nc:LocationCountryFIPS10-4Code>US</nc:LocationCountryFIPS10-4Code><nc:LocationCountryName>UNITED STATES</nc:LocationCountryName><nc:LocationPostalCode>02143</nc:LocationPostalCode><nc:LocationPostalExtensionCode>3515</nc:LocationPostalExtensionCode></dot-ec:StructuredAddress></nc:ContactMailingAddress></nc:ContactInformation><nc:PersonContactInformationAssociation><nc:PersonReference s:ref="person2" /><nc:ContactInformationReference s:ref="contactInformation1" /></nc:PersonContactInformationAssociation><dot-ec:Vehicle s:id="vehicle1"><nc:VehicleColorPrimaryCode>WHI</nc:VehicleColorPrimaryCode><nc:ItemMakeName>MERZ</nc:ItemMakeName><nc:ItemModelName>GLC</nc:ItemModelName><nc:ItemModelYearDate>2018</nc:ItemModelYearDate><nc:VehicleCMVIndicator>false</nc:VehicleCMVIndicator><nc:ConveyanceRegistration><nc:ConveyanceRegistrationPlateIdentification><nc:IdentificationID>9GH541</nc:IdentificationID><nc:IdentificationExpirationDate><nc:Date>2022-01-31</nc:Date></nc:IdentificationExpirationDate><j:DrivingJurisdictionAuthorityANSID20Code>MA</j:DrivingJurisdictionAuthorityANSID20Code><nc:IdentificationJurisdictionFIPS10-4Code>US</nc:IdentificationJurisdictionFIPS10-4Code></nc:ConveyanceRegistrationPlateIdentification><dot-ec:VehiclePlateTypeCode>PAN</dot-ec:VehiclePlateTypeCode></nc:ConveyanceRegistration><dot-ec:VehiclePassenger16PlusIndicator>false</dot-ec:VehiclePassenger16PlusIndicator><dot-ec:HazMatPlacardDisplayedIndicator>false</dot-ec:HazMatPlacardDisplayedIndicator><dot-ec:VehicleRegistrationUnknownIndicator>false</dot-ec:VehicleRegistrationUnknownIndicator><nc:InsuranceCarrierName>THE TRAVELERS INDEMNITY COMPANY</nc:InsuranceCarrierName></dot-ec:Vehicle><dot-ec:Offense s:id="CitationOffense1"><dot-ec:OffenseCategoryCode>C</dot-ec:OffenseCategoryCode><dot-ec:OffenseCategoryText>Criminal</dot-ec:OffenseCategoryText><dot-ec:OffenseSpeedingTypeCode>NONE</dot-ec:OffenseSpeedingTypeCode><dot-ec:OffenseViolatedStatute><j:StatuteCodeIdentification><nc:IdentificationID>90</nc:IdentificationID></j:StatuteCodeIdentification><j:StatuteCodeSectionIdentification><nc:IdentificationID>10</nc:IdentificationID></j:StatuteCodeSectionIdentification><dot-ec:StatuteCodeSubSectionIdentification><nc:IdentificationID>A</nc:IdentificationID></dot-ec:StatuteCodeSubSectionIdentification><dot-ec:StatuteCodeFullIdentificationCode>90/10/A</dot-ec:StatuteCodeFullIdentificationCode><dot-ec:StatuteCodeFullIdentificationText>UNLICENSED OPERATION OF MV c90 S10</dot-ec:StatuteCodeFullIdentificationText></dot-ec:OffenseViolatedStatute><dot-ec:OffenseWarningIndicator>false</dot-ec:OffenseWarningIndicator><dot-ec:OffenseViolatorTypeCode>1</dot-ec:OffenseViolatorTypeCode><dot-ec:OffensePayableIndicator>false</dot-ec:OffensePayableIndicator></dot-ec:Offense><dot-ec:Offense s:id="CitationOffense2"><dot-ec:OffenseCategoryCode>C</dot-ec:OffenseCategoryCode><dot-ec:OffenseCategoryText>Criminal</dot-ec:OffenseCategoryText><dot-ec:OffenseSpeedingTypeCode>NONE</dot-ec:OffenseSpeedingTypeCode><dot-ec:OffenseViolatedStatute><j:StatuteCodeIdentification><nc:IdentificationID>90</nc:IdentificationID></j:StatuteCodeIdentification><j:StatuteCodeSectionIdentification><nc:IdentificationID>24</nc:IdentificationID></j:StatuteCodeSectionIdentification><dot-ec:StatuteCodeSubSectionIdentification><nc:IdentificationID>E</nc:IdentificationID></dot-ec:StatuteCodeSubSectionIdentification><dot-ec:StatuteCodeFullIdentificationCode>90/24/E</dot-ec:StatuteCodeFullIdentificationCode><dot-ec:StatuteCodeFullIdentificationText>NEGLIGENT OPERATION OF MOTOR VEHICLE c90 S24(2)(a)</dot-ec:StatuteCodeFullIdentificationText></dot-ec:OffenseViolatedStatute><dot-ec:OffenseWarningIndicator>false</dot-ec:OffenseWarningIndicator><dot-ec:OffenseViolatorTypeCode>1</dot-ec:OffenseViolatorTypeCode><dot-ec:OffensePayableIndicator>false</dot-ec:OffensePayableIndicator></dot-ec:Offense><dot-ec:Offense s:id="CitationOffense3"><dot-ec:OffenseCategoryCode>N</dot-ec:OffenseCategoryCode><dot-ec:OffenseCategoryText>Civil</dot-ec:OffenseCategoryText><dot-ec:OffenseSpeedingTypeCode>NONE</dot-ec:OffenseSpeedingTypeCode><dot-ec:OffenseViolatedStatute><j:StatuteCodeIdentification><nc:IdentificationID>89</nc:IdentificationID></j:StatuteCodeIdentification><j:StatuteCodeSectionIdentification><nc:IdentificationID>4A</nc:IdentificationID></j:StatuteCodeSectionIdentification><dot-ec:StatuteCodeFullIdentificationCode>89/4A</dot-ec:StatuteCodeFullIdentificationCode><dot-ec:StatuteCodeFullIdentificationText>MARKED LANES VIOLATION * c89 S4A</dot-ec:StatuteCodeFullIdentificationText></dot-ec:OffenseViolatedStatute><dot-ec:OffenseWarningIndicator>false</dot-ec:OffenseWarningIndicator><dot-ec:OffenseViolatorTypeCode>1</dot-ec:OffenseViolatorTypeCode><dot-ec:OffensePayableIndicator>true</dot-ec:OffensePayableIndicator></dot-ec:Offense><dot-ec:Offense s:id="CitationOffense4"><dot-ec:OffenseCategoryCode>N</dot-ec:OffenseCategoryCode><dot-ec:OffenseCategoryText>Civil</dot-ec:OffenseCategoryText><dot-ec:OffenseSpeedingTypeCode>90/17</dot-ec:OffenseSpeedingTypeCode><dot-ec:OffenseViolatedStatute><j:StatuteCodeIdentification><nc:IdentificationID>90</nc:IdentificationID></j:StatuteCodeIdentification><j:StatuteCodeSectionIdentification><nc:IdentificationID>17</nc:IdentificationID></j:StatuteCodeSectionIdentification><dot-ec:StatuteCodeSubSectionIdentification><nc:IdentificationID>A</nc:IdentificationID></dot-ec:StatuteCodeSubSectionIdentification><dot-ec:StatuteCodeFullIdentificationCode>90/17/A</dot-ec:StatuteCodeFullIdentificationCode><dot-ec:StatuteCodeFullIdentificationText>SPEEDING RATE OF SPEED EXCEEDING POSTED LIMIT * c90 S17</dot-ec:StatuteCodeFullIdentificationText></dot-ec:OffenseViolatedStatute><dot-ec:DrivingIncidentSpeed><j:DrivingIncidentRecordedSpeedRate><nc:MeasureText>80</nc:MeasureText></j:DrivingIncidentRecordedSpeedRate><j:DrivingIncidentLegalSpeedRate><nc:MeasureText>55</nc:MeasureText></j:DrivingIncidentLegalSpeedRate><dot-ec:SpeedLimitNotificationCode>1</dot-ec:SpeedLimitNotificationCode><dot-ec:SpeedLimitNotificationText>Posted</dot-ec:SpeedLimitNotificationText><dot-ec:DrivingIncidentRecordedSpeedMethodCode>ESTM</dot-ec:DrivingIncidentRecordedSpeedMethodCode><dot-ec:DrivingIncidentRecordedSpeedMethodText>Estimated</dot-ec:DrivingIncidentRecordedSpeedMethodText></dot-ec:DrivingIncidentSpeed><dot-ec:OffenseWarningIndicator>false</dot-ec:OffenseWarningIndicator><dot-ec:OffenseViolatorTypeCode>1</dot-ec:OffenseViolatorTypeCode><dot-ec:OffensePayableIndicator>true</dot-ec:OffensePayableIndicator><dot-ec:OffenseSurchargeAddedTypeCode>H</dot-ec:OffenseSurchargeAddedTypeCode><dot-ec:OffenseSurchargeAddedTypeText>Head Injury Surcharge</dot-ec:OffenseSurchargeAddedTypeText></dot-ec:Offense><j:Court s:id="court1"><nc:OrganizationIdentification><nc:IdentificationID>068</nc:IdentificationID></nc:OrganizationIdentification><nc:OrganizationLocation><nc:LocationAddress><dot-ec:StructuredAddress><dot-ec:LocationStreet><dot-ec:Street1FullText>300 Boylston Street</dot-ec:Street1FullText></dot-ec:LocationStreet><nc:LocationCityName>Clinton</nc:LocationCityName><dot-ec:LocationStateANSID20Code>MA</dot-ec:LocationStateANSID20Code><nc:LocationPostalCode>01510</nc:LocationPostalCode><nc:LocationPostalExtensionCode>0030</nc:LocationPostalExtensionCode></dot-ec:StructuredAddress></nc:LocationAddress></nc:OrganizationLocation><nc:OrganizationName>Clinton District Court</nc:OrganizationName></j:Court></dot-ec:ECitation></dcjis:EventBody></dcjis:EventTransaction></dcjis:DataExchange>

Since Power Query can't seem to handle this, I've also tried breaking up the XML into more manageable chunks (EventHeader, DataElements, etc.). The XML doesn't always include all sections, for example no citation data. I can successfully create the chunks when data exists, but can't come up with anything that can be parsed by Power Query when there's no data for a section.

I can substitute a blank or a NULL in my SQL script, but I don't know how make Power Query skip blanks or NULLs. 

The bottom line is that I need to either be able to make Power Query skip blanks/NULLs, or be able to provide some sort of XML container with no data that can be successfully parsed.

Thanks.

[lbendlin]

Seems to parse fine.  What would be the expected output after flattening?