Search code examples
sqlxmldb2xmlelement

DB2 SQL XMLSERIALIZE / XML value of Select *

I would like to output all fields of a SELECT SQL in DB2 as XML. Sorry for using "Select *" here but in this case it makes sense. Have checked XMLSERIALIZE and XMLELEMENT commands but they need a field list. e.g.

SELECT XMLELEMENT (*) as X from DATA1.CUSTOMERS where CUSTOMERNO='262627'

or

SELECT  XMLSERIALIZE (*) as X from DATA1.CUSTOMERS where CUSTOMERNO='262627'

does not work. Can you help me?

Solution

  • There is no XML publishing function that accepts the wildcard. The easiest way would be to use XMLROW. It accepts a column list or more complex expressions and gives you some options for the final format.

    select xmlrow(col1, col2) from mytable

    or 

    select xmlrow(col1, col2 option row "myrowname") from mytable