Forum Discussion

hgorina's avatar
hgorina
Frequent Visitor
10 years ago
Solved

import (filter) xml nodes by node name

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

 

 

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    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

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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's avatar
      hgorina
      Frequent Visitor
      10 years ago

      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:)