locked
Convert Xml CData to Table

    Question

  • Hi,

    I'm calling a web service that returns XML with a column of CData (commar delimted text) 

    The query producing this is :

    let
        Query2 = let
       Source = Xml.Tables(Web.Contents(etc..)
     
    in
        Source,
        output = Query2{0}[output]
    in
        output

    The data returned is 1 big column of data representing the complete CSV data (Cdata).   I'm trying to find a way to push this data into the Csv.Document object so the I get a table back.n  I'm not sure of the Syntax. 

    Can anyone give me a clue?

    Thanks in advance.

    Lee

    Monday, June 24, 2013 2:29 PM

All replies

  • I'm making some progress...

    let

    Query2 = let   Source = Xml.Tables(Web.Contents(etc..) in    Source,     output = Csv.Document(Query2{0}[output])   in    output

    I now get the Cdata in a table. 2 things are left. Promote the top row to column headers and change the types.

    I think the keywords here are : Table.PromoteHeaders and Table.TransformColumnTypes.

    This is a bit like making a jigsaw.

    Is M based F# by any chance?   I need to understand the program flow and wondered if an F# book would help.

    Monday, June 24, 2013 2:49 PM
  • It's amazing how asking questions gets the mind over obstacles. 

    I solved this.  Not sure if it's the best way and I don't fully understand it but here it is :

    let
        Query2 = let
       Source = Xml.Tables(Web.Contents(etc..)) 
    in
        Source,
    	
        output = Csv.Document(Query2{0}[output]),
        
        RowAsHeader = Table.PromoteHeaders(output),
        
        NewTable = Table.TransformColumnTypes(
                RowAsHeader,
                {
                   {"Column Name", type text}, 
                   {"Column2 name", type text}, 
                     etc..
                }
        )
    
    in
        NewTable
    I think I'm going to have lots of fun learning M.

    Monday, June 24, 2013 3:02 PM