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 1 XML 2 XML 3 <?xml version="1.0" encoding="utf-8"?><RepeaterData><Version>1.0</Version><Ite...
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"
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.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      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"
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thats worked a treat, thank you.

         

        Also subscribbed to the YT channel!