Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2012
    Posts
    6

    Unanswered: Export a table to an XML file in DB2 9.7

    Hello dB Forum,

    I am trying to export a simple table (there is no XML column existed in this table) called "sample" into an XML file in DB2 9.7. I have looked at DB2 information center and I found the documentation kind of confusing. I attempted to run the following command:

    db2 => EXPORT TO XML TO c:\xml\path XMLFILE c:\xml\path\myfile.001.xml select * from sample

    But this fails with query syntax error.

    Can someone please provide a sample "EXPORT" command to achieve this.

    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    you cannot use the EXPORT command to create an XML file in that manner (i.e. you must use different syntax)
    See
    EXPORT - IBM DB2 9.7 for Linux, UNIX, and Windows

    Choose a different method, you can use DEL with the syntax here
    http://publib.boulder.ibm.com/infoce.../c0024102.html
    Last edited by db2mor; 06-15-12 at 17:44.

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    hello db2mor,

    Thanks for your response.
    If I understood correctly you are saying that there is not way in DB2 to export a DB schema directly as an .xml file ?

    I was able to export the schema in delimited format which was blazing fast, however, my final goal would be to find a way to export the schema directly as a valid and well formed .xml file which can be done in Oracle.

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Did you read the info at the "publib.boulder. . . ." link provided?

    Will this not do what you want?

  5. #5
    Join Date
    Jun 2012
    Posts
    6
    Hello,

    May be I am blind, but that link discusses a table with a couple of XML columns and how to deal with exporting the tables along with its XML columns.

    My table (sample) doesn't have an xml column and its pretty simple.
    Lets say it has the following structure:

    country string
    city string

    now I have the following data in my table:

    COUNTRY CITY
    ------- ------
    Canada Toronto
    Canada Montreal
    U.S New York
    U.S Buffalo

    So I want to export this table as an xml file as follow:

    <country>Canada</country>
    <city>Toronto</city>
    <city>Montreal</city>
    </country>
    <country>U.S</country>
    <city>New York</city>
    <city>Buffalo</city>
    </country>

    If I can manage to add a root to this .xml file through the command that I am running, even better.

    Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can export results of any query, e.g. one like this:
    Code:
    select xmlelement(name root, xmlagg(xmlforest(country, city))) from mytable

  7. #7
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Last edited by db2mor; 06-22-12 at 06:44. Reason: removed incorrect information

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by n_i View Post
    You can export results of any query, e.g. one like this:
    Code:
    select xmlelement(name root, xmlagg(xmlforest(country, city))) from mytable
    Here is an example tested on DB2/XP 9.7.5.

    CREATE TABLE and populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_to_xml
    ( id      SMALLINT NOT NULL
              GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY
    , country VARCHAR(10)
    , city    VARCHAR(20)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_to_xml
    ( country , city )
    VALUES
      ( 'Canada' , 'Toronto'  )
    , ( 'Canada' , 'Montreal' )
    , ( 'U.S'    , 'New York' )
    , ( 'U.S '   , 'Buffalo'  )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    EXPORT:
    Code:
    ------------------------------ Commands Entered ------------------------------
    EXPORT TO test_to_xml.del OF del
    XMLFILE test_to_xml
    SELECT XMLAGG(city_list)
     FROM (SELECT XMLELEMENT(
                    NAME "country"
                  , XMLATTRIBUTES(
                       country AS "name"
                    )
                  , XMLAGG(
                       XMLELEMENT(
                          NAME "city" , city
                       )
                       ORDER BY id
                    )
                 )
                 AS city_list
           FROM  test_to_xml
           GROUP BY
                 country
          )
    ;
    ------------------------------------------------------------------------------
    SQL3104N  The Export utility is beginning to export data to file 
    "test_to_xml.del".
    
    SQL3105N  The Export utility has finished exporting "1" rows.
    
    
    Number of rows exported: 1
    Note: XMLFILE filename specifies one or more base file names for the XML files.


    Output of test_to_xml.del:
    Code:
    "<XDS FIL='test_to_xml.001.xml' OFF='0' LEN='184' />"
    Output of test_to_xml.001.xml
    Code:
    <?xml version="1.0" encoding="UTF-8" ?>
    <country name="Canada">
       <city>Toronto</city>
       <city>Montreal</city>
    </country>
    <country name="U.S">
       <city>New York</city>
       <city>Buffalo</city>
    </country>
    Note: The output does not include blank spaces or new line characters. I added them to enhance readability.

  9. #9
    Join Date
    Jun 2012
    Posts
    6
    Hello tonkuma,

    Thanks for providing your insight. It looks promising. But don't you think the generated XML file doesn't actually have a root ?

    So for the generated .xml doc to be well-formed I think the following structure is expected:

    <list>
    <country>
    <city>
    </city>
    </country>
    </list>


    What do you think?

    Thank

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    <list>
    <country>
    <city>
    </city>
    </country>
    </list>
    Plese write more cocretely by filling in the data
    COUNTRY CITY
    ------- ------
    Canada Toronto
    Canada Montreal
    U.S New York
    U.S Buffalo
    I want to know how country names were expressed in your XML format.

  11. #11
    Join Date
    Jun 2012
    Posts
    6
    sorry for confusion,
    here is my xml file format:

    <?xml version="1.0" encoding="UTF-8" ?>
    <country name="Canada">
    <city>Toronto</city>
    <city>Montreal</city>
    </country>
    <country name="U.S">
    <city>New York</city>
    <city>Buffalo</city>
    </country>

    So as you can see it doesn't have a root element. So what I was referring to was the fact that the should have (for example) the following format:

    <?xml version="1.0" encoding="UTF-8" ?>
    <list>
    <country name="Canada">
    <city>Toronto</city>
    <city>Montreal</city>
    </country>
    <country name="U.S">
    <city>New York</city>
    <city>Buffalo</city>
    </country>
    </list>

    Thanks

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about replacing
    Code:
    SELECT XMLAGG(city_list)
    in my last example
    to
    Code:
    SELECT XMLELEMENT(
              NAME "list"
            , XMLAGG(city_list)
           )
    ?


    Anyway, I thought that the issue was change slightly now
    from "how to export a table(all non-xml columns) into an XML file"
    to "how to construct required XML data(format) from a table(all non-xml columns)".

    So, I hope suggestions of more elegant ways by experts of XML support of DB2.

  13. #13
    Join Date
    Jun 2012
    Posts
    6
    Hello,

    You made my day and possibly my weekend as your suggestion worked like a charm. The output .xml file now is a valid and well-formed document with a root. I would like to appreciate your willingness to share your expertise.

    Have a great weekend!

    <?xml version="1.0" encoding="UTF-8" ?>
    <list>
    <country name="Canada">
    <city>Toronto</city>
    <city>Montreal</city>
    </country>
    <country name="U.S">
    <city>New York</city>
    <city>Buffalo</city>
    </country>
    </list>

  14. #14
    Join Date
    Jul 2012
    Posts
    2
    Hello Tonkuma,

    I found this thread very interesting. I am trying to perform the same action in my DB2 9.7 on tables where there is no XML data stored but simple characters and numbers. But my xml output needs to have one more nested element compared to what "DB2 learner" had. So for example something like the following, any help is appreciated as I am not able to get it resolved by adding additional "XMLELEMENT" to the query :

    <?xml version="1.0" encoding="UTF-8" ?>
    <list>
    <country>Canada</country>
    <city>Toronto</city>
    <town>Markham</town>
    <town>Richmond hill</town>
    <town>Scarbrough</town>
    </city>
    </country>
    </list>

  15. #15
    Join Date
    Jul 2012
    Posts
    2
    To make it more clear, here is my table called TEST_TO_XML:

    COUNTRY CITY TOWN
    --------- ------ --------
    Canada Toronto Markham
    Canada Toronto Richmond Hill
    Canada Toronto Scarbrough
    The query that I am running is as follow:
    EXPORT TO schema_to_xml.del OF del
    XMLFILE schema_to_xml
    SELECT XMLELEMENT(
    NAME "list", XMLAGG(country_list)
    )
    FROM (SELECT XMLELEMENT(
    NAME "country"
    , XMLATTRIBUTES(
    COUNTRY AS "name"
    )
    , XMLAGG(
    XMLELEMENT(
    NAME "city" , CITY
    )
    , XMLAGG(
    XMLELEMENT(
    NAME "town" , TOWN
    )

    )
    )
    AS country_list
    FROM TEST_TO_XML
    GROUP BY
    COUNTRY
    )
    ;
    The output that I am looking for is:
    <?xml version="1.0" encoding="UTF-8" ?>
    <list>
    <country>Canada</country>
    <city>Toronto</city>
    <town>Markham</town>
    <town>Richmond hill</town>
    <town>Scarbrough</town>
    </city>
    </country>
    </list>
    Any help is appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •