DSXchange

Hi,
I need to create a datastage job based on below requirement.

Source: SQL Server
Target: Sequential File

In Source there is a column with datatype XML, which holds the xml code in a single record as below:
e.g.
1st record:
<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
</book>
<book id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
</book>
</catalog>

2nd record:
<?xml version="1.0"?>
<catalog>
<book id="bk103">
<author>Gambardella</author>
<title>XML Guide</title>
</book>
<book id="bk104">
<author>Ralls</author>
<title>Midnight</title>
</book>
</catalog>

similarly we can have multiple records.

NOw the req. is to extract the above xml code from this source table and populate in sequential file as individual records.
"bk101","Gambardella, Matthew","XML Developer's Guide"
"bk102","Ralls, Kim""Midnight Rain"
"bk103","Gambardella","XML Guide"
"bk104","Ralls""Midnight"

New with this xml thing so please specify me exact steps to be done.

Thanks

Do you have or can you get an xsd for this? At a high level, you would select the column from the table and then pass it to an XML Input stage, telling the stage that incoming column was the xml source. Then proper metadata, used in the "XPath Expression" (aka Description) field of the XML stage and generated from your xsd would parse out the individual fields as you need.

Also Do remember to keep your "book id" as the repitation element.

Do some basic testing first, just to see if you are able to read and extract this particular xml column....once you have that, the rest is straightforward....feeding that column as "content" to the xmlInput Stage. You will find many posts on this subject, and the other tips posted just above. You need to import the metadata for this xml document structure, and put that metadata on the output link of your xmlInput Stage...there are many other decisions you have a make based on the structure of the xml, but those are the basics.

Ernie