Hi,

I am trying to create a xml document from a SQL select statement and I am not sure of the correct approach. I am brand new to visualcron and automation in general.

What I have so far is a SQL task with a query in the text tab

select top 1 ItemId as product_id,
itemName as title,
OWIDescriptionHTML as description,
OWIManufacturerName as brand,
CAST(MSRPPrice AS NUMERIC(36,2)) as list_price,
CAST(currentPrice as NUMERIC(36,2)) AS sale_price,
CAST(OWIStockSummaryQtyAvail AS NUMERIC(36,2)) as quantity,
CASE StockUnavailable
when 0 then 'No'
when 1 then 'Yes'
END
from {TABLENAME GOES HERE}

I have separated the columns with '~' character

Then I have a write file task which because it is XML I've done the following

<product_id>{STRING(GetColumn|1|1|~|{USERVAR(product_id)}})}</product_id>
<name>{STRING(GetColumn|1|2|~|{USERVAR(product_id)}})}</name>
<description>{STRING(GetColumn|1|3|~|{USERVAR(product_id)}})}</description>

USERVAR(product) holds the stdOut from SQL task {TASK(xxxx|StdOut)}

This appears to work though I hope there is a better solution, one problem I have though is with StdOut from my SQL task

Even though I do a select Top 1 I get more than one line in stdOut and I think it's because one of my fields in the database has CRLF codes. Has anyone else experienced this? Or is there a better way to do what I am trying to do?

Thanks,
Chris

You can make it easy for you:

https://docs.microsoft.c...er?view=sql-server-ver15 

Basically:

select top 1 ItemId as product_id,
itemName as title,
OWIDescriptionHTML as description,
OWIManufacturerName as brand,
CAST(MSRPPrice AS NUMERIC(36,2)) as list_price,
CAST(currentPrice as NUMERIC(36,2)) AS sale_price,
CAST(OWIStockSummaryQtyAvail AS NUMERIC(36,2)) as quantity,
CASE StockUnavailable
when 0 then 'No'
when 1 then 'Yes'
END
from {TABLENAME GOES HERE}
FOR XML AUTO

---

Mange Tak!

Thanks alot!