Forum Discussion
Skip parsing blank XML
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
Also, please note that this table could be missing fields. The current list is from me perusing many of the longest XML columns. Thanks.
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.
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.
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?
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.
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 Table3The 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.
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.
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.
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.
What's the "make and model" of your SQL Server? Do you know the db admin?
And SSMS v20.
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.
I'm just a contractor for Commonwealth of Massachusetts; there's no way I can make that happen.
I figured as much. Make sure to manage your client's expectations.
Greetings from around the block.
Thanks so much!
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
Unfortunately, no. My XML is much too complicated for anything offered here.
