Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Nested XML

Hi everyone,

 

I have a dataset which contains 3 XML columns. As  per the below dataset.

 

XML 1XML 2XML 3
<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version>1.0</Version><Items><Item><_68314b7831992f9f8d5e7a725ac659c1 type="System.String">test</_68314b7831992f9f8d5e7a725ac659c1><_531ea9e5be0e9226154a6a7e1dc1a130 type="System.String">Safety Valves</_531ea9e5be0e9226154a6a7e1dc1a130><_c9900ef32f210afc9c82eb1d2c3c1f29 type="System.String">1234</_c9900ef32f210afc9c82eb1d2c3c1f29><_a48b2fb7f47ecd63ec47072da6fb32b9 type="System.String">1234</_a48b2fb7f47ecd63ec47072da6fb32b9><_23e7af02b8986bb9f7c72734eefe74f8 type="System.String">On tools</_23e7af02b8986bb9f7c72734eefe74f8><_648088b77175e86d99d0f0dadbb2a43c type="System.String">IST Maintenance</_648088b77175e86d99d0f0dadbb2a43c><_2c69e069b76b5469b7a37b892d6efa60 type="System.String">me</_2c69e069b76b5469b7a37b892d6efa60><_a056422922319cf1758dde9c07206d49 type="System.String">&amp;lt;?xml version=&amp;quot;1.0&amp;quot; encoding=&amp;quot;utf-8&amp;quot;?&amp;gt;&amp;lt;RepeaterData&amp;gt;&amp;lt;Version&amp;gt;&amp;lt;/Version&amp;gt;&amp;lt;Items&amp;gt;...&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;RepeaterData&gt;&lt;Version /&gt;&lt;Items&gt;&lt;Item&gt;&lt;control_EmployeeName type=&quot;System.String&quot;&gt;MARC&lt;/control_EmployeeName&gt;&lt;control_NHours type=&quot;System.Int32&quot;&gt;1&lt;/control_NHours&gt;&lt;control_PHours type=&quot;System.Int32&quot;&gt;1&lt;/control_PHours&gt;&lt;control_DHours type=&quot;System.Int32&quot;&gt;1&lt;/control_DHours&gt;&lt;/Item&gt;&lt;Item&gt;&lt;control_EmployeeName type=&quot;System.String&quot;&gt;RYAN&lt;/control_EmployeeName&gt;&lt;control_NHours type=&quot;System.Int32&quot;&gt;12&lt;/control_NHours&gt;&lt;control_PHours type=&quot;System.String&quot;&gt;&lt;/control_PHours&gt;&lt;control_DHours type=&quot;System.Int32&quot;&gt;2&lt;/control_DHours&gt;&lt;/Item&gt;&lt;Item&gt;&lt;control_EmployeeName type=&quot;System.String&quot;&gt;JOHN&lt;/control_EmployeeName&gt;&lt;control_NHours type=&quot;System.Int32&quot;&gt;4&lt;/control_NHours&gt;&lt;control_PHours type=&quot;System...&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;RepeaterData&gt;&lt;Version /&gt;&lt;Items&gt;&lt;Item&gt;&lt;NonProductiveTime type=&quot;System.String&quot;&gt;Travel time (welfare to work-fronts)&lt;/NonProductiveTime&gt;&lt;NonProductiveTimeMinutes type=&quot;System.Double&quot;&gt;20&lt;/NonProductiveTimeMinutes&gt;&lt;/Item&gt;&lt;/Items&gt;&lt;/RepeaterData&gt;

 

XML column 1 expands perfect when using the parse XML function within Power Query but XML columns 2 & 3 get error messages.

 

I have tried replacing the &lt; with < in columns 2 & 3 in order to make it look similar to column 1 but still no luck.

 

Any ideas as to where I might be going wrong?

 

Many thanks,

 

Elliot 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Anonymous I was able to confirm for XML column 3 that if you do the following replacements that it parses as XML:

    &lt;  to <

    &gt; to >

    &quot; to "

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZExC8IwEIX/Suikg6Z2EKGISxcHRVRc1CHqVYJpTpNL1X9vpVICqeKWe5d738tlu412Lo6ToaJ08igUK8FYiXpcqzeHlA76sVcx0Ec8SX32rzjKeyOvntTnM6WN+xKuIAhMJkiE3U2NZTxsTQkK2y6H6hz1wuDJHUmWsJYFMHpewY+6etpqsr8iU73B0xuvtRElKEbv4c4dVC5M5YLsjubSyw1qst2GxwPgH5lmUjsC+y1ahu6goC1aEv8Af1xDPm9fFf+yWN76U9F+/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","&lt;","<",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","&gt;",">",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","&quot;","""",Replacer.ReplaceText,{"Column1"}),
    #"Parsed XML" = Table.TransformColumns(#"Replaced Value2",{},Xml.Tables)
in
    #"Parsed XML"

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Have you also tried replacing the &gt; with > as well?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Greg,

       

      Thanks for your quick response. I just tried this and still experience an error on columns 2 & 3.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous It's almost certainly an encoding issue. It's like XML columns 2 and 3 were HTML encoded or something and that's just not going to work the the XML parser so things like &quote; would need to be changed to " and such. It's just not valid XML it is HTML encoded XML or something weird like that. Let me see if I can find something that will allow you to convert all of the HTML encoding back to normal.