Forum Discussion
Skip parsing blank XML
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.
- johnjbolduc1 year agoHelper II
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.
- lbendlin1 year agoSuper User
Please provide examples of both XML scenarios.
- johnjbolduc1 year agoHelper II
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.
- johnjbolduc1 year agoHelper II
The problem is that I don't know how to create that empty XML or table for the OTHERWISE clause.
- lbendlin1 year agoSuper User
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.
- johnjbolduc1 year agoHelper II
Is there a good tutorial on this anywhere? I know the layout of the table I need, but don't know how/where in Powery Query to define it.