XML Data Interchange with GoAnywhere

Support_Sai · Post by **Support_Sai** » Thu Jun 04, 2009 3:20 pm

A sample project is below.

Project#1 Creating header-detail style XML document using data from multiple relational database tables

Code: Select all

<project name="Outbound" mainModule="Main" version="1.0" logLevel="debug">

	<module name="Main">

		<sql label="Connect to DB" resourceId="Production">
			<query label="Get Orders" outputVariable="orders">
				<statement>SELECT 
	ORDNUM, 
	ORDDATE, 
	TOTAMT, 
	CUSTNAME, 
	ADDR1, 
	ADDR2, 
	CITY, 
	STATE, 
	ZIP 
FROM 
	GADEMO.ORDERS 
ORDER BY
	ORDNUM</statement>
			</query>
			<query label="Get Line Items" outputVariable="lineitems">
				<statement>SELECT 
	GADEMO.LINEITEMS.ORDNO, 
	GADEMO.ITEMS.ITMNO, 
	GADEMO.ITEMS.ITMNAME, 
	GADEMO.LINEITEMS.PRICE 
FROM 
	GADEMO.ITEMS, 
	GADEMO.LINEITEMS 
WHERE 
	GADEMO.LINEITEMS.ITMNO = GADEMO.ITEMS.ITMNO 
ORDER BY
	GADEMO.LINEITEMS.ORDNO</statement>
			</query>
		</sql>


		<xmlWrite outputFile="C:\Program Files (x86)\Linoma Software\GoAnywhere2_0\userdata\documents\root\webinar-xml-orders.xml" whenFileExists="overwrite" defaultTrim="right" defaultTimestampFormat="yyyy-MM-dd&apos;T&apos;HH:mm:ss">
			<header><?xml version="1.0" encoding="UTF-8" ?></header>
			<element name="orders">
				<forEach inputRowSetVariable="${orders}" sorted="ascending">
					<element name="order">
						<attribute name="orderNumber" value="${orders[1]}" />
						<element name="date" value="${orders[2]}" />
						<element name="totalAmount" value="${orders[3]}" />
						<element name="shipTo">
							<element name="name" value="${orders[4]}" />
							<element name="address">
								<element name="line1" value="${orders[5]}" />
								<element name="line2" value="${orders[6]}" />
								<element name="city" value="${orders[7]}" />
								<element name="state" value="${orders[8]}" />
								<element name="zip" value="${orders[9]}" />
								<element name="country" value="US" />
							</element>
						</element>
						<forEach inputRowSetVariable="${lineitems}" sorted="ascending">
							<if test="${lineitems[1]} eq ${orders[1]}">
								<element name="item">
									<attribute name="itemNumber" value="${lineitems[2]}" />
									<element name="name" value="${lineitems[3]}" />
									<element name="price" value="${lineitems[4]}" />
								</element>
							</if>
						</forEach>
					</element>
				</forEach>
			</element>
		</xmlWrite>

	</module>

</project>

Project#2 Parse an XML document and import the data into a relational database

Code: Select all

<project name="Inbound" mainModule="Main" version="1.0" logLevel="debug">

	<module name="Main">

		<xmlRead label="Parse Orders XML" file="C:\Program Files (x86)\Linoma Software\GoAnywhere2_0\userdata\documents\root\webinar-xml-orders.xml" defaultTimestampFormat="yyyy-MM-dd&apos;T&apos;HH:mm:ss">
			<rowset name="orders">
				<column index="1" value="/orders/order/@orderNumber" name="OrderNumber" type="INTEGER" />
				<column index="2" value="/orders/order/date" name="OrderDate" type="TIMESTAMP" />
				<column index="3" value="/orders/order/totalAmount" name="TotalAmount" type="NUMERIC" />
				<column index="4" value="/orders/order/shipTo/name" name="CustomerName" />
				<column index="5" value="/orders/order/shipTo/address/line1" name="AddressLine1" />
				<column index="6" value="/orders/order/shipTo/address/line2" name="AddressLine2" />
				<column index="7" value="/orders/order/shipTo/address/city" name="City" />
				<column index="8" value="/orders/order/shipTo/address/state" name="State" />
				<column index="9" value="/orders/order/shipTo/address/zip" name="Zip" />
			</rowset>
			<rowset name="lineitems">
				<column index="1" value="/orders/order/@orderNumber" name="OrderNumber" type="INTEGER" foreignKey="true" />
				<column index="2" value="/orders/order/item/@itemNumber" name="ItemNumber" type="INTEGER" />
				<column index="3" value="/orders/order/item/price" name="ItemPrice" type="NUMERIC" />
			</rowset>
		</xmlRead>


		<sql label="Connect to DB" resourceId="Production" autoCommit="true">
			<query label="Clear Line Items">
				<statement>delete from wpsai.lineitems</statement>
			</query>
			<query label="Clear Orders">
				<statement>delete from wpsai.orders</statement>
			</query>
			<query label="Insert Orders" inputRowSetVariable="${orders}">
				<statement>INSERT INTO WPSAI.ORDERS
	(ORDNUM, ORDDATE, TOTAMT, CUSTNAME, ADDR1, ADDR2, CITY, STATE, ZIP)
VALUES
	(?, ?, ?, ?, ?, ?, ?, ?, ?)</statement>
			</query>
			<query label="Insert Line Items" inputRowSetVariable="${lineitems}">
				<statement>INSERT INTO WPSAI.LINEITEMS
	(ORDNO, ITMNO, PRICE)
VALUES
	(?, ?, ?)</statement>
			</query>
		</sql>

	</module>

</project>

Project#3 Calling a web service using SQL Task, Write XML Task and HTTP Task

Code: Select all

<project name="Call Web Service" mainModule="Main" version="1.0">

	<module name="Main">

		<createWorkspace />


		<sql resourceId="Production">
			<query outputVariable="data">
				<statement>select zipcode from gademo.websrvdemo where company = &apos;Linoma Software&apos;</statement>
			</query>
		</sql>


		<xmlWrite outputFile="SOAPRequest.xml">
			<header><?xml version="1.0" encoding="UTF-8" ?></header>
			<element name="soap12:Envelope">
				<attribute name="xmlns:xsi" value="http://www.w3.org/2001/XMLSchema-instance" />
				<attribute name="xmlns:xsd" value="http://www.w3.org/2001/XMLSchema" />
				<attribute name="xmlns:soap12" value="http://www.w3.org/2003/05/soap-envelope" />
				<element name="soap12:Body">
					<forEach inputRowSetVariable="${data}">
						<element name="GetWeatherByZipCode">
							<attribute name="xmlns" value="http://www.webservicex.net" />
							<element name="ZipCode" value="${data[1]}" />
						</element>
					</forEach>
				</element>
			</element>
		</xmlWrite>


		<http host="www.webservicex.com">
			<postRawData uri="/WeatherForecast.asmx " inputFile="SOAPRequest.xml" contentType="application/soap+xml; charset=utf-8" responseBodyDestination="file" responseBodyFile="C:\Program Files (x86)\Linoma Software\GoAnywhere2_0\userdata\documents\root\weather-report.xml" whenResponseBodyFileExists="overwrite" />
		</http>

	</module>

</project>