How do I

tonystark622 · Post by **tonystark622** » Wed Sep 29, 2004 3:17 pm

create an XML document in one column and one or more key columns on the same link?

The requirement is to read data from a database table, transform the data to XML and insert the XML into a database table with non-XML key data as the Key columns for the row.

I appreciate any help you all may give me.

Tony

ray.wurlod · Post by **ray.wurlod** » Wed Sep 29, 2004 4:05 pm

Create a routine that writes the XML document as a field-mark delimited dynamic array.
Just before returning, convert the field-marks to line-terminator characters. Because you're on UNIX:

Code: Select all

FUNCTION CreateXMLDocument(Args)
Equate LF To Char(10)
XMLDocument = ""

* your code here
* in the next example line, -1 means "append a new field"
XMLDocument<-1> = "Text to put in line, possibly with tags"

Ans = Convert(@FM, LF, XMLDocument)
RETURN(Ans)

For Windows users, your line-terminator is two characters long so you will need to use

Code: Select all

Ereplace(XMLDocument, @FM, CR:LF, -1, 0)

instead of the Change function used above. LF is Char(10), CR is Char(13).

Check out my recent post on constructing XML in BASIC, even though this XML was written to file. You would substitute the <-1> notation where that code uses WriteSeq.

trobinson · Post by **trobinson** » Thu Sep 30, 2004 9:03 am

The XML Output stage will do this for you. You can have non-XML as passthrough columns untouched so that one column is defined as XML and the others are defined as columns that contain the key. Is there a requirement that this be done in a routine or am I missing something? Is it a single column you're after with all values defined together as Ray suggests?

tonystark622 · Post by **tonystark622** » Thu Sep 30, 2004 9:14 am

No, I needed separate key columns and a column containing the entire XML string (including the data in the key columns).

I haven't figured out how to make the XML Output stage pass through a column. I'm reading the documentation now. Can you give me any pointers on how to do this?

Thanks,
Tony

trobinson · Post by **trobinson** » Thu Sep 30, 2004 9:23 am

On the input side define your key columns as normal but do not define any XPATHs in the description. For the columns that form the single XML chunk column, go ahead and define their XPATHS. In the output stage define the key columns as they were in the input taking care not to define their type as XML. Define a single XML column as the xml output chunk of type XML. This should be all that you need do. I do not have DataStage in front of me or I could be more specific. I'm not sure this is adequately covered in the documentation. Also I think you'll want to make sure the XML chunk is outputted down the link as a chunk when the key columns change and not as a single document.
Being nosy since I am in the St. Louis area, can you tell me if this is for a company in St. Louis?

tonystark622 · Post by **tonystark622** » Thu Sep 30, 2004 9:55 am

Thanks! I'm reading up on this now. I appreciate your help.

Tony