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.

[johnjbolduc]

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.

[lbendlin]

You mean the stuff after "Person"?

It sits in a different branch.

[johnjbolduc]

How do I get Power Query to parse a different branch? Thanks.

[lbendlin]

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.

[johnjbolduc]

The problem is that I don't know how to create that empty XML or table for the OTHERWISE clause.

[lbendlin]

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.

[johnjbolduc]

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]

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]

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]

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]

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

[johnjbolduc]

Also, please note that this table could be missing fields. The current list is from me perusing many of the longest XML columns. Thanks.

[lbendlin]

What you are attempting to do is pretty much the antithesis of Power BI. It's something you would do in Excel (and poorly).  Power BI does not like wide tables with gazillions of columns. 

If your destination is a database anyway then you can also consider using the native XML features of databases like SQL Server. You wouldn't even need to create the table - you could just store the XML as text blobs and then parse them in your SQL queries.

Let's assume for a second that that output would be possible. Your empty table would look like this

Select top 0 * from [dbo].[z_2b_XML_Data]

You would use that as one of your data sources, and would supply it in the "otherwise"  branch if the XML read fails.

[johnjbolduc]

Excel can't handle the data volume. I've spent many hours trying to use XPATH and XQUERY in SQL, but again my XML is so much more complicated than any examples that I can't expand them to meet my needs. Plus my XML is in a column, not a file, which seems to be rarely addressed.

At any rate, thanks.

[lbendlin]

Once you have the data ingested what is the next step - what are you planning to do with it?  Is this a one-off report, or do you want to share that semantic model with others?  Have you considered normalizing the data?

[johnjbolduc]

I need to get all that XML data into SQL server so other teams can access it. I'll also be using it for some analyses and visualizations. The data in the XML are already kind of "flat," so I'm not sure normalizing would provide any benefit. 

[lbendlin]

The XML data is the furthest from "flat"  that you can get. It has dozens of hierarchy layers in dozens of different branches.  As you said , it is way more complex than any examples you will find online. 

Here's my proposal on how to start the normalization:

Header:

let
    Source = Xml.Tables(File.Contents("C:\Users\xxx\Downloads\text.xml")),
    Table = Source{0}[Table],
    Table1 = Table{0}[Table],
    #"Expanded CJISQueryRequester" = Table.ExpandTableColumn(Table1, "CJISQueryRequester", {"PersonFullName"}, {"CJISQueryRequester"}),
    #"Expanded CJISQueryOperator" = Table.ExpandTableColumn(#"Expanded CJISQueryRequester", "CJISQueryOperator", {"PersonFullName"}, {"CJISQueryOperator"}),
    #"Expanded CJISQueryAuthorizer" = Table.ExpandTableColumn(#"Expanded CJISQueryOperator", "CJISQueryAuthorizer", {"PersonFullName"}, {"CJISQueryAuthorizer"}),
    #"Expanded CJISUserID" = Table.ExpandTableColumn(#"Expanded CJISQueryAuthorizer", "CJISUserID", {"ID"}, {"CJISUserID"}),
    #"Expanded OrganizationORIID" = Table.ExpandTableColumn(#"Expanded CJISUserID", "OrganizationORIID", {"ID"}, {"OrganizationORIID"})
in
    #"Expanded OrganizationORIID"


Paystation:

let
    Source = Xml.Tables(File.Contents("C:\Users\xxx\Downloads\text.xml")),
    Table = Source{0}[Table],
    Table1 = Table{1}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    DataElements = Table3{0}[DataElements],
    DataElement = DataElements{0}[DataElement],
    #"Expanded http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" = Table.ExpandTableColumn(DataElement, "http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension", {"Attribute:elementName"}, {"Attribute:elementName"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension", List.Distinct(#"Expanded http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension"[#"Attribute:elementName"]), "Attribute:elementName", "Element:Text")
in
    #"Pivoted Column"

ECitation:

let
    Source = Xml.Tables(File.Contents("C:\Users\xxx\Downloads\text.xml")),
    Table = Source{0}[Table],
    Table1 = Table{1}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{1}[Table]
in
    Table3

The ECitation object alone is far, far more complex than what you would normally attempt to flatten.  I'd say anything beyond four levels of hierarchy is very very ambitious.

[johnjbolduc]

Thanks, but your example assumes reading the XML from a file. My XML is in a SQL Server database column.

I'm guessing what I want to do isn't really doable given the complexity and volume of data.

[lbendlin]

I would go back to SQL views that parse the XML directly, and I would do separate views that focus on the individual parts.  Then you can use Power BI  to bring at least some resemblance of a data model into play.

[johnjbolduc]

I started out trying to do all this in SQL using XQUERY but got nowhere at all. I was actually able to use string functions in my SQL to parse out the pieces, and it works fine, but the performance is horrendous.