import (filter) xml nodes by node name

[hgorina]

I have multiple XML files witn variety of data stored in them.

i need to create tables based on the node names. The data contained in a different nodes of XML file is quite different

here is an example of data:

<BIMListDS xmlns="http://tempuri.org/BIMListDS.xsd">
  <DatabaseInfo>
    <ID>a71e1eb0-9811-448c-952c-c5f99d197bfd</ID>
    <SchemaVersion>3</SchemaVersion>
    <Name>01 PW Imperial 2016</Name>
    <LastUpdatedDate>2016-05-24T14:29:06.8645433</LastUpdatedDate>
    <IsRemote>true</IsRemote>
    <RemoteServerAddress>net.tcp://PW02BIMList:5055/BIMListDataService</RemoteServerAddress>
    <IsCloud>false</IsCloud>
    <CreatedBy>46d418f4-a599-44ee-9a12-72a39b123d20</CreatedBy>
    <LastUpdateBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</LastUpdateBy>
  </DatabaseInfo>
  <User>
    <ID>46d418f4-a599-44ee-9a12-72a39b123d20</ID>
    <Name>gorinah</Name>
  </User>
  <User>
    <ID>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</ID>
    <Name>gorina_admin</Name>
  </User>
  <User>
    <ID>cd195d56-5603-4d97-a38f-baeecc3be4a0</ID>
    <Name>lopezb</Name>
  </User>
  <User>
    <ID>58c53056-67e8-41ac-980d-d75586b12c13</ID>
    <Name>moorej</Name>
  </User>
  <User>
    <ID>d269e3ba-163e-4903-8cba-07c520dc0046</ID>
    <Name>clinea</Name>
  </User>
  <User>
    <ID>c26b59d7-bb5b-4213-a8d6-06cc89c7c7a8</ID>
    <Name>hamiltonad</Name>
  </User>
 <ContentItem>
    <ID>22389006-df18-419f-a657-d005bb31d642</ID>
    <Name>Graphic-Cross</Name>
    <CategoryID>-2000279</CategoryID>
    <Path>S:\_Firmwide\BIMListContent\2016-Imperial\_Hatches(RVT).rvt</Path>
    <IsFamily>false</IsFamily>
    <IsVisible>true</IsVisible>
    <AverageRating>0</AverageRating>
    <TotalLoadCount>0</TotalLoadCount>
    <AddedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</AddedBy>
    <LastUpdatedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</LastUpdatedBy>
    <AddedDate>2016-05-09T11:57:38.3246744-05:00</AddedDate>
    <LastUpdatedDate>2016-05-09T11:57:38.3246744-05:00</LastUpdatedDate>
    <ElementId>3215</ElementId>
    <IsOffline>false</IsOffline>
  </ContentItem>
  <ContentItem>
    <ID>d36f216f-6989-4417-8668-9c52dfc923ec</ID>
    <Name>Graphic-Crosshatch - Large</Name>
    <CategoryID>-2000279</CategoryID>
    <Path>S:\_Firmwide\BIMListContent\2016-Imperial\_Hatches(RVT).rvt</Path>
    <IsFamily>false</IsFamily>
    <IsVisible>true</IsVisible>
    <AverageRating>0</AverageRating>
    <TotalLoadCount>0</TotalLoadCount>
    <AddedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</AddedBy>
    <LastUpdatedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</LastUpdatedBy>
    <AddedDate>2016-05-09T11:57:43.9126149-05:00</AddedDate>
    <LastUpdatedDate>2016-05-09T11:57:43.9126149-05:00</LastUpdatedDate>
    <ElementId>3231</ElementId>
    <IsOffline>false</IsOffline>
  </ContentItem>

I need to create tables based on the node names. the number of nodes of each type varies

I have followed the directions from the following post: http://community.powerbi.com/t5/Desktop/Import-multiple-XML-files/m-p/8900/highlight/true#M1596

it helped a lot, thanks!

however i need to select just some node types, and want to base it on the name

the function

(path as text) =>
let
    Source = Xml.Tables(File.Contents(path)),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"ID", type text}, {"SchemaVersion", Int64.Type}, {"Name", type text}, {"LastUpdatedDate", type datetime}, {"IsRemote", type logical}, {"RemoteServerAddress", type text}, {"IsCloud", type logical}})
in
    #"Changed Type"

allows me to traverse the xml file and get the tables sequentually, but I am trying to select only nodes I am interested in.

many thanks for your help

[Eric_Zhang · Accepted solution]

@hgorina

Not quite sure about your requirement, if you'd like to load the elements table by name but not sequentially(0,1,2 etc), you can try

let
    Source = Xml.Tables(File.Contents("C:\Users\user1\Desktop\1.XML")),
    Table2 = Source{[Name="User"]}[Table]
in
    Table2

[hgorina]

thanks!

It worked.

perhaps I need to clarify the task:

in multiple XMLs I am working with, some tables exist in all of them, and some tables exist in just some XMLs. Also, the table sequence order is not the same in some of them. that is why it was omportatn to get them by name

many thanks again:)