Skip parsing blank XML

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.

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.