Forum Discussion

johnjbolduc's avatar
johnjbolduc
Helper II
1 year ago

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 ...
Need Help
johnjbolduc's avatar
johnjbolduc
Helper II
1 year ago

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.

lbendlin's avatar
lbendlin
Super User
1 year ago

It will be the result of your flattening exercise.

 

I am sure you know that already but your XML is a round hole that you want to put into the Power Query square peg.  One is a hierarchy, the other one is a cartesian.  You have to make compromises when converting XML into a table.

  • johnjbolduc's avatar
    johnjbolduc
    Helper II
    1 year ago

    I appreciate all of your responses, but honestly I'm no closer to understanding how to resolve this issue than when I posted the question Friday. Your short answers seem to assume that I know a lot more about Power BI/Power Query than I do.

     

    I've looked at dozens of tutorials, example, blog posts, etc., but in all cases the XML they're using is ridiculously simple. I've found no way to expand those solutions to my much more complicated XML. 

     

    Others read the XML data from a file, which doesn't apply to my sitiuation either.

     

    Thanks again.

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      Yes, there is no "Easy"  button with XML like yours. You need to be able to articulate the acceptable tabular output. Then we can craft the Power Query code to get you there, and then we can also supply the code for the empty table.

      • johnjbolduc's avatar
        johnjbolduc
        Helper II
        1 year ago

        This is the output table I want:

         

        /******************************************************************************************************/
        IF OBJECT_ID('[dbo].[z_2b_XML_Data]') IS NOT NULL DROP TABLE [dbo].[z_2b_XML_Data];
        CREATE TABLE [dbo].[z_2b_XML_Data]
        ([MSPPSDataId] BIGINT NOT NULL
        , [UUID] VARCHAR(100) DEFAULT ''
        , [CreateDateTime] VARCHAR(20) DEFAULT ''
        , [TransactionSource] VARCHAR(20) DEFAULT ''
        , [MessageIdentifier] VARCHAR(100) DEFAULT ''
        , [TransactionMessageKey] VARCHAR(20) DEFAULT ''
        , [UserID] VARCHAR(10) DEFAULT ''
        , [OrganizationORIID] VARCHAR(20) DEFAULT ''
        , [DeviceAddress] VARCHAR(15) DEFAULT ''
        , [SchemaVersion] VARCHAR(10) DEFAULT ''
        , [ApplicationVersion] VARCHAR(10) DEFAULT ''
        , [ResponseMechanism] VARCHAR(25) DEFAULT ''
        , [CJISUserID] VARCHAR(15) DEFAULT ''
        , [CJISUserPassword] VARCHAR(50) DEFAULT ''
        , [CJISQueryAuthorizer] VARCHAR(255) DEFAULT ''
        , [CJISQueryOperator] VARCHAR(255) DEFAULT ''
        , [CJISQueryRequester] VARCHAR(255) DEFAULT ''
        , [CJISQueryPurposeCode] VARCHAR(10) DEFAULT ''
        , [ContentTypeCode] VARCHAR(20) DEFAULT ''
        , [ContentTypeText] VARCHAR(50) DEFAULT ''
        , [citationNo] VARCHAR(10) DEFAULT ''
        , [citationDate] VARCHAR(10) DEFAULT ''
        , [resultOfStopCode] VARCHAR(10) DEFAULT ''
        , [resultOfStopText] VARCHAR(50) DEFAULT ''
        , [violatorTypeCode] VARCHAR(10) DEFAULT ''
        , [violatorTypeText] VARCHAR(50) DEFAULT ''
        , [citationVoidedInd] VARCHAR(10) DEFAULT ''
        , [plateReaderInd] VARCHAR(10) DEFAULT ''
        , [licenseStateCode] VARCHAR(10) DEFAULT ''
        , [licenseStateText] VARCHAR(50) DEFAULT ''
        , [birthYear] VARCHAR(10) DEFAULT ''
        , [sexCode] VARCHAR(10) DEFAULT ''
        , [sexText] VARCHAR(50) DEFAULT ''
        , [raceCode] VARCHAR(10) DEFAULT ''
        , [raceText] VARCHAR(50) DEFAULT ''
        , [vehiclePlateStateCode] VARCHAR(10) DEFAULT ''
        , [vehiclePlateStateText] VARCHAR(50) DEFAULT ''
        , [DEvehiclePlateTypeCode] VARCHAR(10) DEFAULT ''
        , [vehiclePlateTypeText] VARCHAR(50) DEFAULT ''
        , [locationCityCode] VARCHAR(10) DEFAULT ''
        , [locationCityText] VARCHAR(75) DEFAULT ''
        , [locationRoadwayTypeCode] VARCHAR(10) DEFAULT ''
        , [locationRoadwayTypeText] VARCHAR(50) DEFAULT ''
        , [locationRouteNumberCode] VARCHAR(10) DEFAULT ''
        , [locationRouteNumberText] VARCHAR(50) DEFAULT ''
        , [searchInd] VARCHAR(10) DEFAULT ''
        , [issuingAgencyCode] VARCHAR(10) DEFAULT ''
        , [issuingAgencyText] VARCHAR(100) DEFAULT ''
        , [issuingOfficerFirstName] VARCHAR(50) DEFAULT ''
        , [issuingOfficerMI] VARCHAR(50) DEFAULT ''
        , [issuingOfficerLastName] VARCHAR(50) DEFAULT ''
        , [issuingOfficerIdNo] VARCHAR(50) DEFAULT ''
        , [offenseSpeedingInd] VARCHAR(10) DEFAULT ''
        , [activityCode] VARCHAR(10) DEFAULT ''
        , [activityText] VARCHAR(50) DEFAULT ''
        , [verbalWarningInd] VARCHAR(10) DEFAULT ''
        , [organizationInd] VARCHAR(10) DEFAULT ''
        , [electronicCitationInd] VARCHAR(10) DEFAULT ''
        , [transactionType] VARCHAR(10) DEFAULT ''
        , [ActivityIdentification1] VARCHAR(10) DEFAULT ''
        , [ActivityDate1] VARCHAR(10) DEFAULT ''
        , [OrganizationIdentification1] VARCHAR(10) DEFAULT ''
        , [OrganizationName1] VARCHAR(50) DEFAULT ''
        , [CitationFineAmount1] VARCHAR(10) DEFAULT ''
        , [IssuingOfficerBadgeNo1] VARCHAR(50) DEFAULT ''
        , [SubjectArrestIndicator1] VARCHAR(10) DEFAULT ''
        , [SubjectArrestDate1] VARCHAR(10) DEFAULT ''
        , [IncidentDate1] VARCHAR(10) DEFAULT ''
        , [IncidentTime1] VARCHAR(10) DEFAULT ''
        , [IncidentLocationCityName1] VARCHAR(255) DEFAULT ''
        , [IncidentLocationStateANSID20Code1] VARCHAR(255) DEFAULT ''
        , [IncidentLocationStateName1] VARCHAR(255) DEFAULT ''
        , [IncidentLocationCityCode1] VARCHAR(10) DEFAULT ''
        , [IncidentLocationHighwayFullText1] VARCHAR(MAX) DEFAULT ''
        , [IncidentLocation1] VARCHAR(255) DEFAULT ''
        , [IncidentLatitudeDegreeValue1] VARCHAR(20) DEFAULT ''
        , [IncidentLatitudeMinuteValue1] VARCHAR(20) DEFAULT ''
        , [IncidentLatitudeSecondValue1] VARCHAR(20) DEFAULT ''
        , [IncidentLongitudeDegreeValue1] VARCHAR(20) DEFAULT ''
        , [IncidentLongitudeMinuteValue1] VARCHAR(20) DEFAULT ''
        , [IncidentLongitudeSecondValue1] VARCHAR(20) DEFAULT ''
        , [ViolatorCitationTypeCode1] VARCHAR(10) DEFAULT ''
        , [ViolatorCitationTypeText1] VARCHAR(50) DEFAULT ''
        , [VehicleSearchNonInventoryIndicator1] VARCHAR(10) DEFAULT ''
        , [CitationTypeCode1] VARCHAR(10) DEFAULT ''
        , [CitationTypeText1] VARCHAR(50) DEFAULT ''
        , [AcknowledgementTypeCode1] VARCHAR(10) DEFAULT ''
        , [AcknowledgementTypeText1] VARCHAR(50) DEFAULT ''
        , [ViolatorAgentSignatureIndicator1] VARCHAR(10) DEFAULT ''
        , [OfficerSignatureIndicator1] VARCHAR(10) DEFAULT ''
        , [OfficerSignatureModifiedIndicator1] VARCHAR(10) DEFAULT ''
        , [OfficerSignatureText1] VARCHAR(100) DEFAULT ''
        , [CertificationTypeCode1] VARCHAR(10) DEFAULT ''
        , [CertificationTypeText1] VARCHAR(50) DEFAULT ''
        , [ViolatorOrganizationIndicator1] VARCHAR(10) DEFAULT ''
        , [TrafficCrashInvolvedIndicator1] VARCHAR(10) DEFAULT ''
        , [IssuingAgencyUniqueCitationID1] VARCHAR(10) DEFAULT ''
        , [CitationPaperOriginationIndicator1] VARCHAR(10) DEFAULT ''
        , [MACCSCitationID1] VARCHAR(20) DEFAULT ''
        , [OfficerGivenName] VARCHAR(50) DEFAULT ''
        , [OfficerMI] VARCHAR(50) DEFAULT ''
        , [OfficerSurName] VARCHAR(50) DEFAULT ''
        , [ViolatorBirthDate] VARCHAR(10) DEFAULT ''
        , [ViolatorGivenName] VARCHAR(50) DEFAULT ''
        , [ViolatorMI] VARCHAR(50) DEFAULT ''
        , [ViolatorSurName] VARCHAR(50) DEFAULT ''
        , [ViolatorRaceCode] VARCHAR(10) DEFAULT ''
        , [ViolatorRaceText] VARCHAR(50) DEFAULT ''
        , [ViolatorSexCode] VARCHAR(10) DEFAULT ''
        , [ViolatorSexText] VARCHAR(50) DEFAULT ''
        , [ViolatorDriverLicenseIdentification] VARCHAR(50) DEFAULT ''
        , [ViolatorDrivingJurisdictionAuthorityANSID20Code] VARCHAR(10) DEFAULT ''
        , [ViolatorIdentificationJurisdictionText] VARCHAR(50) DEFAULT ''
        , [ViolatorIdentificationJurisdictionFIPS10-4Code] VARCHAR(10) DEFAULT ''
        , [ViolatorIdentificationJurisdictionFIPS10-4Text] VARCHAR(50) DEFAULT ''
        , [ViolatorIdentificationClassCode] VARCHAR(10) DEFAULT ''
        , [ViolatorIdentificationClassText] VARCHAR(50) DEFAULT ''
        , [ViolatorDriverLicenseCommercialIndicator] VARCHAR(10) DEFAULT ''
        , [ViolatorUnlicensedIndicator] VARCHAR(10) DEFAULT ''
        , [violatorStreet1FullText] VARCHAR(100) DEFAULT ''
        , [violatorLocationCityName] VARCHAR(50) DEFAULT ''
        , [violatorLocationStateANSID20Code] VARCHAR(10) DEFAULT ''
        , [violatorLocationStateName] VARCHAR(50) DEFAULT ''
        , [violatorLocationCountryFIPS10-4Code] VARCHAR(50) DEFAULT ''
        , [violatorLocationCountryName] VARCHAR(50) DEFAULT ''
        , [violatorLocationPostalCode] VARCHAR(50) DEFAULT ''
        , [VehicleColorPrimaryCode] VARCHAR(10) DEFAULT ''
        , [ItemMakeName] VARCHAR(75) DEFAULT ''
        , [ItemModelName] VARCHAR(75) DEFAULT ''
        , [ItemMakeName/ItemModelName] VARCHAR(100) DEFAULT ''
        , [ItemModelYearDate] VARCHAR(10) DEFAULT ''
        , [VehicleCMVIndicator] VARCHAR(10) DEFAULT ''
        , [ConveyanceRegistrationPlateIdentification] VARCHAR(25) DEFAULT ''
        , [ConveyanceRegistrationPlateExpirationDate] VARCHAR(10) DEFAULT ''
        , [VDrivingJurisdictionAuthorityANSID20Code] VARCHAR(10) DEFAULT ''
        , [VIdentificationJurisdictionFIPS10-4Code] VARCHAR(10) DEFAULT ''
        , [VVehiclePlateTypeCode] VARCHAR(10) DEFAULT ''
        , [VehiclePassenger16PlusIndicator] VARCHAR(10) DEFAULT ''
        , [HazMatPlacardDisplayedIndicator] VARCHAR(10) DEFAULT ''
        , [VehicleRegistrationUnknownIndicator] VARCHAR(10) DEFAULT ''
        , [InsuranceCarrierName] VARCHAR(255) DEFAULT ''
        , [ActivityDescriptionText1] VARCHAR(100) DEFAULT ''
        , [OffenseCategoryCode1] VARCHAR(10) DEFAULT ''
        , [OffenseCategoryText1] VARCHAR(50) DEFAULT ''
        , [OffenseSpeedingTypeCode1] VARCHAR(10) DEFAULT ''
        , [StatuteCodeIdentification1] VARCHAR(10) DEFAULT ''
        , [StatuteCodeSectionIdentification1] VARCHAR(50) DEFAULT ''
        , [StatuteCodeSubSectionIdentification1] VARCHAR(50) DEFAULT ''
        , [StatuteCodeFullIdentificationCode1] VARCHAR(25) DEFAULT ''
        , [StatuteCodeFullIdentificationText1] VARCHAR(255) DEFAULT ''
        , [DrivingIncidentRecordedSpeedRate1] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentLegalSpeedRate1] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationCode1] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationText1] VARCHAR(50) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodCode1] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodText1] VARCHAR(50) DEFAULT ''
        , [OffenseWarningIndicator1] VARCHAR(10) DEFAULT ''
        , [AssessmentAmount1] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeCode1] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeText1] VARCHAR(50) DEFAULT ''
        , [OffensePayableIndicator1] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeCode1] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeText1] VARCHAR(50) DEFAULT ''
        , [ActivityDescriptionText2] VARCHAR(100) DEFAULT ''
        , [OffenseCategoryCode2] VARCHAR(10) DEFAULT ''
        , [OffenseCategoryText2] VARCHAR(50) DEFAULT ''
        , [OffenseSpeedingTypeCode2] VARCHAR(10) DEFAULT ''
        , [StatuteCodeIdentification2] VARCHAR(10) DEFAULT ''
        , [StatuteCodeSectionIdentification2] VARCHAR(50) DEFAULT ''
        , [StatuteCodeSubSectionIdentification2] VARCHAR(50) DEFAULT ''
        , [StatuteCodeFullIdentificationCode2] VARCHAR(25) DEFAULT ''
        , [StatuteCodeFullIdentificationText2] VARCHAR(255) DEFAULT ''
        , [DrivingIncidentRecordedSpeedRate2] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentLegalSpeedRate2] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationCode2] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationText2] VARCHAR(50) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodCode2] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodText2] VARCHAR(50) DEFAULT ''
        , [OffenseWarningIndicator2] VARCHAR(10) DEFAULT ''
        , [AssessmentAmount2] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeCode2] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeText2] VARCHAR(50) DEFAULT ''
        , [OffensePayableIndicator2] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeCode2] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeText2] VARCHAR(50) DEFAULT ''
        , [ActivityDescriptionText3] VARCHAR(100) DEFAULT ''
        , [OffenseCategoryCode3] VARCHAR(10) DEFAULT ''
        , [OffenseCategoryText3] VARCHAR(50) DEFAULT ''
        , [OffenseSpeedingTypeCode3] VARCHAR(10) DEFAULT ''
        , [StatuteCodeIdentification3] VARCHAR(10) DEFAULT ''
        , [StatuteCodeSectionIdentification3] VARCHAR(50) DEFAULT ''
        , [StatuteCodeSubSectionIdentification3] VARCHAR(50) DEFAULT ''
        , [StatuteCodeFullIdentificationCode3] VARCHAR(25) DEFAULT ''
        , [StatuteCodeFullIdentificationText3] VARCHAR(255) DEFAULT ''
        , [DrivingIncidentRecordedSpeedRate3] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentLegalSpeedRate3] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationCode3] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationText3] VARCHAR(50) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodCode3] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodText3] VARCHAR(50) DEFAULT ''
        , [OffenseWarningIndicator3] VARCHAR(10) DEFAULT ''
        , [AssessmentAmount3] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeCode3] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeText3] VARCHAR(50) DEFAULT ''
        , [OffensePayableIndicator3] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeCode3] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeText3] VARCHAR(50) DEFAULT ''
        , [ActivityDescriptionText4] VARCHAR(100) DEFAULT ''
        , [OffenseCategoryCode4] VARCHAR(10) DEFAULT ''
        , [OffenseCategoryText4] VARCHAR(50) DEFAULT ''
        , [OffenseSpeedingTypeCode4] VARCHAR(10) DEFAULT ''
        , [StatuteCodeIdentification4] VARCHAR(10) DEFAULT ''
        , [StatuteCodeSectionIdentification4] VARCHAR(50) DEFAULT ''
        , [StatuteCodeSubSectionIdentification4] VARCHAR(50) DEFAULT ''
        , [StatuteCodeFullIdentificationCode4] VARCHAR(25) DEFAULT ''
        , [StatuteCodeFullIdentificationText4] VARCHAR(255) DEFAULT ''
        , [DrivingIncidentRecordedSpeedRate4] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentLegalSpeedRate4] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationCode4] VARCHAR(10) DEFAULT ''
        , [SpeedLimitNotificationText4] VARCHAR(50) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodCode4] VARCHAR(10) DEFAULT ''
        , [DrivingIncidentRecordedSpeedMethodText4] VARCHAR(50) DEFAULT ''
        , [OffenseWarningIndicator4] VARCHAR(10) DEFAULT ''
        , [AssessmentAmount4] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeCode4] VARCHAR(10) DEFAULT ''
        , [OffenseViolatorTypeText4] VARCHAR(50) DEFAULT ''
        , [OffensePayableIndicator4] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeCode4] VARCHAR(10) DEFAULT ''
        , [OffenseSurchargeAddedTypeText4] VARCHAR(50) DEFAULT ''
        , [CourtCode] VARCHAR(10) DEFAULT ''
        , [CourtStreet] VARCHAR(100) DEFAULT ''
        , [CourtCity] VARCHAR(50) DEFAULT ''
        , [CourtLocationStateANSID20Code] VARCHAR(10) DEFAULT ''
        , [CourtPostalCode] VARCHAR(50) DEFAULT ''
        , [CourtName] VARCHAR(100) DEFAULT '');
        GO