Need Output for XML CLOB DATATYPE

[praburaj]

Hi all,

I have received XML file from the source system which is stored as a clob in the db2 table. Now I have data like this in my xml file.

<Object name="Person"><Attribute name="Name"><Scalar>david</Scalar></Attribute><Attribute name="erfestival" type="String"><Scalar>newyear</Scalar></Attribute><Attribute name="xxx"

Output:

Name erfestival
------ ---------
david newyear

A row need to converted into column with those corresponding values. How can I achive this result?

Any help is appreciated

[ray.wurlod]

Try extracting the column as a Long VarChar data type - perhaps with a CAST in the SQL, perhaps just setting the metadata.

[eostic]

...is your question "how to parse the XML string" or "how to retrieve the string from inside of DB2" ?

Ernie

[praburaj]

Thanx for your reply eostic. The question how to parse the xml string.

I am new to DB2 Env. I don't know how to achieve this result. Plz help me

to resolve this issue.

Sample data:

<Object name="Person"><Attribute name="Name"><Scalar>david</Scalar></Attribute><Attribute name="erfestival" type="String"><Scalar>newyear</Scalar></Attribute><Attribute name="xxx"

Need my Output like this.

Name erfestival
------ ---------
david newyear

[eostic]

We'll assume you know how to get that entire tagged string as a row in a single column. For what you are doing, and these few columns, it is very easy.....however, you should spend time looking thru the many threads here on XML, and spend time with the documentation.....

...send this single column into an xmlInput Stage....there is an indicator there that you are supplying xml "content" as input. On the output link, place your columns. You can just type them in with a short list....with a larger list you will want to use the xml metadata importer and a schema or full xml document example for importing --- I'll let you read about that.

Already I can see you will have a problem "after" you retrieve the data, because it won't be in the format you would like. This is a fairly poor xml design --- it is carrying its metadata as "data"....it would be nicer, for example, if your xml used actual tags for <PersonName>David</PersonName>, instead of presenting you with multiple elements and attributes for each of the given values you need....you will have multiple "rows" for your attributes...but let's get you started anyway.

....The output link will contain your columns. In your case, there is probably a repeating set of "objects" and within that a repeating set of attributes. Try this (the lengths are just a guess)...and the "/" goes into the Description at the far right of the column def on the link.

ObjectName varchar 40 /Object/@name
AttributeName varchar 40 /Object/Attribute/@name
AttributeType varchar 40 /Object/Attribute/@type
ScalarValue varchar 40 /Object/Attribute/Scalar/text()

Mark the ScalarValue column as a "key"....this is the repeating element.....you can read more in the forum here about what that means.

Your xml in the example here might not be complete....I don't know what things might be "above" Object in the nested path....if there are more elements in the hierarchy, they would need to be specified in front of Object in the xpath syntax above.

Ernie