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.