Hi,
I'm trying to tag field values with the field names after extracting records from a DB2 table. Does anyone know how to extract the column names along with the column values from the table - is it possible using a function or a statement?

eg.
Customer (Name of table)
Name Age Sex Location
Don 28 M US
Jane 21 F UK

I have to extract each customer record and tag the values as follows:
<Name>Don<\Name><Age>28<\Age><Sex>M<\Sex><Location >US<\Location>
<Name>Jane<\Name><Age>21<\Age><Sex>F<\Sex><Locatio n>UK<\Location>

Would appreciate anyone's help with this.

Thanks,
Maxdp
*****

with java?????


If u use java,jdbc,



like this


System.out.println(sqlSelect);
rs=db.execQuery(sqlSelect);
rsmd=rs.getMetaData();
col_len=rsmd.getColumnCount();
}
catch(SQLException e)
{
System.out.println("·¢ÉúÁË´íÎó");
}
String col_name[]=new String[col_len];
int col_type[]=new int[col_len];
for(int i=0;i<col_len;i++)
{
try {
col_name[i]=rsmd.getColumnName(i+1);
col_type[i]=rsmd.getColumnType(i+1);
// System.out.println(col_name[i]+"type"+col_type[i]);
} catch (SQLException e) {
System.out.println("·¢ÉúÁË´íÎó");
}
}
try {
while(rs.next())
{

for(int j=0;j<col_len;j++)
{
if(col_type[j]!=93)
{
hashtable.put(col_name[j],(rs.getString(j+1)==null)?"":rs.getString(j+1));
//System.out.println(col_name[j]+"name "+rs.getString(j+1));
}
}
}
} catch (SQLException e) {
}
db.close();
return hashtable;
}

Thanks Richard. I forgot to say that I'm trying to do this by calling a COBOL sub-routine on the mainframe through a triggered stored procedure. Hope someone can provide some info on this, without me having to actually include a file layout for the table columns.
Maxdp
*****

Not sure this is going down the wrong path. Within a stored procedure you can have a SELECT which builds the tags around the data values and produce a result set.

SELECT '<Name>' || name || '</Name><Age>' || age || ......
FROM table
WHERE ....

You can also use system tables to build a select statement dynamically to pull the columns and data. I'm familiar with doing that within UDB but not sure about the mainframe.

hey dmmac, thanks for the creative suggestion, but I think that performance gets affected too much doing it that way. Actually tested it and found that for tagging 7 fields of around 3200 records, the difference between the non-tagged retrieve and the tagged one was like 5 secs - 1.50 and 6.93 respy. How do u do it on the UDB and how's the performance on that?

I'm not sure if it is supported from within a stored procedure but you can get the column names from the SQLDA after preparing the SQL statement. I'm not at the office until next week so I can't show an example right now.

I do have my doubts wether this will perform any faster then the concat suggestion from dmmac