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
How do I
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
For Windows users, your line-terminator is two characters long so you will need to use 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.
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)
Code: Select all
Ereplace(XMLDocument, @FM, CR:LF, -1, 0)
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
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?
Being nosy since I am in the St. Louis area, can you tell me if this is for a company in St. Louis?
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA