Forum Discussion
converting column to XML string
Hi,
I'm new to Power BI. But I'm trying to POST a SOAP request which takes input as xml. I have a query (name Headerids) that has a column name xmltaskids. I would like to convert that Column to the below xml String. Please help me how can I convert ALL the records to a single String so that it represents XMLbody
Please note:
Each row in the column --> has XML tag and Value.
e.g. 1st row is <taskId>28161430</taskId> in the column
2nd row is <taskId>27739924</taskId>
body = "<Envelope xmlns=""http://schemas.xmlsoap.org/soap/envelope/"">
<Body>
<readTasks xmlns=""http://mercury.com/ppm/pm/service/1.0"">
<taskId>28161430</taskId>
<taskId>27739924</taskId>
<taskId>27739925</taskId>
<taskId>27739926</taskId>
<taskId>27739927</taskId>
<taskId>28161430</taskId>
</readTasks>
</Body>
</Envelope> ",
1 Reply
There might be other ways of doing this, but used Table.AddColumns to add a column with a constant value of 1. Then I used Table.Group to group all the rows on that value and used Text.Combine to join that xmltaskids column.
eg.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOLkkszvZMAbNTjSwMzQxNjA0gMvrIUkqxOlh1mJsbW1oamZCsw5RkHWYk6zAnQQc+n8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [xmltaskids = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"xmltaskids", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each 1), #"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"TaskIds", each Text.Combine( [xmltaskids]), type text}}), #"FirstRow" = #"Grouped Rows"{[Group=1]}[TaskIds], body = Text.Combine({"<Envelope xmlns=""http://schemas.xmlsoap.org/soap/envelope/""> <Body> <readTasks xmlns=""http://mercury.com/ppm/pm/service/1.0"">", #"FirstRow", "</readTasks> </Body> </Envelope> "}) in body
