Execute SQL and Execute SQL FOR XML gives different output

(0) Share

Report

Posted on by kris_T

Hi All,

I am running an sql statement on an Azure database. Results are returned back perfect as "QueryResult".

I can VIEW the correct results :

Next step is to write this result to a txt file. Output is perfect as comma separated values.

However when I change my SQL to include

FOR XML AUTO, ELEMENTS (which runs fine in SSMS by the way)

the QUERYRESULT View changes to :

Obviously the next step (save to file) throws an error because of "Type of value has a mismatch with column type. Expected type is Byte. Can I set the type of a QueryResult ?

Any advice or guidance would be appreciated.

Kris

Categories:

Power Automate Desktop

All responses (7)

Answers (0)

Sort by

geavgous Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi kris_T,

Thank you so much for trying out Power Automate Desktop! Before digging into the issue, I would like to ask you why did you switch to FOR XML AUTO since the output of it is no longer a datatable but an XML formatted output, and I think it's a one row result?
Thanks,
George
geavgous Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

To add up to that, after you have saved your query results into a datatable, you could easily add the next action in Power Automate Desktop called "write text to csv" and then just select the datatable variable and that's it 🙂

Thanks,
George
kris_T 19 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi George,
indeed the output is now XML formatted, but this is exactly what I need.
It is however broken over about 700 lines, or records I should say.
kris_T 19 on at

Like
a
(0)

Report
Copy link

Link copied!

OK, writing my sql-select into a CSV works fine.
But I require a meaningful XML output.
So is there another step to turn CSV into XML ?

Thanks for your assistance,
Kris
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

http://convertcsv.com/csv-to-xml.htm

Enjoy!
geavgous Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi,
There is no need in using external tools. After you save your result set you got from sql (not the xml one), you may launch excel so that you create an excel instance ( and you can also hide it too!), add another action called "write to excel" where you can select as an input, the variable containing the sql result. That will write everything to excel.
Right after that, add the next action called "save excel" and inside the action properties select save as a different type, namely xml file.
Close excel instance using the action "Close excel" and you're done 🙂
Thanks,
George
kris_T 19 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks George
I'm gonna give this a try and let you know.
It just seems like a long way to move data from SQL to a plain XML file;
Have a great w.e.

Kris