Hi,

I have a situation where I have to pull data from one Oracle database, transform it into an XML string, and insert that XML string into a separate Oracle database. Pulling from the first database isn't an issue, but I'm trying to figure out how to turn those SQL results into an XML string in the expected format. I've tried using the XML Create Node task, but from what I can tell, all of those XML tasks require an existing XML file - they're not meant for creating an XML file from scratch.

The only way I can think of doing this is to create job variables, something like XML_HEADER, XML_MESSAGE, XML_MESSAGE_DETAIL, etc, each of which contains a different part of the XML in the format that the destination database expects. Each of these job variables would reference task/loop variables, pointing back to my SQL results. I imagine this would work, but it would be incredibly ugly to do the initial setup and to maintain later on if we decide we need to bring in another field, so I was hoping someone knows of an easier way. E.g. in the Create Node task, there's an interface where I can add the different elements of the node in a nice and neat gridview, and I could easily put the task variables there.

Thanks.

What is an XML string exactly. XML can have many different formats. I think the easiest approach is to use Oracle engine and methods to convert result to your XML format - should be a method for that.

---

Hi
I am new to VisualCron and trying to convert a SQL Extract (CSV) into a specific XML output to pass to a SOAP process. I have tried the XML transform but no luck yet. It needs to have a loop to convert each line of the CSV to a concatenated line in the XML.

CSV example (from Adventureworks)
BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion,AdditionalContactInfo,rowguid,ModifiedDate
1,EM,False,,Ken,J,Sánchez,,0,,92c4279f-1207-48a3-8448-4636514eb7e2,7/01/2009 12:00:00 AM
2,EM,False,,Terri,Lee,Duffy,,1,,d8763459-8aa8-47cc-aff7-c9079af79033,24/01/2008 12:00:00 AM
3,EM,False,,Roberto,,Tamburello,,0,,e1a2555e-0828-434b-a33b-6f38136a37de,4/11/2007 12:00:00 AM
4,EM,False,,Rob,,Walters,,0,,f2d7ce06-38b3-4357-805b-f4b6b71c01ff,28/11/2007 12:00:00 AM

Need to convert to
XML
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<BulkStringLoadWithLogin xmlns="http://xxxxxx">
<LoginName>string</LoginName>
<Password>string</Password>
<TenantCode>string</TenantCode>
<Rule>string</Rule>
<Data>
<string>string</string>
</Data>
<strDelimiter>string</strDelimiter>
</BulkStringLoadWithLogin>
</soap:Body>
</soap:Envelope>